crystalai/Ai-auto-training-simulations-software-app

OMNI-333 Supabase / PostgreSQL Deployment Pack

This pack includes real SQL for:

1. A Construction Monitor using a SECURITY DEFINER trigger function on public.blueprint_pages
2. A Deconstruction Detector using a scheduled PostgreSQL function
3. A Reconstruction Engine using a scheduled PostgreSQL function
4. pg_cron job definitions for Supabase scheduled execution
5. A deployment checklist and rollback script

Important implementation notes

• This pack assumes public.blueprint_pages exists and includes at least:
  • content_json jsonb
  • content_hash text
  • integrity_verified boolean
  • drift_score numeric
  • finality_sig text
  • parent_id uuid
• This pack adds missing support columns if they do not already exist.
• The trigger is implemented as BEFORE INSERT OR UPDATE rather than AFTER INSERT/UPDATE. This is deliberate and correct: it lets PostgreSQL set classification and security fields atomically on the row without recursive self-updates.
• The scheduled jobs use pg_cron. On Supabase, ensure pg_cron is enabled for your project/database.
• The detector/reconstruction functions are SQL/PLpgSQL only. They keep the automation inside the DB trust boundary, consistent with your fail-closed/RLS-minimising design.

Files

• 01_support_schema.sql – support schema, tables, indexes, config, and helper functions
• 02_construction_monitor.sql – SECURITY DEFINER trigger function + trigger
• 03_detector_and_reconstruction.sql – detector and reconstruction functions
• 04_supabase_scheduled_jobs.sql – pg_cron jobs for hourly detector and frequent reconstruction
• 05_deployment_checklist.md – step-by-step deployment sequence
• 06_rollback.sql – rollback / uninstallation script

Configuration you must set after deployment

Populate root parent IDs in public.omni333_system_config:

• real_root_parent_id
• game_root_parent_id
• bridge_root_parent_id

This is how the construction monitor assigns parent pages when parent_id is missing.