leocrm/scripts/restore_drill.sh

#!/bin/bash
# =============================================================================
# Restore Drill — Dump → fresh DB → Restore → Smoke checks (fully local)
# =============================================================================
# Proves the backup→restore path end-to-end WITHOUT production access:
#   1. Build a source DB via alembic migrations + seed rows
#   2. pg_dump it
#   3. Create an empty target DB
#   4. Restore the dump into it
#   5. Smoke-check integrity (table count, alembic version, row parity)
#
# Usage:
#   bash scripts/restore_drill.sh
#
# Exit codes: 0 = drill passed, 1 = drill failed
# =============================================================================

set -euo pipefail

RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

PGHOST="${PGHOST:-localhost}"
PGPORT="${PGPORT:-5432}"
PGUSER_SRC="${PGUSER_SRC:-leocrm_test}"
PGPASS_SRC="${PGPASS_SRC:-test123}"
SRC_DB="leocrm_drill_src_$$"
DST_DB="leocrm_drill_dst_$$"
BACKUP_FILE="/tmp/leocrm_drill_$$.sql"

export PGPASSWORD="$PGPASS_SRC"
PSQL=(psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER_SRC" -v ON_ERROR_STOP=1 -q)

cleanup() {
    echo "[DRILL] Cleanup temp databases..."
    "${PSQL[@]}" -d postgres -c "DROP DATABASE IF EXISTS $SRC_DB" >/dev/null 2>&1 || true
    "${PSQL[@]}" -d postgres -c "DROP DATABASE IF EXISTS $DST_DB" >/dev/null 2>&1 || true
    rm -f "$BACKUP_FILE"
}
trap cleanup EXIT

step() { echo -e "${YELLOW}[DRILL] $1${NC}"; }
pass() { echo -e "${GREEN}[DRILL] PASS: $1${NC}"; }
fail() { echo -e "${RED}[DRILL] FAIL: $1${NC}"; exit 1; }

echo "============================================================"
echo "  Restore Drill — local end-to-end proof"
echo "============================================================"

# ── 0. pgvector available? ────────────────────────────────────────────────────
step "0. Prerequisite: template1 has vector extension (for new DBs)"
"${PSQL[@]}" -d postgres -c "CREATE EXTENSION IF NOT EXISTS vector" >/dev/null 2>&1 || true
pass "pgvector ensured"

# ── 1. Source DB via migrations ──────────────────────────────────────────────
step "1. Create source DB + run alembic migrations"
"${PSQL[@]}" -d postgres -c "CREATE DATABASE $SRC_DB OWNER $PGUSER_SRC" >/dev/null
export DATABASE_URL="postgresql+asyncpg://$PGUSER_SRC:$PGPASS_SRC@$PGHOST:$PGPORT/$SRC_DB"
/opt/venv/bin/python -m alembic upgrade head > /tmp/drill_migrate.log 2>&1 \
    || fail "alembic upgrade head failed (see /tmp/drill_migrate.log)"
pass "source DB migrated"

# ── 2. Seed representative data ──────────────────────────────────────────────
step "2. Seed representative rows (tenant, user, contact, audit)"
PY=/opt/venv/bin/python
TENANT_ID=$($PY -c 'import uuid; print(uuid.uuid4())')
USER_ID=$($PY -c 'import uuid; print(uuid.uuid4())')
CONTACT_ID=$($PY -c 'import uuid; print(uuid.uuid4())')
"${PSQL[@]}" -d "$SRC_DB" <<SQL >/dev/null
INSERT INTO tenants (id, name, slug, created_at, updated_at)
VALUES ('$TENANT_ID', 'Drill Tenant', 'drill-tenant-$RANDOM', now(), now());
INSERT INTO users (id, email, name, password_hash, is_active, created_at, updated_at)
VALUES ('$USER_ID', 'drill@example.com', 'Drill User', 'x', true, now(), now());
INSERT INTO user_tenants (user_id, tenant_id, role, status, is_default, created_at)
VALUES ('$USER_ID', '$TENANT_ID', 'admin', 'active', true, now());
INSERT INTO contacts (id, tenant_id, type, displayname, name, owner_id, created_by, updated_by, created_at, updated_at)
VALUES ('$CONTACT_ID', '$TENANT_ID', 'company', 'Drill Corp', 'Drill Corp', '$USER_ID', '$USER_ID', '$USER_ID', now(), now());
INSERT INTO audit_log (id, tenant_id, user_id, action, entity_type, entity_id, timestamp)
VALUES (gen_random_uuid(), '$TENANT_ID', '$USER_ID', 'create', 'contact', '$CONTACT_ID', now());
SQL
pass "seed rows inserted"

# ── 3. Dump source DB ────────────────────────────────────────────────────────
step "3. pg_dump source DB"
PGPASSWORD="$PGPASS_SRC" pg_dump -h "$PGHOST" -p "$PGPORT" -U "$PGUSER_SRC" \
    --no-owner --no-acl "$SRC_DB" > "$BACKUP_FILE" \
    || fail "pg_dump failed"
SIZE=$(wc -c < "$BACKUP_FILE")
[ "$SIZE" -gt 10000 ] || fail "dump suspiciously small ($SIZE bytes)"
pass "dump written ($SIZE bytes)"

# ── 4. Fresh target DB + restore ─────────────────────────────────────────────
step "4. Create empty target DB and restore dump"
"${PSQL[@]}" -d postgres -c "CREATE DATABASE $DST_DB OWNER $PGUSER_SRC" >/dev/null
PGPASSWORD="$PGPASS_SRC" psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER_SRC" \
    -v ON_ERROR_STOP=1 -q -d "$DST_DB" -f "$BACKUP_FILE" \
    || fail "restore into fresh DB failed"
pass "restore completed"

# ── 5. Smoke checks ──────────────────────────────────────────────────────────
step "5. Smoke checks on restored DB"

TABLES_DST=$("${PSQL[@]}" -d "$DST_DB" -tAc "SELECT count(*) FROM pg_tables WHERE schemaname='public'")
TABLES_SRC=$("${PSQL[@]}" -d "$SRC_DB" -tAc "SELECT count(*) FROM pg_tables WHERE schemaname='public'")
[ "$TABLES_DST" = "$TABLES_SRC" ] || fail "table count mismatch: src=$TABLES_SRC dst=$TABLES_DST"
pass "table count parity: $TABLES_DST tables"

VER_DST=$("${PSQL[@]}" -d "$DST_DB" -tAc "SELECT version_num FROM alembic_version")
VER_SRC=$("${PSQL[@]}" -d "$SRC_DB" -tAc "SELECT version_num FROM alembic_version")
[ "$VER_DST" = "$VER_SRC" ] || fail "alembic version mismatch: src=$VER_SRC dst=$VER_DST"
pass "alembic version parity: $VER_DST"

RLS_DST=$("${PSQL[@]}" -d "$DST_DB" -tAc "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND rowsecurity=true")
RLS_SRC=$("${PSQL[@]}" -d "$SRC_DB" -tAc "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND rowsecurity=true")
[ "$RLS_DST" = "$RLS_SRC" ] || fail "RLS table count mismatch: src=$RLS_SRC dst=$RLS_DST"
# NOTE: a pure-migration DB has fewer RLS tables than production (plugin
# runtime schema-sync adds more); parity + a sane floor is what we assert.
[ "$RLS_DST" -ge 50 ] || fail "RLS count too low: $RLS_DST"
pass "RLS policies restored: $RLS_DST tables (parity with source)"

C_SRC=$("${PSQL[@]}" -d "$SRC_DB" -tAc "SET app.current_tenant_id='$TENANT_ID'; SELECT count(*) FROM contacts")
C_DST=$("${PSQL[@]}" -d "$DST_DB" -tAc "SET app.current_tenant_id='$TENANT_ID'; SELECT count(*) FROM contacts")
[ "$C_DST" = "$C_SRC" ] && [ "$C_DST" -ge 1 ] || fail "contacts row parity failed: src=$C_SRC dst=$C_DST"
pass "tenant-scoped contacts parity: $C_DST rows"

A_SRC=$("${PSQL[@]}" -d "$SRC_DB" -tAc "SELECT count(*) FROM audit_log")
A_DST=$("${PSQL[@]}" -d "$DST_DB" -tAc "SELECT count(*) FROM audit_log")
[ "$A_DST" = "$A_SRC" ] || fail "audit_log parity failed: src=$A_SRC dst=$A_DST"
pass "audit_log parity: $A_DST rows"

# RLS fail-closed check on restored DB — MUST run as a non-superuser role
# without BYPASSRLS (superusers/owners bypass RLS by design).
step "6. RLS fail-closed as restricted role"
"${PSQL[@]}" -d "$DST_DB" -c "DO \$\$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'drill_rls_user') THEN
        CREATE ROLE drill_rls_user LOGIN PASSWORD 'drill123' NOSUPERUSER NOBYPASSRLS;
    END IF;
END \$\$;" >/dev/null
"${PSQL[@]}" -d "$DST_DB" -c "GRANT USAGE ON SCHEMA public TO drill_rls_user; GRANT SELECT ON contacts TO drill_rls_user;" >/dev/null
NOCTX=$(PGPASSWORD=drill123 psql -h "$PGHOST" -p "$PGPORT" -U drill_rls_user -d "$DST_DB" -tAc "SELECT count(*) FROM contacts")
[ "$NOCTX" = "0" ] || fail "RLS fail-closed broken on restored DB: $NOCTX rows visible to restricted role without tenant"
pass "RLS fail-closed verified on restored DB (restricted role sees 0 rows)"
# Policies are bound to app roles (crm_api/crm_worker) by design; a foreign
# role is intentionally denied even WITH tenant context (no policy matches).
POLICY_ROLES=$("${PSQL[@]}" -d "$DST_DB" -tAc "SELECT string_agg(roles::text, ',') FROM pg_policies WHERE tablename='contacts'")
echo "[DRILL] contacts RLS policy roles: $POLICY_ROLES"
[[ "$POLICY_ROLES" == *crm_api* ]] || fail "contacts RLS policy not bound to crm_api role"
pass "RLS policies bound to app roles (crm_api) as designed"

echo ""
echo "============================================================"
echo -e "  ${GREEN}[DRILL] PASSED — backup→restore path proven end-to-end${NC}"
echo "============================================================"