Spaces:
Sleeping
Sleeping
| CREATE EXTENSION IF NOT EXISTS pgcrypto; | |
| CREATE TABLE IF NOT EXISTS accounts ( | |
| account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| owner_name TEXT NOT NULL, | |
| jurisdiction TEXT NOT NULL, | |
| account_type TEXT NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| is_beneficial_owner_verified BOOLEAN NOT NULL DEFAULT FALSE, | |
| opening_balance_usd NUMERIC(18,2) NOT NULL DEFAULT 0, | |
| current_balance_usd NUMERIC(18,2) NOT NULL DEFAULT 0 | |
| ); | |
| CREATE TABLE IF NOT EXISTS transactions ( | |
| tx_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| from_account UUID NOT NULL REFERENCES accounts(account_id), | |
| to_account UUID NOT NULL REFERENCES accounts(account_id), | |
| amount_usd NUMERIC(18,2) NOT NULL, | |
| fx_rate NUMERIC(10,6) NOT NULL DEFAULT 1, | |
| tx_timestamp TIMESTAMPTZ NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| memo TEXT, | |
| is_fraud BOOLEAN NOT NULL DEFAULT FALSE, | |
| fraud_type TEXT, | |
| episode_id UUID NOT NULL | |
| ); | |
| CREATE OR REPLACE VIEW transactions_visible AS | |
| SELECT | |
| tx_id, | |
| from_account, | |
| to_account, | |
| amount_usd, | |
| fx_rate, | |
| tx_timestamp, | |
| memo, | |
| episode_id | |
| FROM transactions; | |
| CREATE TABLE IF NOT EXISTS audit_log ( | |
| log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| episode_id UUID NOT NULL, | |
| agent_type TEXT NOT NULL, | |
| action TEXT NOT NULL, | |
| payload JSONB NOT NULL, | |
| logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE TABLE IF NOT EXISTS aml_violations ( | |
| violation_code TEXT PRIMARY KEY, | |
| description TEXT NOT NULL, | |
| threshold JSONB NOT NULL, | |
| severity INTEGER NOT NULL CHECK (severity BETWEEN 1 AND 5) | |
| ); | |
| CREATE TABLE IF NOT EXISTS injected_observations ( | |
| obs_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), | |
| episode_id UUID NOT NULL, | |
| content TEXT NOT NULL, | |
| target_account UUID REFERENCES accounts(account_id), | |
| injected_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE TABLE IF NOT EXISTS episode_ground_truth ( | |
| episode_id UUID PRIMARY KEY, | |
| root_fraud_tx_id UUID NOT NULL REFERENCES transactions(tx_id), | |
| fraud_type TEXT NOT NULL, | |
| fraud_archetype TEXT NOT NULL, | |
| misleading_injection_active BOOLEAN NOT NULL DEFAULT FALSE, | |
| misleading_target_account UUID REFERENCES accounts(account_id), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE TABLE IF NOT EXISTS episode_results ( | |
| episode_id UUID PRIMARY KEY, | |
| fraud_archetype TEXT NOT NULL, | |
| fraud_discovered BOOLEAN NOT NULL, | |
| reward NUMERIC(10,2) NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() | |
| ); | |
| CREATE TABLE IF NOT EXISTS baseline_accounts ( | |
| account_id UUID PRIMARY KEY, | |
| owner_name TEXT NOT NULL, | |
| jurisdiction TEXT NOT NULL, | |
| account_type TEXT NOT NULL, | |
| is_beneficial_owner_verified BOOLEAN NOT NULL, | |
| opening_balance_usd NUMERIC(18,2) NOT NULL | |
| ); | |
| CREATE OR REPLACE FUNCTION restore_episode_snapshot() RETURNS void AS $$ | |
| BEGIN | |
| -- Acquire a transaction-scoped advisory lock so concurrent /reset calls | |
| -- queue up and execute serially instead of racing on DELETE + INSERT. | |
| -- The lock is automatically released when this transaction commits/rolls back. | |
| PERFORM pg_advisory_xact_lock(8675309); | |
| -- Delete in proper order: child tables first, then parent tables | |
| -- transactions has FK to accounts, so delete transactions first | |
| DELETE FROM injected_observations; | |
| DELETE FROM audit_log; | |
| DELETE FROM episode_ground_truth; | |
| DELETE FROM episode_results; | |
| DELETE FROM transactions; | |
| DELETE FROM accounts; | |
| -- ON CONFLICT DO NOTHING as a defense-in-depth safety net | |
| INSERT INTO accounts ( | |
| account_id, | |
| owner_name, | |
| jurisdiction, | |
| account_type, | |
| is_beneficial_owner_verified, | |
| opening_balance_usd, | |
| current_balance_usd | |
| ) | |
| SELECT | |
| b.account_id, | |
| b.owner_name, | |
| b.jurisdiction, | |
| b.account_type, | |
| b.is_beneficial_owner_verified, | |
| b.opening_balance_usd, | |
| b.opening_balance_usd | |
| FROM baseline_accounts b | |
| ON CONFLICT (account_id) DO NOTHING; | |
| END; | |
| $$ LANGUAGE plpgsql; | |