github.com/Team-Kujira/tendermint@v0.34.24-indexer/state/indexer/sink/psql/schema.sql (about)

     1  /*
     2    This file defines the database schema for the PostgresQL ("psql") event sink
     3    implementation in Tendermint. 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     idx           INT NOT NULL,
    62     key           VARCHAR NOT NULL, -- bare key
    63     composite_key VARCHAR NOT NULL, -- composed type.key
    64     value         VARCHAR NULL,
    65  
    66     UNIQUE (event_id, idx, key)
    67  );
    68  
    69  -- A joined view of events and their attributes. Events that do not have any
    70  -- attributes are represented as a single row with empty key and value fields.
    71  CREATE VIEW event_attributes AS
    72    SELECT block_id, tx_id, type, key, composite_key, value
    73    FROM events LEFT JOIN attributes ON (events.rowid = attributes.event_id);
    74  
    75  -- A joined view of all block events (those having tx_id NULL).
    76  CREATE VIEW block_events AS
    77    SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
    78    FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
    79    WHERE event_attributes.tx_id IS NULL;
    80  
    81  -- A joined view of all transaction events.
    82  CREATE VIEW tx_events AS
    83    SELECT height, index, chain_id, type, key, composite_key, value, tx_results.created_at
    84    FROM blocks JOIN tx_results ON (blocks.rowid = tx_results.block_id)
    85    JOIN event_attributes ON (tx_results.rowid = event_attributes.tx_id)
    86    WHERE event_attributes.tx_id IS NOT NULL;