github.com/badrootd/celestia-core@v0.0.0-20240305091328-aa4207a4b25d/state/indexer/sink/psql/schema.sql (about)

     1  /*
     2    This file defines the database schema for the PostgresQL ("psql") event sink
     3    implementation in CometBFT. The operator must create a database and install
     4    this schema before using the database to index events.
     5   */
     6  
     7  -- The blocks table records metadata about each block.
     8  -- The block record does not include its events or transactions (see tx_results).
     9  CREATE TABLE blocks (
    10    rowid      BIGSERIAL PRIMARY KEY,
    11  
    12    height     BIGINT NOT NULL,
    13    chain_id   VARCHAR NOT NULL,
    14  
    15    -- When this block header was logged into the sink, in UTC.
    16    created_at TIMESTAMPTZ NOT NULL,
    17  
    18    UNIQUE (height, chain_id)
    19  );
    20  
    21  -- Index blocks by height and chain, since we need to resolve block IDs when
    22  -- indexing transaction records and transaction events.
    23  CREATE INDEX idx_blocks_height_chain ON blocks(height, chain_id);
    24  
    25  -- The tx_results table records metadata about transaction results.  Note that
    26  -- the events from a transaction are stored separately.
    27  CREATE TABLE tx_results (
    28    rowid BIGSERIAL PRIMARY KEY,
    29  
    30    -- The block to which this transaction belongs.
    31    block_id BIGINT NOT NULL REFERENCES blocks(rowid),
    32    -- The sequential index of the transaction within the block.
    33    index INTEGER NOT NULL,
    34    -- When this result record was logged into the sink, in UTC.
    35    created_at TIMESTAMPTZ NOT NULL,
    36    -- The hex-encoded hash of the transaction.
    37    tx_hash VARCHAR NOT NULL,
    38    -- The protobuf wire encoding of the TxResult message.
    39    tx_result BYTEA NOT NULL,
    40  
    41    UNIQUE (block_id, index)
    42  );
    43  
    44  -- The events table records events. All events (both block and transaction) are
    45  -- associated with a block ID; transaction events also have a transaction ID.
    46  CREATE TABLE events (
    47    rowid BIGSERIAL PRIMARY KEY,
    48  
    49    -- The block and transaction this event belongs to.
    50    -- If tx_id is NULL, this is a block event.
    51    block_id BIGINT NOT NULL REFERENCES blocks(rowid),
    52    tx_id    BIGINT NULL REFERENCES tx_results(rowid),
    53  
    54    -- The application-defined type label for the event.
    55    type VARCHAR NOT NULL
    56  );
    57  
    58  -- The attributes table records event attributes.
    59  CREATE TABLE attributes (
    60     event_id      BIGINT NOT NULL REFERENCES events(rowid),
    61     key           VARCHAR NOT NULL, -- bare key
    62     composite_key VARCHAR NOT NULL, -- composed type.key
    63     value         VARCHAR NULL,
    64  
    65     UNIQUE (event_id, key)
    66  );
    67  
    68  -- A joined view of events and their attributes. Events that do not have any
    69  -- attributes are represented as a single row with empty key and value fields.
    70  CREATE VIEW event_attributes AS
    71    SELECT block_id, tx_id, type, key, composite_key, value
    72    FROM events LEFT JOIN attributes ON (events.rowid = attributes.event_id);
    73  
    74  -- A joined view of all block events (those having tx_id NULL).
    75  CREATE VIEW block_events AS
    76    SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
    77    FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
    78    WHERE event_attributes.tx_id IS NULL;
    79  
    80  -- A joined view of all transaction events.
    81  CREATE VIEW tx_events AS
    82    SELECT height, index, chain_id, type, key, composite_key, value, tx_results.created_at
    83    FROM blocks JOIN tx_results ON (blocks.rowid = tx_results.block_id)
    84    JOIN event_attributes ON (tx_results.rowid = event_attributes.tx_id)
    85    WHERE event_attributes.tx_id IS NOT NULL;