#!/usr/bin/env bash set -Eeuo pipefail env_file="${1:-compose/.env}" source_directory="${2:-/srv/ai-soulmate/sources/ai-soulmate-server}" compose_file="${COMPOSE_FILE:-compose/docker-compose.infra.yml}" schema_file="$source_directory/00_init_schema.sql" migration_directory="$source_directory/backend/scripts" for required_command in docker git grep sha256sum; do if ! command -v "$required_command" >/dev/null 2>&1; then printf 'Required command is unavailable: %s\n' "$required_command" >&2 exit 1 fi done if [[ ! -r "$env_file" ]]; then printf 'Environment file not found: %s\n' "$env_file" >&2 exit 1 fi if [[ ! -d "$source_directory/.git" || ! -r "$schema_file" ]]; then printf 'AI SoulMate source checkout is incomplete: %s\n' "$source_directory" >&2 exit 1 fi source_branch="$(git -C "$source_directory" branch --show-current)" source_commit="$(git -C "$source_directory" rev-parse HEAD)" if [[ "$source_branch" != "local-deployment" ]]; then printf 'Expected source branch local-deployment, found: %s\n' "$source_branch" >&2 exit 1 fi if grep -q 'OWNER TO postgres' "$schema_file"; then printf 'Schema still contains the legacy postgres owner. Pull the latest local-deployment branch.\n' >&2 exit 1 fi if ! grep -q 'SET search_path TO public;' "$schema_file"; then printf 'Schema does not select the public search path. Pull the latest local-deployment branch.\n' >&2 exit 1 fi migration_names=( 005_local_deployment_schema_alignment.sql 006_add_site_announcements.sql 007_add_competition_submission_payload_columns.sql 008_add_competition_submission_attempt_tracking.sql 009_payment_hardening_preflight.sql 010_payment_hardening_expand.sql 011_payment_hardening_backfill.sql 012_payment_hardening_contract.sql 013_account_merge_hardening.sql ) for migration_name in "${migration_names[@]}"; do if [[ ! -r "$migration_directory/$migration_name" ]]; then printf 'Required migration is missing: %s\n' "$migration_name" >&2 exit 1 fi done compose_command=(docker compose --env-file "$env_file" -f "$compose_file") "${compose_command[@]}" config --quiet "${compose_command[@]}" exec -T postgres sh -ec 'pg_isready -U "$POSTGRES_USER" -d "$POSTGRES_DB"' >/dev/null psql_scalar() { local sql="$1" "${compose_command[@]}" exec -T postgres sh -ec \ 'exec psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" --tuples-only --no-align --set ON_ERROR_STOP=1 --command "$1"' \ sh "$sql" } metadata_table="$(psql_scalar "SELECT COALESCE(to_regclass('public.local_deployment_schema_migrations')::text, '');")" table_count="$(psql_scalar "SELECT count(*) FROM pg_tables WHERE schemaname = 'public';")" schema_checksum="$(sha256sum "$schema_file" | awk '{print $1}')" if [[ -z "$metadata_table" ]]; then if [[ "$table_count" != "0" ]]; then printf 'Database has %s public tables but no local migration metadata; refusing to modify it.\n' "$table_count" >&2 exit 1 fi printf 'Applying base schema from %s at %s\n' "$source_branch" "$source_commit" "${compose_command[@]}" exec -T postgres sh -ec \ 'exec psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" --set ON_ERROR_STOP=1 --single-transaction' \ < "$schema_file" psql_scalar " CREATE TABLE public.local_deployment_schema_migrations ( name text PRIMARY KEY, sha256 char(64) NOT NULL, source_commit char(40) NOT NULL, applied_at timestamptz NOT NULL DEFAULT now() ); INSERT INTO public.local_deployment_schema_migrations (name, sha256, source_commit) VALUES ('00_init_schema.sql', '$schema_checksum', '$source_commit'); " >/dev/null else recorded_schema_checksum="$(psql_scalar "SELECT sha256 FROM public.local_deployment_schema_migrations WHERE name = '00_init_schema.sql';")" if [[ "$recorded_schema_checksum" != "$schema_checksum" ]]; then printf 'Base schema checksum differs from the initialized database; use a new reviewed migration.\n' >&2 exit 1 fi fi for migration_name in "${migration_names[@]}"; do migration_file="$migration_directory/$migration_name" migration_checksum="$(sha256sum "$migration_file" | awk '{print $1}')" recorded_checksum="$(psql_scalar "SELECT COALESCE((SELECT sha256 FROM public.local_deployment_schema_migrations WHERE name = '$migration_name'), '');")" if [[ -n "$recorded_checksum" ]]; then if [[ "$recorded_checksum" != "$migration_checksum" ]]; then printf 'Migration checksum changed after application: %s\n' "$migration_name" >&2 exit 1 fi printf 'Already applied: %s\n' "$migration_name" continue fi printf 'Applying migration: %s\n' "$migration_name" "${compose_command[@]}" exec -T postgres sh -ec \ 'exec psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" --set ON_ERROR_STOP=1' \ < "$migration_file" psql_scalar " INSERT INTO public.local_deployment_schema_migrations (name, sha256, source_commit) VALUES ('$migration_name', '$migration_checksum', '$source_commit'); " >/dev/null done missing_tables="$(psql_scalar " SELECT string_agg(required_name, ', ' ORDER BY required_name) FROM unnest(ARRAY[ 'users', 'studio_jobs', 'competition_submissions', 'farm_status', 'printer_status', 'order_assignments', 'site_announcements', 'payment_attempts', 'account_merge_operations' ]) AS required_name WHERE to_regclass('public.' || required_name) IS NULL; ")" if [[ -n "$missing_tables" ]]; then printf 'Required application tables are missing: %s\n' "$missing_tables" >&2 exit 1 fi table_count="$(psql_scalar "SELECT count(*) FROM pg_tables WHERE schemaname = 'public';")" migration_count="$(psql_scalar "SELECT count(*) FROM public.local_deployment_schema_migrations;")" printf 'Application database initialization passed.\n' printf 'Source commit: %s\n' "$source_commit" printf 'Public tables: %s\n' "$table_count" printf 'Recorded schema files: %s\n' "$migration_count" printf 'Excluded incident repair: 014_repair_half_merged_accounts_20260803.sql\n'