Spaces:
Sleeping
Sleeping
File size: 4,101 Bytes
c613bf9 d876a49 c613bf9 625d4cb 2dbd2c3 c613bf9 625d4cb c613bf9 625d4cb c613bf9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | 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;
|