cred402/db/migrations/0001_init.sql
2026-06-16 02:50:52 +03:00

208 lines
7.5 KiB
PL/PgSQL

-- Cred402 indexer schema (p2 §10.1)
-- Postgres tables hydrated by the event indexer from Casper streaming events.
-- The off-chain indexer (Go, see ROADMAP.md) writes here; the API reads here in
-- production. The in-memory ledger simulation mirrors this exact shape.
BEGIN;
CREATE TABLE IF NOT EXISTS operators (
id BIGSERIAL PRIMARY KEY,
public_key TEXT NOT NULL UNIQUE,
kyb_status TEXT NOT NULL DEFAULT 'unverified',
jurisdiction TEXT,
risk_flags JSONB NOT NULL DEFAULT '[]',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS agents (
id BIGSERIAL PRIMARY KEY,
agent_id TEXT NOT NULL UNIQUE,
public_key TEXT NOT NULL,
operator_id BIGINT REFERENCES operators(id),
service_type TEXT NOT NULL,
metadata_hash TEXT,
status TEXT NOT NULL DEFAULT 'active',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS agent_passports (
id BIGSERIAL PRIMARY KEY,
agent_id TEXT NOT NULL UNIQUE REFERENCES agents(agent_id),
reputation_score INT NOT NULL DEFAULT 0,
credit_score INT NOT NULL DEFAULT 0,
total_revenue NUMERIC(78,0) NOT NULL DEFAULT 0, -- U512 motes
total_receipts BIGINT NOT NULL DEFAULT 0,
dispute_rate_bps INT NOT NULL DEFAULT 0,
credit_limit NUMERIC(78,0) NOT NULL DEFAULT 0,
outstanding_debt NUMERIC(78,0) NOT NULL DEFAULT 0,
stake NUMERIC(78,0) NOT NULL DEFAULT 0,
last_active_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS api_keys (
id BIGSERIAL PRIMARY KEY,
operator_id BIGINT NOT NULL REFERENCES operators(id),
key_hash TEXT NOT NULL UNIQUE,
scopes JSONB NOT NULL DEFAULT '[]',
status TEXT NOT NULL DEFAULT 'active',
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS rwa_assets (
id BIGSERIAL PRIMARY KEY,
rwa_id TEXT NOT NULL UNIQUE,
asset_type TEXT NOT NULL,
issuer TEXT,
jurisdiction TEXT,
metadata_hash TEXT,
document_bundle_hash TEXT,
status TEXT NOT NULL DEFAULT 'active',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS rwa_evidence (
id BIGSERIAL PRIMARY KEY,
evidence_id TEXT NOT NULL UNIQUE,
rwa_id TEXT NOT NULL REFERENCES rwa_assets(rwa_id),
agent_id TEXT NOT NULL REFERENCES agents(agent_id),
receipt_id TEXT,
evidence_type TEXT NOT NULL,
evidence_hash TEXT NOT NULL,
source_hash TEXT,
confidence_bps INT NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'submitted',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS x402_receipts (
id BIGSERIAL PRIMARY KEY,
receipt_id TEXT NOT NULL UNIQUE,
payer_agent_id TEXT NOT NULL,
seller_agent_id TEXT NOT NULL REFERENCES agents(agent_id),
amount NUMERIC(78,0) NOT NULL,
asset TEXT NOT NULL DEFAULT 'CSPR',
network TEXT NOT NULL DEFAULT 'casper',
service_type TEXT NOT NULL,
request_hash TEXT,
result_hash TEXT NOT NULL,
payment_proof_hash TEXT NOT NULL,
nonce TEXT NOT NULL,
expires_at TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'pending',
transaction_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finalized_at TIMESTAMPTZ,
-- Replay protection (p2 §6.3): nonce unique per payer; proof globally unique.
UNIQUE (payer_agent_id, nonce),
UNIQUE (payment_proof_hash)
);
CREATE TABLE IF NOT EXISTS credit_lines (
id BIGSERIAL PRIMARY KEY,
agent_id TEXT NOT NULL UNIQUE REFERENCES agents(agent_id),
max_credit NUMERIC(78,0) NOT NULL DEFAULT 0,
drawn NUMERIC(78,0) NOT NULL DEFAULT 0,
interest_rate_bps INT NOT NULL DEFAULT 0,
origination_fee_bps INT NOT NULL DEFAULT 0,
health_factor_bps INT NOT NULL DEFAULT 0,
due_timestamp TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'active',
opened_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS credit_draws (
id BIGSERIAL PRIMARY KEY,
draw_id TEXT NOT NULL UNIQUE,
agent_id TEXT NOT NULL REFERENCES agents(agent_id),
amount NUMERIC(78,0) NOT NULL,
purpose_hash TEXT,
transaction_hash TEXT,
due_timestamp TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'open',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS repayments (
id BIGSERIAL PRIMARY KEY,
repayment_id TEXT NOT NULL UNIQUE,
draw_id TEXT REFERENCES credit_draws(draw_id),
agent_id TEXT NOT NULL REFERENCES agents(agent_id),
amount NUMERIC(78,0) NOT NULL,
interest_amount NUMERIC(78,0) NOT NULL DEFAULT 0,
transaction_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS pool_positions (
id BIGSERIAL PRIMARY KEY,
account TEXT NOT NULL,
shares NUMERIC(78,0) NOT NULL DEFAULT 0,
principal NUMERIC(78,0) NOT NULL DEFAULT 0,
accrued_yield NUMERIC(78,0) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS disputes (
id BIGSERIAL PRIMARY KEY,
dispute_id TEXT NOT NULL UNIQUE,
target_type TEXT NOT NULL, -- agent | receipt | evidence
target_id TEXT NOT NULL,
opened_by TEXT NOT NULL,
reason TEXT NOT NULL,
evidence_hash TEXT,
status TEXT NOT NULL DEFAULT 'opened',
verdict TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
resolved_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS agent_audit_logs (
id BIGSERIAL PRIMARY KEY,
agent_id TEXT NOT NULL REFERENCES agents(agent_id),
workflow_id TEXT,
action_type TEXT NOT NULL,
input_hash TEXT,
output_hash TEXT,
tool_name TEXT,
transaction_hash TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS risk_scores (
id BIGSERIAL PRIMARY KEY,
subject_type TEXT NOT NULL, -- agent | rwa
subject_id TEXT NOT NULL,
score INT NOT NULL,
risk_bucket TEXT NOT NULL,
model_version TEXT NOT NULL,
reason_codes JSONB NOT NULL DEFAULT '[]',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS protocol_events (
id BIGSERIAL PRIMARY KEY,
chain TEXT NOT NULL DEFAULT 'casper-test',
block_hash TEXT,
block_height BIGINT,
transaction_hash TEXT,
contract TEXT NOT NULL,
event_type TEXT NOT NULL,
payload JSONB NOT NULL,
observed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_receipts_seller ON x402_receipts (seller_agent_id);
CREATE INDEX IF NOT EXISTS idx_receipts_status ON x402_receipts (status);
CREATE INDEX IF NOT EXISTS idx_evidence_rwa ON rwa_evidence (rwa_id);
CREATE INDEX IF NOT EXISTS idx_events_type ON protocol_events (event_type);
CREATE INDEX IF NOT EXISTS idx_events_contract ON protocol_events (contract);
CREATE INDEX IF NOT EXISTS idx_disputes_target ON disputes (target_type, target_id);
COMMIT;