jtricerolph
8d688b459d
FastAPI backend (Python 3.11, MSSQL ODBC for SambaPOS, Azure DI OCR),
kitchen_db on central PG. React/TS/Vite frontend with navy sidebar layout.
Backend: auth.py (APP_SLUG=kitchen, SimpleNamespace — archive routes use
.kitchen_id/.is_admin without modification), main.py (51 migrations, scheduler,
internal router for KDS bookings feed), api/internal.py, full archive API
(31 routers: invoices, recipes, menus, sambapos, resos, newbook, disputes,
purchase_orders, etc.), models, migrations, OCR pipeline.
kitchen_id pinned to 1 (B1 — single hotel).
Frontend: AuthGate (app=kitchen, token shim for archive compat — B5b pending),
Layout (navy sidebar, 6 sections, Lucide icons, teal --app-primary),
App.tsx (Outlet pattern, UploadApp outside Layout), index.css (full :root block).
strict: false — archive components have type issues; build clean.
Note: 45 archive components call fetch('/api/...') without /kitchen/ prefix
(B5b). Runtime 404s; deferred until after initial testing.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
201 lines
9.2 KiB
Bash
Executable file
201 lines
9.2 KiB
Bash
Executable file
#!/usr/bin/env bash
|
|
# =============================================================================
|
|
# Kitchen — Migrate from Production
|
|
# =============================================================================
|
|
# Copies the live production kitchen_gp database and invoice files into the
|
|
# new stack (kitchen_db on LXC 100 + invoice_data volume on LXC 110).
|
|
#
|
|
# Run this from the Proxmox host AFTER kitchen and kds are deployed:
|
|
# bash /opt/kitchen/migrate-from-production.sh
|
|
#
|
|
# It is safe to re-run: the final step truncates and re-imports, so a second
|
|
# run simply refreshes the data. Stop kitchen + kds before running if live.
|
|
#
|
|
# FILL IN SECTION BELOW before first run (D10: confirm production host + creds).
|
|
# =============================================================================
|
|
set -euo pipefail
|
|
|
|
# ── Production source (fill in) ──────────────────────────────────────────────
|
|
PROD_HOST="" # e.g. 192.168.1.50 or laptop hostname
|
|
PROD_PG_PORT="5432"
|
|
PROD_PG_USER="postgres"
|
|
PROD_DB="kitchen_gp" # production DB name
|
|
PROD_DATA_DIR="" # path to invoice data on production, e.g. /home/user/data/invoices
|
|
|
|
# ── Stack target ─────────────────────────────────────────────────────────────
|
|
STACK_PG_HOST="10.10.10.100"
|
|
STACK_PG_PORT="5432"
|
|
STACK_PG_SUPER="postgres" # postgres superuser in hotel-manage-postgres container
|
|
STACK_DB="kitchen_db"
|
|
STACK_DB_USER="kitchen" # created by deploy_kitchen()
|
|
KITCHEN_LXC=110
|
|
|
|
# ── Load KITCHEN_DB_PASS from credentials file ────────────────────────────────
|
|
CREDS_FILE=/root/hotel-manage-credentials.txt
|
|
if [[ -f "$CREDS_FILE" ]]; then
|
|
KITCHEN_DB_PASS=$(grep '^KITCHEN_DB_PASS=' "$CREDS_FILE" | cut -d= -f2- | head -1)
|
|
fi
|
|
if [[ -z "${KITCHEN_DB_PASS:-}" ]]; then
|
|
echo "ERROR: KITCHEN_DB_PASS not found in ${CREDS_FILE}" >&2
|
|
exit 1
|
|
fi
|
|
|
|
# =============================================================================
|
|
RD="\033[01;31m"; GN="\033[1;92m"; YW="\033[33m"; CL="\033[m"
|
|
die() { printf "${RD}✗ %s${CL}\n" "$*" >&2; exit 1; }
|
|
ok() { printf "${GN}✓ %s${CL}\n" "$*"; }
|
|
inf() { printf "${YW}◌ %s${CL}\n" "$*"; }
|
|
|
|
[[ -z "$PROD_HOST" ]] && die "PROD_HOST not set — edit this script first (see D10)"
|
|
[[ -z "$PROD_DATA_DIR" ]] && die "PROD_DATA_DIR not set — edit this script first"
|
|
|
|
DUMP_FILE="/tmp/kitchen_migration_$(date +%Y%m%d_%H%M%S).dump"
|
|
|
|
# =============================================================================
|
|
# 1. Dump production DB
|
|
# =============================================================================
|
|
inf "Dumping ${PROD_DB} from ${PROD_HOST}..."
|
|
PGPASSWORD="${PROD_PG_PASS:-}" pg_dump \
|
|
-h "$PROD_HOST" -p "$PROD_PG_PORT" -U "$PROD_PG_USER" \
|
|
-Fc --no-owner --no-privileges \
|
|
"$PROD_DB" > "$DUMP_FILE"
|
|
ok "Dump written to ${DUMP_FILE} ($(du -sh "$DUMP_FILE" | cut -f1))"
|
|
|
|
# =============================================================================
|
|
# 2. Stop kitchen + kds so no writes arrive during restore
|
|
# =============================================================================
|
|
inf "Stopping kitchen and kds containers..."
|
|
pct exec 110 -- bash -c "cd /opt/kitchen && docker compose stop backend" 2>/dev/null || true
|
|
pct exec 125 -- bash -c "cd /opt/kds && docker compose stop backend" 2>/dev/null || true
|
|
ok "Services stopped"
|
|
|
|
# =============================================================================
|
|
# 3. Drop + recreate kitchen_db (clean slate for restore)
|
|
# =============================================================================
|
|
inf "Recreating ${STACK_DB} on ${STACK_PG_HOST}..."
|
|
# Push dump file into LXC 100 for restore
|
|
pct push 100 "$DUMP_FILE" /tmp/kitchen_restore.dump
|
|
|
|
pct exec 100 -- bash -c "
|
|
docker exec hotel-manage-postgres psql -U postgres -c \
|
|
\"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '${STACK_DB}' AND pid <> pg_backend_pid();\"
|
|
docker exec hotel-manage-postgres psql -U postgres -c \
|
|
\"DROP DATABASE IF EXISTS ${STACK_DB};\"
|
|
docker exec hotel-manage-postgres psql -U postgres -c \
|
|
\"CREATE DATABASE ${STACK_DB} OWNER ${STACK_DB_USER};\"
|
|
docker exec hotel-manage-postgres psql -U postgres -d ${STACK_DB} -c \
|
|
\"GRANT ALL ON SCHEMA public TO ${STACK_DB_USER};\"
|
|
"
|
|
ok "${STACK_DB} recreated"
|
|
|
|
# =============================================================================
|
|
# 4. Restore dump into kitchen_db
|
|
# =============================================================================
|
|
inf "Restoring dump into ${STACK_DB}..."
|
|
pct exec 100 -- bash -c "
|
|
docker cp /tmp/kitchen_restore.dump hotel-manage-postgres:/tmp/
|
|
docker exec hotel-manage-postgres pg_restore \
|
|
-U postgres -d ${STACK_DB} \
|
|
--no-owner --no-privileges \
|
|
--exit-on-error \
|
|
/tmp/kitchen_restore.dump
|
|
docker exec hotel-manage-postgres rm -f /tmp/kitchen_restore.dump
|
|
rm -f /tmp/kitchen_restore.dump
|
|
"
|
|
ok "Dump restored to ${STACK_DB}"
|
|
|
|
# =============================================================================
|
|
# 5. Re-run KDS migrations (add KDS columns not in production dump)
|
|
# =============================================================================
|
|
inf "Applying KDS schema additions to ${STACK_DB}..."
|
|
pct exec 125 -- bash -c "
|
|
cd /opt/kds
|
|
docker compose run --rm -e DATABASE_URL=postgresql://${STACK_DB_USER}:${KITCHEN_DB_PASS}@${STACK_PG_HOST}:${STACK_PG_PORT}/${STACK_DB} \
|
|
backend python -c \"
|
|
import asyncio
|
|
from database import engine, Base
|
|
from migrations.add_kds_tables import run_migration as m1
|
|
from migrations.add_kds_course_flow import run_migration as m2
|
|
from migrations.add_kds_order_tracking import run_migration as m3
|
|
from migrations.add_kds_bookings_refresh import run_migration as m4
|
|
async def run():
|
|
async with engine.begin() as conn:
|
|
await conn.run_sync(Base.metadata.create_all)
|
|
for m in [m1, m2, m3, m4]:
|
|
try:
|
|
await m()
|
|
except Exception as e:
|
|
print(f'migration warning (may be ok): {e}')
|
|
asyncio.run(run())
|
|
\"" || true
|
|
ok "KDS schema additions applied"
|
|
|
|
# =============================================================================
|
|
# 6. Rsync invoice data files
|
|
# =============================================================================
|
|
inf "Syncing invoice data files from ${PROD_HOST}:${PROD_DATA_DIR} ..."
|
|
# Find the docker volume mount on LXC 110
|
|
VOLUME_PATH=$(pct exec 110 -- bash -c "
|
|
docker inspect kitchen-backend-1 2>/dev/null | \
|
|
python3 -c \"import sys,json; mounts=json.load(sys.stdin)[0]['Mounts']; \
|
|
print(next(m['Source'] for m in mounts if 'invoice_data' in m.get('Name','') or '/app/data' in m.get('Destination','')))\"\
|
|
" 2>/dev/null || echo "")
|
|
|
|
if [[ -z "$VOLUME_PATH" ]]; then
|
|
inf "Could not auto-detect invoice volume path — skipping file sync"
|
|
inf "Manually rsync: ${PROD_HOST}:${PROD_DATA_DIR}/ → LXC 110's invoice_data volume"
|
|
else
|
|
rsync -avz --progress "${PROD_HOST}:${PROD_DATA_DIR}/" \
|
|
"root@${STACK_PG_HOST}:${VOLUME_PATH}/" || true
|
|
ok "Invoice files synced to ${VOLUME_PATH}"
|
|
fi
|
|
|
|
# =============================================================================
|
|
# 7. Restart services
|
|
# =============================================================================
|
|
inf "Restarting kitchen and kds..."
|
|
pct exec 110 -- bash -c "cd /opt/kitchen && docker compose start backend"
|
|
pct exec 125 -- bash -c "cd /opt/kds && docker compose start backend"
|
|
ok "Services restarted"
|
|
|
|
# =============================================================================
|
|
# 8. Verification report
|
|
# =============================================================================
|
|
inf "Running verification report..."
|
|
|
|
REPORT=$(pct exec 100 -- bash -c "
|
|
docker exec hotel-manage-postgres psql -U postgres -d ${STACK_DB} -t -A -F'|' -c \"
|
|
SELECT 'invoices' , COUNT(*) FROM invoices;
|
|
SELECT 'line_items' , COUNT(*) FROM invoice_line_items;
|
|
SELECT 'suppliers' , COUNT(*) FROM suppliers;
|
|
SELECT 'ingredients' , COUNT(*) FROM ingredients;
|
|
SELECT 'recipes' , COUNT(*) FROM recipes;
|
|
SELECT 'resos_bookings', COUNT(*) FROM resos_bookings;
|
|
\"
|
|
" 2>/dev/null || echo "verification query failed")
|
|
|
|
printf "\n${GN}══ Migration verification ══════════════════════════════════${CL}\n"
|
|
while IFS='|' read -r table count; do
|
|
printf " %-20s %s rows\n" "$table" "$count"
|
|
done <<< "$REPORT"
|
|
printf "\n"
|
|
|
|
# Monthly invoice total sanity check (compare with production before cutover)
|
|
MONTHLY=$(pct exec 100 -- bash -c "
|
|
docker exec hotel-manage-postgres psql -U postgres -d ${STACK_DB} -t -A -F'|' -c \"
|
|
SELECT TO_CHAR(invoice_date, 'YYYY-MM'), COUNT(*), ROUND(SUM(total_exc_vat)::numeric, 2)
|
|
FROM invoices
|
|
WHERE invoice_date >= NOW() - INTERVAL '6 months'
|
|
GROUP BY 1 ORDER BY 1 DESC LIMIT 6;
|
|
\"
|
|
" 2>/dev/null || echo "")
|
|
|
|
if [[ -n "$MONTHLY" ]]; then
|
|
printf "${GN} Monthly invoice counts (last 6 months)${CL}\n"
|
|
while IFS='|' read -r month cnt total; do
|
|
printf " %s %4s invoices £%s\n" "$month" "$cnt" "$total"
|
|
done <<< "$MONTHLY"
|
|
fi
|
|
|
|
printf "\n${GN}Done. Clean up dump file:${CL}\n"
|
|
printf " rm -f %s\n\n" "$DUMP_FILE"
|