235 lines
9.0 KiB
PL/PgSQL
235 lines
9.0 KiB
PL/PgSQL
-- Migration: Track 2-4 Schema
|
|
-- Description: Creates tables for indexed explorer (Track 2), analytics (Track 3), and operator tools (Track 4)
|
|
|
|
-- Track 2: Indexed Address Data
|
|
CREATE TABLE IF NOT EXISTS addresses (
|
|
id SERIAL PRIMARY KEY,
|
|
address VARCHAR(42) NOT NULL UNIQUE,
|
|
chain_id INTEGER NOT NULL,
|
|
first_seen_block BIGINT,
|
|
first_seen_timestamp TIMESTAMP WITH TIME ZONE,
|
|
last_seen_block BIGINT,
|
|
last_seen_timestamp TIMESTAMP WITH TIME ZONE,
|
|
tx_count_sent INTEGER DEFAULT 0,
|
|
tx_count_received INTEGER DEFAULT 0,
|
|
total_sent_wei NUMERIC(78, 0) DEFAULT 0,
|
|
total_received_wei NUMERIC(78, 0) DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_addresses_address ON addresses(address);
|
|
CREATE INDEX idx_addresses_chain_id ON addresses(chain_id);
|
|
CREATE INDEX idx_addresses_first_seen ON addresses(first_seen_timestamp);
|
|
CREATE INDEX idx_addresses_last_seen ON addresses(last_seen_timestamp);
|
|
|
|
-- Track 2: Token Transfers (ERC-20)
|
|
CREATE TABLE IF NOT EXISTS token_transfers (
|
|
id SERIAL PRIMARY KEY,
|
|
chain_id INTEGER NOT NULL,
|
|
transaction_hash VARCHAR(66) NOT NULL,
|
|
log_index INTEGER NOT NULL,
|
|
block_number BIGINT NOT NULL,
|
|
block_hash VARCHAR(66) NOT NULL,
|
|
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
token_contract VARCHAR(42) NOT NULL,
|
|
from_address VARCHAR(42) NOT NULL,
|
|
to_address VARCHAR(42) NOT NULL,
|
|
value NUMERIC(78, 0) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(chain_id, transaction_hash, log_index)
|
|
);
|
|
|
|
CREATE INDEX idx_token_transfers_token ON token_transfers(token_contract);
|
|
CREATE INDEX idx_token_transfers_from ON token_transfers(from_address);
|
|
CREATE INDEX idx_token_transfers_to ON token_transfers(to_address);
|
|
CREATE INDEX idx_token_transfers_block ON token_transfers(block_number);
|
|
CREATE INDEX idx_token_transfers_timestamp ON token_transfers(timestamp);
|
|
CREATE INDEX idx_token_transfers_tx_hash ON token_transfers(transaction_hash);
|
|
|
|
-- Track 2: Token Balances (Snapshots)
|
|
CREATE TABLE IF NOT EXISTS token_balances (
|
|
id SERIAL PRIMARY KEY,
|
|
address VARCHAR(42) NOT NULL,
|
|
token_contract VARCHAR(42) NOT NULL,
|
|
chain_id INTEGER NOT NULL,
|
|
balance NUMERIC(78, 0) NOT NULL DEFAULT 0,
|
|
balance_formatted NUMERIC(78, 18),
|
|
last_updated_block BIGINT,
|
|
last_updated_timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(address, token_contract, chain_id)
|
|
);
|
|
|
|
CREATE INDEX idx_token_balances_address ON token_balances(address);
|
|
CREATE INDEX idx_token_balances_token ON token_balances(token_contract);
|
|
CREATE INDEX idx_token_balances_chain ON token_balances(chain_id);
|
|
|
|
-- Track 2: Internal Transactions
|
|
CREATE TABLE IF NOT EXISTS internal_transactions (
|
|
id SERIAL PRIMARY KEY,
|
|
chain_id INTEGER NOT NULL,
|
|
transaction_hash VARCHAR(66) NOT NULL,
|
|
block_number BIGINT NOT NULL,
|
|
block_hash VARCHAR(66) NOT NULL,
|
|
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
trace_address INTEGER[],
|
|
from_address VARCHAR(42) NOT NULL,
|
|
to_address VARCHAR(42),
|
|
value NUMERIC(78, 0) NOT NULL DEFAULT 0,
|
|
gas_limit NUMERIC(78, 0),
|
|
gas_used NUMERIC(78, 0),
|
|
call_type VARCHAR(50),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_internal_txs_tx_hash ON internal_transactions(transaction_hash);
|
|
CREATE INDEX idx_internal_txs_from ON internal_transactions(from_address);
|
|
CREATE INDEX idx_internal_txs_to ON internal_transactions(to_address);
|
|
CREATE INDEX idx_internal_txs_block ON internal_transactions(block_number);
|
|
CREATE INDEX idx_internal_txs_timestamp ON internal_transactions(timestamp);
|
|
|
|
-- Track 3: Analytics Flows (Address → Address)
|
|
CREATE TABLE IF NOT EXISTS analytics_flows (
|
|
id SERIAL PRIMARY KEY,
|
|
chain_id INTEGER NOT NULL,
|
|
from_address VARCHAR(42) NOT NULL,
|
|
to_address VARCHAR(42) NOT NULL,
|
|
token_contract VARCHAR(42),
|
|
total_amount NUMERIC(78, 0) NOT NULL DEFAULT 0,
|
|
transfer_count INTEGER NOT NULL DEFAULT 0,
|
|
first_seen TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
last_seen TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(chain_id, from_address, to_address, token_contract)
|
|
);
|
|
|
|
CREATE INDEX idx_analytics_flows_from ON analytics_flows(from_address);
|
|
CREATE INDEX idx_analytics_flows_to ON analytics_flows(to_address);
|
|
CREATE INDEX idx_analytics_flows_token ON analytics_flows(token_contract);
|
|
CREATE INDEX idx_analytics_flows_last_seen ON analytics_flows(last_seen);
|
|
|
|
-- Track 3: Bridge Analytics History
|
|
CREATE TABLE IF NOT EXISTS analytics_bridge_history (
|
|
id SERIAL PRIMARY KEY,
|
|
chain_from INTEGER NOT NULL,
|
|
chain_to INTEGER NOT NULL,
|
|
token_contract VARCHAR(42),
|
|
transfer_hash VARCHAR(66) NOT NULL,
|
|
from_address VARCHAR(42) NOT NULL,
|
|
to_address VARCHAR(42) NOT NULL,
|
|
amount NUMERIC(78, 0) NOT NULL,
|
|
timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_bridge_history_chains ON analytics_bridge_history(chain_from, chain_to);
|
|
CREATE INDEX idx_bridge_history_token ON analytics_bridge_history(token_contract);
|
|
CREATE INDEX idx_bridge_history_timestamp ON analytics_bridge_history(timestamp);
|
|
CREATE INDEX idx_bridge_history_from ON analytics_bridge_history(from_address);
|
|
|
|
-- Track 3: Token Distribution (Materialized View)
|
|
CREATE MATERIALIZED VIEW IF NOT EXISTS token_distribution AS
|
|
SELECT
|
|
token_contract,
|
|
chain_id,
|
|
COUNT(DISTINCT address) as holder_count,
|
|
SUM(balance) as total_balance,
|
|
AVG(balance) as avg_balance,
|
|
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY balance) as median_balance,
|
|
MAX(balance) as max_balance,
|
|
MIN(balance) as min_balance,
|
|
COUNT(*) FILTER (WHERE balance > 0) as active_holders,
|
|
NOW() as last_updated
|
|
FROM token_balances
|
|
GROUP BY token_contract, chain_id;
|
|
|
|
CREATE UNIQUE INDEX idx_token_distribution_unique ON token_distribution(token_contract, chain_id);
|
|
CREATE INDEX idx_token_distribution_holders ON token_distribution(holder_count);
|
|
|
|
-- Track 4: Operator Events (Audit Log)
|
|
CREATE TABLE IF NOT EXISTS operator_events (
|
|
id SERIAL PRIMARY KEY,
|
|
event_type VARCHAR(100) NOT NULL,
|
|
chain_id INTEGER,
|
|
operator_address VARCHAR(42) NOT NULL,
|
|
target_resource VARCHAR(200),
|
|
action VARCHAR(100) NOT NULL,
|
|
details JSONB,
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_operator_events_type ON operator_events(event_type);
|
|
CREATE INDEX idx_operator_events_operator ON operator_events(operator_address);
|
|
CREATE INDEX idx_operator_events_timestamp ON operator_events(timestamp);
|
|
CREATE INDEX idx_operator_events_chain ON operator_events(chain_id);
|
|
|
|
-- Track 4: Operator IP Whitelist
|
|
CREATE TABLE IF NOT EXISTS operator_ip_whitelist (
|
|
id SERIAL PRIMARY KEY,
|
|
operator_address VARCHAR(42) NOT NULL,
|
|
ip_address INET NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(operator_address, ip_address)
|
|
);
|
|
|
|
CREATE INDEX idx_operator_whitelist_operator ON operator_ip_whitelist(operator_address);
|
|
CREATE INDEX idx_operator_whitelist_ip ON operator_ip_whitelist(ip_address);
|
|
|
|
-- Track 4: Operator Roles
|
|
CREATE TABLE IF NOT EXISTS operator_roles (
|
|
id SERIAL PRIMARY KEY,
|
|
address VARCHAR(42) NOT NULL UNIQUE,
|
|
track_level INTEGER NOT NULL DEFAULT 4,
|
|
roles TEXT[],
|
|
approved BOOLEAN DEFAULT FALSE,
|
|
approved_by VARCHAR(42),
|
|
approved_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_operator_roles_address ON operator_roles(address);
|
|
CREATE INDEX idx_operator_roles_approved ON operator_roles(approved);
|
|
|
|
-- Wallet Authentication: Nonce storage
|
|
CREATE TABLE IF NOT EXISTS wallet_nonces (
|
|
id SERIAL PRIMARY KEY,
|
|
address VARCHAR(42) NOT NULL UNIQUE,
|
|
nonce VARCHAR(64) NOT NULL,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_wallet_nonces_address ON wallet_nonces(address);
|
|
CREATE INDEX idx_wallet_nonces_expires ON wallet_nonces(expires_at);
|
|
|
|
-- Update triggers for updated_at
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
CREATE TRIGGER update_addresses_updated_at BEFORE UPDATE ON addresses
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_token_balances_updated_at BEFORE UPDATE ON token_balances
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_analytics_flows_updated_at BEFORE UPDATE ON analytics_flows
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_operator_roles_updated_at BEFORE UPDATE ON operator_roles
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|