Skip to content

ADR-0002: Use Single Events Table with JSONB Payload over Typed Per-Event Tables

Status: accepted Deciders: DeAcero Platform Team Date: 2026-03-17 Parent: ADR-0001

Context and Problem Statement

The observability service must store 6 distinct event types (project.generated, skill.invoked, ci.run, tool.executed, knowledge.created, knowledge.used). Each event type has a different payload structure. How should the PostgreSQL schema be designed?

Considered Options

  1. Single events table with JSONB payload — one table, polymorphic payload column
  2. Six typed tables — one table per event type, all columns strongly typed
  3. Single events table with a normalized payload — common columns + a details JSONB for overflow

Decision Outcome

Chosen option: Option 1 — Single events table with JSONB payload.

Rationale: The 6 event types share a common envelope (event_type, project_slug, github_username, timestamp, schema_version) but have completely different payload structures. Six typed tables (Option 2) would require complex JOINs for cross-event queries and make adding new event types a migration per type. A single table with JSONB (Option 1) allows all cross-event queries to be simple SELECT ... WHERE event_type = '...', while JSONB operators (->, ->>, @>) provide efficient payload filtering. PostgreSQL's GIN index on the JSONB column can accelerate payload lookups when needed.

Schema

CREATE TABLE events (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type      TEXT NOT NULL,
    project_slug    TEXT NOT NULL,
    github_username TEXT NOT NULL DEFAULT '',
    schema_version  TEXT NOT NULL DEFAULT '1.0',
    received_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_ts        TIMESTAMPTZ NOT NULL,
    payload         JSONB NOT NULL
);

CREATE INDEX idx_events_event_type   ON events (event_type);
CREATE INDEX idx_events_project_slug ON events (project_slug);
CREATE INDEX idx_events_received_at  ON events (received_at DESC);
CREATE INDEX idx_events_type_project ON events (event_type, project_slug);

-- Materialized view for dashboard (refreshed on write)
CREATE MATERIALIZED VIEW mv_summary AS
SELECT
    project_slug,
    event_type,
    date_trunc('day', event_ts) AS day,
    count(*) AS event_count,
    sum((payload->>'estimated_cost_usd')::float)
        FILTER (WHERE event_type = 'skill.invoked') AS daily_cost_usd
FROM events
GROUP BY project_slug, event_type, day;

CREATE UNIQUE INDEX ON mv_summary (project_slug, event_type, day);

Positive Consequences

  • All 6 event types in one table — simple cross-event queries
  • Adding a new event type requires no schema migration
  • schema_version field future-proofs payload evolution
  • Materialized view provides cheap dashboard aggregations

Negative Consequences

  • No database-enforced constraints on payload structure (mitigated by Pydantic validation at ingest)
  • JSONB column may be slower than typed columns for high-cardinality payload field lookups (mitigated by targeted GIN index if needed)
  • Parent: ADR-0001
  • Alembic migration: services/observability/alembic/versions/0001_initial.py