Database Schema Reference

psql "$DATABASE_URL"

select approver_id, key_id, revoked, added_at, revoked_at, c.proposed_by, c.resolved_by
from approval_issuers i
join approval_issuer_changes c on c.change_id = i.added_by_change_id
order by added_at;

select x.capability, x.endpoint_url, x.status, x.registered_at, c.proposed_by, c.resolved_by
from external_connectors x
join external_connector_changes c on c.change_id = x.registration_id
order by registered_at;

SELECT 'business_transactions' AS source, created_at AS at, status::text AS detail
  FROM business_transactions WHERE business_transaction_id = $1
UNION ALL
SELECT 'execution_intents', created_at, state
  FROM execution_intents 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 intent_id, business_transaction_id, state, created_at, failure_reason
FROM execution_intents
WHERE state NOT IN ('FINALIZED', 'RESOLVED')
ORDER BY created_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 id, occurred_at, chain_hash, previous_chain_hash
FROM caller_audit_events
WHERE caller_id = $1
ORDER BY chain_position;

SELECT capability, type, COUNT(*)
FROM caller_audit_events
WHERE occurred_at > now() - interval '1 hour'
GROUP BY capability, type
ORDER BY COUNT(*) DESC;

SELECT decision_json->>'reason' AS reason, COUNT(*)
FROM refusal_records
WHERE created_at > now() - interval '24 hours'
GROUP BY reason
ORDER BY COUNT(*) DESC;

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

SELECT nonce, consumed_at, expires_at FROM consumed_nonces WHERE nonce = $1;

-- Everything still awaiting a checker
SELECT policy_name, policy_version, proposed_by, proposed_at, reason
FROM pending_policy_changes
WHERE status = 'PENDING_APPROVAL'
ORDER BY proposed_at;

-- Full approval history for one policy version
SELECT approved_by, approved_at, content_hash_before, content_hash_after
FROM policy_change_approval_records
WHERE policy_name = $1 AND policy_version = $2
ORDER BY approved_at;

SELECT 'business_transactions', COUNT(*) FROM business_transactions
UNION ALL SELECT 'executions', COUNT(*) FROM executions
UNION ALL SELECT 'execution_trust_records', COUNT(*) FROM execution_trust_records
UNION ALL SELECT 'execution_intents', COUNT(*) FROM execution_intents
UNION ALL SELECT 'caller_audit_events', COUNT(*) FROM caller_audit_events
UNION ALL SELECT 'execution_audit_events', COUNT(*) FROM execution_audit_events
UNION ALL SELECT 'refusal_records', COUNT(*) FROM refusal_records
UNION ALL SELECT 'pending_policy_changes', COUNT(*) FROM pending_policy_changes
UNION ALL SELECT 'policy_change_approval_records', COUNT(*) FROM policy_change_approval_records;

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("In files, not tracked:", fileVersions.filter(v => !dbVersions.includes(v)));
  console.log("Tracked, no file:", dbVersions.filter(v => !fileVersions.includes(v)));
  await pool.end();
})();
'

SELECT c.relname AS table_name, c.relrowsecurity AS rls_enabled
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY c.relname;

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;