schema.sql

-- Enhanced Avail Blockchain Schema
-- Based on domain-driven architecture with comprehensive entity support

CREATE SCHEMA IF NOT EXISTS app;

-- Core Blockchain Entities
CREATE TABLE IF NOT EXISTS app.blocks (
    id TEXT PRIMARY KEY,
    number BIGINT NOT NULL UNIQUE,
    timestamp TIMESTAMP NOT NULL,
    parent_hash TEXT NOT NULL,
    state_root TEXT NOT NULL,
    extrinsics_root TEXT NOT NULL,
    spec_version INTEGER,
    validator_address TEXT,
    block_size BIGINT DEFAULT 0,
    block_weight BIGINT DEFAULT 0,
    total_fees NUMERIC(78,0) DEFAULT 0,
    extrinsics_count INTEGER DEFAULT 0,
    events_count INTEGER DEFAULT 0,
    transfer_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app.extrinsics (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    index INTEGER NOT NULL,
    hash TEXT NOT NULL,
    method TEXT NOT NULL,
    section TEXT NOT NULL,
    args TEXT,
    signer TEXT,
    nonce INTEGER,
    signature TEXT,
    tip NUMERIC(78,0) DEFAULT 0,
    is_signed BOOLEAN DEFAULT FALSE,
    is_success BOOLEAN DEFAULT TRUE,
    timestamp TIMESTAMP NOT NULL,
    weight BIGINT,
    fee_paid NUMERIC(78,0),
    lifetime TEXT,
    parameters TEXT,
    signature_info TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

CREATE TABLE IF NOT EXISTS app.events (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    extrinsic_index INTEGER,
    index INTEGER NOT NULL,
    section TEXT NOT NULL,
    method TEXT NOT NULL,
    data TEXT,
    phase TEXT,
    topics TEXT[],
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

-- Account Management
CREATE TABLE IF NOT EXISTS app.accounts (
    address TEXT PRIMARY KEY,
    account_type TEXT DEFAULT 'regular',
    first_seen_block BIGINT,
    last_activity_block BIGINT,
    balance NUMERIC(78,0) DEFAULT 0,
    reserved_balance NUMERIC(78,0) DEFAULT 0,
    free_balance NUMERIC(78,0) DEFAULT 0,
    locked_balance NUMERIC(78,0) DEFAULT 0,
    transaction_count INTEGER DEFAULT 0,
    transfer_count INTEGER DEFAULT 0,
    identity_info TEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Financial Entities
CREATE TABLE IF NOT EXISTS app.transfers (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    extrinsic_index INTEGER,
    event_index INTEGER NOT NULL,
    from_address TEXT,
    to_address TEXT,
    amount NUMERIC(78,0) NOT NULL,
    transfer_type TEXT NOT NULL,
    success BOOLEAN DEFAULT TRUE,
    timestamp TIMESTAMP NOT NULL,
    fee NUMERIC(78,0) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

-- Avail-Specific Data Availability
CREATE TABLE IF NOT EXISTS app.data_submissions (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    extrinsic_index INTEGER,
    event_index INTEGER NOT NULL,
    application_id TEXT NOT NULL,
    submitter_address TEXT NOT NULL,
    data_index INTEGER,
    data_size BIGINT,
    data_hash TEXT,
    submission_fee NUMERIC(78,0),
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

CREATE TABLE IF NOT EXISTS app.applications (
    id TEXT PRIMARY KEY,
    application_id TEXT NOT NULL UNIQUE,
    application_key TEXT,
    owner_account TEXT NOT NULL,
    creation_block BIGINT NOT NULL,
    creation_event_index INTEGER,
    status TEXT DEFAULT 'active',
    last_updated_block BIGINT,
    last_updated_by TEXT,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_account) REFERENCES app.accounts(address)
);

-- Balance Changes Tracking
CREATE TABLE IF NOT EXISTS app.balances (
    id TEXT PRIMARY KEY,
    address TEXT NOT NULL,
    change_type TEXT NOT NULL, -- 'endowed', 'reserved', 'unreserved', 'transfer', etc
    amount NUMERIC(78,0) NOT NULL,
    block_number BIGINT NOT NULL,
    extrinsic_index INTEGER,
    event_index INTEGER NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (address) REFERENCES app.accounts(address)
);

-- Vector Messages for Cross-Chain Operations
CREATE TABLE IF NOT EXISTS app.vector_messages (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    message_id TEXT NOT NULL,
    origin_chain TEXT,
    destination_chain TEXT,
    message_data TEXT,
    message_type TEXT,
    sender_address TEXT,
    receiver_address TEXT,
    status TEXT DEFAULT 'pending',
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

CREATE TABLE IF NOT EXISTS app.rollups (
    app_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    owner_account TEXT,
    first_seen_block BIGINT NOT NULL,
    last_active_block BIGINT NOT NULL,
    total_submissions INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app.kate_commitments (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    commitment_hash TEXT NOT NULL,
    commitment_data TEXT NOT NULL,
    commitment_index INTEGER,
    polynomial_commitment TEXT,
    data_root TEXT,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

-- Staking & Consensus
CREATE TABLE IF NOT EXISTS app.validators (
    stash_account TEXT PRIMARY KEY,
    controller_account TEXT,
    commission_rate NUMERIC(10,2),
    self_stake NUMERIC(78,0),
    total_stake NUMERIC(78,0),
    nominator_count INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    preferences TEXT,
    blocks_authored INTEGER DEFAULT 0,
    first_seen_block BIGINT,
    last_active_block BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app.nominations (
    id TEXT PRIMARY KEY,
    nominator_account TEXT NOT NULL,
    validator_account TEXT NOT NULL,
    stake_amount NUMERIC(78,0) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    block_number BIGINT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (nominator_account) REFERENCES app.accounts(address),
    FOREIGN KEY (validator_account) REFERENCES app.validators(stash_account)
);

CREATE TABLE IF NOT EXISTS app.rewards (
    id TEXT PRIMARY KEY,
    era_index INTEGER NOT NULL,
    validator_account TEXT NOT NULL,
    nominator_account TEXT,
    reward_amount NUMERIC(78,0) NOT NULL,
    reward_type TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (validator_account) REFERENCES app.validators(stash_account)
);

CREATE TABLE IF NOT EXISTS app.eras (
    era_index INTEGER PRIMARY KEY,
    start_block BIGINT NOT NULL,
    end_block BIGINT,
    total_stake NUMERIC(78,0),
    validator_count INTEGER,
    reward_points INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app.sessions (
    session_index INTEGER PRIMARY KEY,
    era_index INTEGER NOT NULL,
    start_block BIGINT NOT NULL,
    end_block BIGINT,
    validator_set TEXT[],
    session_length INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (era_index) REFERENCES app.eras(era_index)
);

CREATE TABLE IF NOT EXISTS app.staking_events (
    id TEXT PRIMARY KEY,
    block_id TEXT NOT NULL,
    block_number BIGINT NOT NULL,
    event_index INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    account TEXT NOT NULL,
    amount NUMERIC(78,0),
    validator_account TEXT,
    era_index INTEGER,
    timestamp TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (block_id) REFERENCES app.blocks(id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_blocks_number ON app.blocks(number);
CREATE INDEX IF NOT EXISTS idx_blocks_timestamp ON app.blocks(timestamp);
CREATE INDEX IF NOT EXISTS idx_extrinsics_block ON app.extrinsics(block_number);
CREATE INDEX IF NOT EXISTS idx_extrinsics_signer ON app.extrinsics(signer);
CREATE INDEX IF NOT EXISTS idx_events_block ON app.events(block_number);
CREATE INDEX IF NOT EXISTS idx_events_section_method ON app.events(section, method);
CREATE INDEX IF NOT EXISTS idx_transfers_from ON app.transfers(from_address);
CREATE INDEX IF NOT EXISTS idx_transfers_to ON app.transfers(to_address);
CREATE INDEX IF NOT EXISTS idx_transfers_block ON app.transfers(block_number);
CREATE INDEX IF NOT EXISTS idx_accounts_activity ON app.accounts(last_activity_block);
CREATE INDEX IF NOT EXISTS idx_data_submissions_app ON app.data_submissions(application_id);
CREATE INDEX IF NOT EXISTS idx_data_submissions_block ON app.data_submissions(block_number);
CREATE INDEX IF NOT EXISTS idx_rollups_active ON app.rollups(last_active_block);
CREATE INDEX IF NOT EXISTS idx_validators_active ON app.validators(active);
CREATE INDEX IF NOT EXISTS idx_nominations_validator ON app.nominations(validator_account);
CREATE INDEX IF NOT EXISTS idx_rewards_era ON app.rewards(era_index);
CREATE INDEX IF NOT EXISTS idx_staking_events_block ON app.staking_events(block_number);
CREATE INDEX IF NOT EXISTS idx_balances_address ON app.balances(address);
CREATE INDEX IF NOT EXISTS idx_balances_block ON app.balances(block_number);
CREATE INDEX IF NOT EXISTS idx_balances_type ON app.balances(change_type);
CREATE INDEX IF NOT EXISTS idx_applications_owner ON app.applications(owner_account);
CREATE INDEX IF NOT EXISTS idx_applications_status ON app.applications(status);
CREATE INDEX IF NOT EXISTS idx_kate_commitments_block ON app.kate_commitments(block_number);
CREATE INDEX IF NOT EXISTS idx_vector_messages_chain ON app.vector_messages(origin_chain, destination_chain);
CREATE INDEX IF NOT EXISTS idx_vector_messages_status ON app.vector_messages(status);