konnosDashboard
heal-auth-schema-all-tenants.sh84 lines · main
1#!/usr/bin/env bash
2# Heal Briven Auth schema drift on EVERY project database.
3#
4# Why: older "Enable Auth" tenants only got the first tables. Later columns
5# (two_factor_enabled) and tables (_briven_auth_email_templates, passkeys, …)
6# were missing → magic-link / email-OTP returned HTTP 500 (Mavi 2026-07-21).
7#
8# Safe to re-run. Skips DBs with no _briven_auth_users (auth never enabled).
9# Doltgres: no ADD COLUMN IF NOT EXISTS — we probe then ALTER.
10#
11# Usage (on the host that runs doltgres, with PGPASSWORD or .pgpass):
12# ./scripts/heal-auth-schema-all-tenants.sh
13# Or via docker:
14# DOLTGRES_CONTAINER=briven-…-doltgres-1 \
15# CONTROL_URL_FROM_API=1 \
16# ./scripts/heal-auth-schema-all-tenants.sh
17
18set -euo pipefail
19
20C="${DOLTGRES_CONTAINER:-briven-brivenfrance-uilsk6-doltgres-1}"
21API_C="${API_CONTAINER:-briven-brivenfrance-uilsk6-api-1}"
22
23if [[ -z "${PGPASSWORD:-}" ]]; then
24 if docker inspect "$API_C" &>/dev/null; then
25 URL=$(docker inspect "$API_C" --format '{{range .Config.Env}}{{println .}}{{end}}' | sed -n 's/^BRIVEN_DATABASE_URL=//p' | head -1)
26 PGPASSWORD=$(printf '%s' "$URL" | python3 -c 'import sys; from urllib.parse import urlparse,unquote; u=urlparse(sys.stdin.read().strip()); print(unquote(u.password or ""))')
27 export PGPASSWORD
28 fi
29fi
30
31psqlc() {
32 local db="$1"; shift
33 docker exec -e PGPASSWORD="$PGPASSWORD" "$C" \
34 psql -h 127.0.0.1 -U postgres -d "$db" -v ON_ERROR_STOP=0 -t -A -c "$1" 2>&1
35}
36
37mapfile -t DBS < <(docker exec -e PGPASSWORD="$PGPASSWORD" "$C" \
38 psql -h 127.0.0.1 -U postgres -d postgres -t -A \
39 -c "SELECT datname FROM pg_database WHERE datname LIKE 'proj_%' ORDER BY 1;")
40
41echo "healing ${#DBS[@]} project databases on $C …"
42healed=0
43skipped=0
44partial=0
45
46for DB in "${DBS[@]}"; do
47 [[ -z "$DB" ]] && continue
48 has_users=$(psqlc "$DB" "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='public' AND table_name='_briven_auth_users';" | tr -d '[:space:]')
49 if [[ "$has_users" != "1" ]]; then
50 echo " $DB skip (no auth tables)"
51 skipped=$((skipped + 1))
52 continue
53 fi
54
55 has_col=$(psqlc "$DB" "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='_briven_auth_users' AND column_name='two_factor_enabled';" | tr -d '[:space:]')
56 if [[ "$has_col" != "1" ]]; then
57 psqlc "$DB" 'ALTER TABLE "_briven_auth_users" ADD COLUMN two_factor_enabled boolean NOT NULL DEFAULT false;' >/dev/null || true
58 fi
59
60 for sql in \
61 'CREATE TABLE IF NOT EXISTS "_briven_auth_jwks" (id text PRIMARY KEY, public_key text NOT NULL, private_key text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), expires_at timestamptz);' \
62 'CREATE TABLE IF NOT EXISTS "_briven_auth_email_templates" (id text PRIMARY KEY, name text NOT NULL, subject text NOT NULL, html text NOT NULL, text text, active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());' \
63 'CREATE UNIQUE INDEX IF NOT EXISTS "_briven_auth_email_templates_name_uniq" ON "_briven_auth_email_templates" (name);' \
64 'CREATE TABLE IF NOT EXISTS "_briven_auth_two_factors" (id text PRIMARY KEY, secret text NOT NULL, backup_codes text NOT NULL, user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE, verified boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());' \
65 'CREATE INDEX IF NOT EXISTS "_briven_auth_two_factors_user_idx" ON "_briven_auth_two_factors" (user_id);' \
66 'CREATE TABLE IF NOT EXISTS "_briven_auth_passkeys" (id text PRIMARY KEY, name text, public_key text NOT NULL, user_id text NOT NULL REFERENCES "_briven_auth_users"(id) ON DELETE CASCADE, credential_id text NOT NULL, counter bigint NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());' \
67 'CREATE INDEX IF NOT EXISTS "_briven_auth_passkeys_user_idx" ON "_briven_auth_passkeys" (user_id);'
68 do
69 psqlc "$DB" "$sql" >/dev/null || true
70 done
71
72 email_t=$(psqlc "$DB" "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='_briven_auth_email_templates';" | tr -d '[:space:]')
73 t2fa=$(psqlc "$DB" "SELECT COUNT(*) FROM information_schema.columns WHERE table_name='_briven_auth_users' AND column_name='two_factor_enabled';" | tr -d '[:space:]')
74 if [[ "$email_t" == "1" && "$t2fa" == "1" ]]; then
75 echo " $DB HEALED_OK"
76 healed=$((healed + 1))
77 else
78 echo " $DB PARTIAL email_templates=$email_t two_factor_enabled=$t2fa"
79 partial=$((partial + 1))
80 fi
81done
82
83echo "done: healed_ok=$healed skipped=$skipped partial=$partial total=${#DBS[@]}"
84[[ "$partial" -eq 0 ]]