Skip to content

schema.md

Latest commit

 

History

History
319 lines (219 loc) · 14.8 KB

schema.md

File metadata and controls

319 lines (219 loc) · 14.8 KB
Raw

Database Schema

This document describes the PostgreSQL schema used by Soroban Pulse. All tables are created and evolved through the migration files in migrations/.

Entity-Relationship Diagram

erDiagram
    events {
        uuid        id           PK  "gen_random_uuid()"
        text        contract_id  NK  "NOT NULL"
        text        event_type   NK  "NOT NULL"
        text        tx_hash      NK  "NOT NULL"
        bigint      ledger           "NOT NULL"
        timestamptz timestamp        "NOT NULL"
        jsonb       event_data       "NOT NULL"
        timestamptz created_at       "NOT NULL DEFAULT NOW()"
    }
Loading

The events table has no foreign keys — it is a self-contained append-only log of indexed Soroban events.

events Table

The central (and only) table. Each row represents one Soroban event emitted by a smart contract on the Stellar network.

Columns

Column Type Nullable Constraints Purpose
id UUID NOT NULL PRIMARY KEY, default gen_random_uuid() Surrogate primary key. Generated server-side; never supplied by the RPC. Used as the Last-Event-ID value in SSE streams for resumable connections.
contract_id TEXT NOT NULL Part of unique constraint Stellar contract address (56-character Strkey, always starts with C). Example: CAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD2KM.
event_type TEXT NOT NULL Part of unique constraint, CHECK via application Soroban event category. One of contract, diagnostic, or system. Stored as plain text rather than a Postgres enum so that new types added by the Stellar protocol do not require a schema migration.
tx_hash TEXT NOT NULL Part of unique constraint SHA-256 hex digest of the transaction that emitted the event (64 lowercase hex characters). Example: a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2.
ledger BIGINT NOT NULL — Ledger sequence number at which the event was emitted. Used for range queries and ordering.
timestamp TIMESTAMPTZ NOT NULL — Ledger close time reported by the RPC (ledgerClosedAt). Stored with timezone (UTC).
event_data JSONB NOT NULL CHECK constraint on structure Structured event payload. Always a JSON object with two keys: value (object or null) and topic (array or null). The CHECK constraint check_event_data_structure enforces this shape. Example: {"value": {"amount": 1000}, "topic": [{"sym": "swap"}]}.
created_at TIMESTAMPTZ NOT NULL Default NOW() Wall-clock time when the row was inserted by the indexer. Distinct from timestamp (ledger close time). Used for SSE replay queries (Last-Event-ID resumption).

Constraints

Primary Key

PRIMARY KEY (id)

id is a UUID generated by gen_random_uuid(). It is stable across re-indexing attempts because ON CONFLICT DO NOTHING is used on insert — the UUID is only assigned once, on first successful insert.

Unique Constraint — idx_events_tx_hash_contract

UNIQUE (tx_hash, contract_id, event_type)

Rationale: A single transaction can emit multiple Soroban events, potentially from different contracts and of different types. The combination (tx_hash, contract_id, event_type) is the natural deduplication key that matches the Stellar protocol's event identity. Using this as the conflict target for ON CONFLICT DO NOTHING makes the indexer idempotent — re-processing a ledger range (e.g. during a replay job) never produces duplicate rows.

A simpler PRIMARY KEY (tx_hash, contract_id, event_type) was not chosen because:

  • The UUID id is needed as a stable, opaque cursor value for SSE Last-Event-ID resumption.
  • Composite primary keys make foreign key references from future tables more verbose.

CHECK Constraint — check_event_data_structure

CHECK (
    (event_data->'value' IS NULL OR jsonb_typeof(event_data->'value') = 'object') AND
    (event_data->'topic' IS NULL OR jsonb_typeof(event_data->'topic') = 'array')
)

Enforces that event_data always has the expected shape. Prevents malformed payloads from being stored if the indexer or a replay job encounters unexpected RPC output.

Indexes

idx_events_contract_ledger (composite)

CREATE INDEX idx_events_contract_ledger ON events(contract_id, ledger DESC);

Optimises: GET /v1/events/contract/{contract_id} — filters by contract_id and sorts by ledger DESC. The composite index satisfies both the equality filter and the sort in a single index scan, avoiding a separate sort step.

idx_events_tx_ledger (composite)

CREATE INDEX idx_events_tx_ledger ON events(tx_hash, ledger DESC);

