schema.sqlite

PRAGMA journal_mode=WAL;

CREATE TABLE registrations (
    id INTEGER PRIMARY KEY NOT NULL,
    pubkey_ed25519 BLOB NOT NULL,
    pubkey_bls BLOB NOT NULL,
    sig_ed25519 BLOB NOT NULL,
    sig_bls BLOB NOT NULL,
    operator BLOB NOT NULL,
    contract BLOB,
    timestamp FLOAT NOT NULL DEFAULT ((julianday('now') - 2440587.5)*86400.0), /* unix epoch */

    CHECK(length(pubkey_ed25519) == 32),
    CHECK(length(pubkey_bls) == 64),
    CHECK(length(sig_ed25519) == 64),
    CHECK(length(sig_bls) == 128),
    CHECK(length(operator) == 20),
    CHECK(contract IS NULL OR length(contract) == 20)
);
CREATE INDEX registrations_operator_idx ON registrations(operator);
CREATE UNIQUE INDEX registration_pk_multi_idx ON registrations(pubkey_ed25519, contract IS NULL);

CREATE TABLE contribution_contracts (
    id INTEGER PRIMARY KEY NOT NULL,
    contract_address TEXT NOT NULL,
    status INTEGER DEFAULT 1,
    timestamp FLOAT NOT NULL DEFAULT ((julianday('now') - 2440587.5)*86400.0), /* unix epoch */

    CHECK(length(contract_address) == 42)  -- Assuming Ethereum addresses
);
CREATE UNIQUE INDEX contribution_contract_address_idx ON contribution_contracts(contract_address);