Database Schema Reference

SELECT 'business_transactions' AS source, created_at AS at, status::text AS detail
  FROM business_transactions WHERE business_transaction_id = $1
UNION ALL
SELECT 'executions', created_at, (execution_json->>'status')
  FROM executions WHERE business_transaction_id = $1
UNION ALL
SELECT 'execution_trust_records', created_at, trust_record_hash
  FROM execution_trust_records WHERE business_transaction_id = $1
UNION ALL
SELECT 'refusal_records', created_at, refusal_record_hash
  FROM refusal_records WHERE business_transaction_id = $1
UNION ALL
SELECT 'execution_audit_events', occurred_at, type || COALESCE(': ' || reason, '')
  FROM execution_audit_events WHERE business_transaction_id = $1
UNION ALL
SELECT 'caller_audit_events', occurred_at, type
  FROM caller_audit_events WHERE business_transaction_id = $1
ORDER BY at;

SELECT type, route, occurred_at, capability, principal_id, reason
FROM caller_audit_events
WHERE caller_id = $1
ORDER BY occurred_at DESC LIMIT 50;

SELECT key, count, reset_time, (reset_time > now()) AS window_still_active
FROM rate_limit_counters
WHERE key = 'execute:' || $1;

SELECT policy_name, policy_version, proposed_by, proposed_at, reason
FROM pending_policy_changes
WHERE status = 'PENDING_APPROVAL'
ORDER BY proposed_at;

node -e '
require("dotenv").config();
const fs = require("fs");
const { Pool } = require("pg");
const pool = new Pool({ connectionString: process.env.DATABASE_URL, min: 1 });
const fileVersions = fs.readdirSync("supabase/migrations")
  .filter(f => f.endsWith(".sql")).map(f => f.split("_")[0]).sort();
(async () => {
  const { rows } = await pool.query("SELECT version FROM supabase_migrations.schema_migrations ORDER BY version");
  const dbVersions = rows.map(r => r.version).sort();
  console.log("Files:", fileVersions.length, "Tracked:", dbVersions.length);
  console.log("Untracked:", fileVersions.filter(v => !dbVersions.includes(v)));
  await pool.end();
})();
'

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'caller_audit_events'::regclass AND contype = 'c';

SELECT tc.table_name AS referencing_table, tc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY' AND ccu.table_name = $1;