Optimises: GET /v1/events/tx/{tx_hash} — filters by tx_hash and sorts by ledger DESC. Same rationale as above.

idx_events_ledger_desc

CREATE INDEX idx_events_ledger_desc ON events(ledger DESC);

Optimises: GET /v1/events (paginated list) — the global events feed is always ordered by ledger DESC. A descending index avoids a full-table sort. The original ascending idx_events_ledger was dropped in migration 20260325000000 once all queries were confirmed to use ORDER BY ledger DESC.

idx_events_event_data_gin (GIN, CONCURRENTLY)

CREATE INDEX CONCURRENTLY idx_events_event_data_gin
    ON events USING GIN (event_data jsonb_path_ops);

Optimises: JSON containment queries on event_data using the @> operator (e.g. filtering by topic value). Built with CONCURRENTLY so it does not lock the table during creation. The migration file is marked -- no-transaction because CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

idx_events_tx_hash_contract (unique)

UNIQUE INDEX idx_events_tx_hash_contract ON events(tx_hash, contract_id, event_type);

Serves dual purpose: enforces the deduplication constraint (see above) and supports fast lookups by (tx_hash, contract_id, event_type).

Migration History

File Description
20260314000000_create_events.sql Initial schema: events table, single-column indexes on contract_id, tx_hash, ledger, and the unique constraint.
20260325000000_optimize_ledger_index.sql Replace ascending idx_events_ledger with descending idx_events_ledger_desc.
20260325000001_composite_indices.sql Add composite indexes idx_events_contract_ledger and idx_events_tx_ledger; drop now-redundant single-column indexes.
20260424000000_gin_index_event_data.sql Add GIN index on event_data for JSON containment queries (no-transaction migration).
20260425000001_event_data_validation.sql Add check_event_data_structure CHECK constraint.

| 20260425000001_event_data_validation.sql | Add check_event_data_structure CHECK constraint. |

File Description
20260428000002_matview_daily_summary.sql Create events_daily_summary materialized view and its unique index.
20260428000003_matview_contract_summary.sql Create events_contract_summary materialized view and its unique index.
20260428000004_matview_hourly_volume.sql Create events_hourly_volume materialized view and its unique index.

Materialized Views

Three materialized views pre-compute aggregations over the events table. They are refreshed every 5 minutes (configurable via STATS_REFRESH_INTERVAL_SECS) by a background task using REFRESH MATERIALIZED VIEW CONCURRENTLY, which does not lock the view for reads.

Each view has a UNIQUE index — required by PostgreSQL for CONCURRENTLY refresh.

events_daily_summary

Pre-computes event counts grouped by calendar date and event type.

SELECT DATE(timestamp) AS event_date, event_type, COUNT(*) AS event_count
FROM events
GROUP BY DATE(timestamp), event_type;
Column Type Description
event_date DATE Calendar date of the events (part of unique key)
event_type TEXT Event type: contract, diagnostic, or system (part of unique key)
event_count BIGINT Number of events on that date with that type

Used by: GET /v1/events/stats — per-type totals and 24h/7d windowed counts.

events_contract_summary

Pre-computes total event count and latest ledger per contract.

SELECT contract_id, COUNT(*) AS event_count, MAX(ledger) AS latest_ledger
FROM events
GROUP BY contract_id;
Column Type Description
contract_id TEXT Stellar contract address (unique key)
event_count BIGINT Total events emitted by this contract
latest_ledger BIGINT Highest ledger sequence seen for this contract

Used by: GET /v1/events/stats — top 10 contracts by event count.

events_hourly_volume

Pre-computes event counts per hour for the last 7 days.

SELECT DATE_TRUNC('hour', timestamp) AS event_hour, COUNT(*) AS event_count
FROM events
WHERE timestamp >= NOW() - INTERVAL '7 days'
GROUP BY DATE_TRUNC('hour', timestamp);
Column Type Description
event_hour TIMESTAMPTZ Hour bucket (truncated to the hour, unique key)
event_count BIGINT Number of events in that hour

Note: Because the WHERE clause uses NOW() at view-creation time, the view must be refreshed regularly to keep the 7-day window current. The background refresh task handles this automatically.

Refresh Background Task

A Tokio task (src/stats_refresh.rs) runs at startup and then on a configurable interval:

STATS_REFRESH_INTERVAL_SECS=300   # default: 5 minutes

It issues REFRESH MATERIALIZED VIEW CONCURRENTLY for each view in sequence. Failures are logged as errors but do not crash the service — the views simply serve slightly stale data until the next successful refresh.

Lock Timeout Behaviour

Each materialized view refresh acquires a dedicated pool connection, sets lock_timeout = '5s', and resets it before returning the connection. If a concurrent long-running query holds a conflicting lock, the refresh is skipped (a WARN is logged) and retried on the next scheduled interval. This prevents a stuck refresh from blocking the connection pool or cascading into API failures.

Metrics emitted per refresh cycle:

  • soroban_pulse_matview_refresh_duration_seconds{view} — histogram of successful refresh latency.
  • soroban_pulse_matview_refresh_timeout_total{view} — counter incremented each time a lock timeout causes a skip.

Index Monitoring

The background task in src/index_monitor.rs runs on every cycle (INDEX_CHECK_INTERVAL_HOURS, default 24 h) and performs two checks:

  1. EXPLAIN-based checks — runs EXPLAIN (FORMAT JSON) on representative queries and warns if the query planner falls back to a sequential scan instead of the expected index.

  2. pg_stat_user_indexes scan counts — queries pg_stat_user_indexes and emits per-index scan counts as Prometheus metrics:

    • soroban_pulse_unused_indexes_total — gauge reporting how many public-schema indexes have idx_scan = 0 since the last statistics reset.
    • soroban_pulse_index_scan_count{table, index} — gauge reporting idx_scan for each monitored index.

A Prometheus alert (UnusedIndexesDetected) fires when soroban_pulse_unused_indexes_total > 0 for more than 24 hours. Unused indexes waste write throughput and storage; the alert prompts operators to review and drop obsolete indexes.

Note: idx_scan resets when pg_stat_reset() is called or the PostgreSQL instance is restarted. A newly created index will show idx_scan = 0 until it is first used; allow one full monitoring cycle before treating it as unused.

Migration Consolidation History

The following indexes were dropped as part of issue #804 (migration 20260728000001_index_consolidation.sql):

Dropped index Reason Surviving replacement
idx_events_contract_ledger Superseded by idx_events_contract_type_ledger which covers the same (contract_id, ledger DESC) pattern and additionally supports event_type filters idx_events_contract_type_ledger
idx_events_event_data_topic_gin Covered by idx_events_event_data_gin (full-document jsonb_path_ops) and the per-position idx_events_topic_1/2/3_gin indexes idx_events_event_data_gin, idx_events_topic_1_gin, idx_events_topic_2_gin, idx_events_topic_3_gin

All surviving indexes now carry a COMMENT describing their purpose (see the migration file for the full text).

A full audit of all 60+ migrations is documented in docs/schema-audit.md.

Partition Maintenance

The events table is partitioned by timestamp (monthly ranges) as of migration 20260727000002_partition_events_by_month.sql.

Pre-creating future partitions

The create_future_partitions(N) SQL function (defined in scripts/manage_partitions.sql) creates N months of partitions starting from the current month:

-- Create partitions for the next 3 months (run as needed or via cron)
SELECT create_future_partitions(3);

The schema health check (src/index_monitor.rs::run_schema_health_check) emits a WARN log and increments soroban_pulse_schema_missing_future_partitions if fewer than 2 future months are pre-created. Alert on this gauge being > 0 for more than 48 hours.

Verifying partition pruning

Partition pruning is enabled by default in PostgreSQL 12+. Confirm:

SHOW enable_partition_pruning;  -- should be 'on'

-- A query with a timestamp predicate should show only the relevant partition:
EXPLAIN SELECT id FROM events
WHERE timestamp >= '2026-07-01' AND timestamp < '2026-08-01'
ORDER BY ledger DESC LIMIT 20;
-- Expected: only events_2026_07 appears in the plan

Queries filtered only by contract_id (no timestamp) will scan all partitions but use the per-partition idx_events_contract_type_ledger index to limit rows.

Dropping old partitions

Use drop_old_partitions(N) to remove partitions older than N months:

-- Drop partitions older than 12 months (data retention policy)
SELECT drop_old_partitions(12);

Always take a backup before dropping partitions in production. Refer to docs/data-retention.md for the full retention policy.

events_legacy table

events_legacy is the original non-partitioned events table preserved during the partitioning migration. It is not referenced by any application code (grep of src/**/*.rs returns zero matches). After confirming that all historical data has been migrated to the partitioned events table, it can be dropped:

-- Verify data completeness first:
SELECT COUNT(*) FROM events_legacy;
SELECT COUNT(*) FROM events;
-- Then drop when counts match and data is confirmed migrated:
DROP TABLE events_legacy;