Advisory SQL Schema

Design Principles

• Full fidelity — The complete event JSON blob is stored in a data column. Indexed columns are projections of frequently-queried fields, not the source of truth.
• Normalization — Relations, metrics, and media are normalized into their own tables for efficient JOIN queries.
• Dual temporal storage — Temporal values are stored as both their original calendar-native string (for faithful display) and as a resolved Unix timestamp (for range queries and sorting).
• Tombstone retention — Tombstone records MUST be retained. Filter them with WHERE type != 'tombstone'. Never hard-delete them.

Core Tables

packages

One row per .chronpkg bundle manifest. Events reference packages via package_id.

-- Packages (from .chronpkg manifest)
CREATE TABLE packages (
  id              TEXT PRIMARY KEY,   -- package_id from manifest
  title           TEXT NOT NULL,
  schema_version  TEXT NOT NULL,
  meta            JSON,
  created_at      TIMESTAMP,
  updated_at      TIMESTAMP
);

events

One row per .chron file. The data column holds the complete original JSON for full fidelity. All other columns are indexed projections.

-- Events (one row per .chron file)
CREATE TABLE events (
  id                    TEXT PRIMARY KEY,   -- UUID-v4
  package_id            TEXT REFERENCES packages(id),
  type                  TEXT NOT NULL
                          CHECK (type IN ('event','era','marker','tombstone','deprecated')),
  original_type         TEXT
                          CHECK (original_type IN ('event','era','marker',NULL)),
  title                 TEXT,
  universe              TEXT,
  calendar              TEXT,
  start_value           TEXT,
  end_value             TEXT,
  start_unix            REAL,              -- Resolved SI seconds
  end_unix              REAL,
  precision             TEXT,
  significance_value    INTEGER,
  temporal_scope        TEXT,
  geographic_scope      TEXT,
  longitude             REAL,              -- GeoJSON order
  latitude              REAL,
  altitude              REAL,
  canonical_url         TEXT,
  is_recurring          BOOLEAN DEFAULT FALSE,
  data                  JSON NOT NULL,
  created_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_events_package      ON events(package_id);
CREATE INDEX idx_events_type         ON events(type);
CREATE INDEX idx_events_start_unix   ON events(start_unix);
CREATE INDEX idx_events_temporal_scope ON events(temporal_scope);
CREATE INDEX idx_events_geo_scope    ON events(geographic_scope);
CREATE INDEX idx_events_sig_value    ON events(significance_value);

event_relations

Normalized from the event's relations array. target_event_id may be NULL when only target_url is known (unresolved cross-file relation).

CREATE TABLE event_relations (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  source_event_id TEXT NOT NULL REFERENCES events(id),
  target_event_id TEXT,
  target_url      TEXT,
  relation_type   TEXT NOT NULL,
  canon_scope     TEXT,
  canon_status    TEXT,
  confidence      REAL,
  bidirectional   BOOLEAN DEFAULT FALSE,
  description     TEXT
);

event_metrics

Normalized from significance.metrics[]. Enables efficient per-domain significance queries.

CREATE TABLE event_metrics (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id     TEXT NOT NULL REFERENCES events(id),
  metric_type  TEXT NOT NULL,
  metric_value INTEGER NOT NULL,
  description  TEXT
);

event_media

Normalized from the event's media[] array.

CREATE TABLE event_media (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id     TEXT NOT NULL REFERENCES events(id),
  media_type   TEXT NOT NULL,
  url          TEXT NOT NULL,
  alt_text     TEXT,
  sync_enabled BOOLEAN DEFAULT FALSE
);

calendars

Resolved calendar definition cache. Populated on first fetch; updated when canonical_url returns a newer version.

CREATE TABLE calendars (
  id              TEXT PRIMARY KEY,
  canonical_url   TEXT,
  display_name    TEXT,
  calendar_type   TEXT NOT NULL CHECK (calendar_type IN ('builtin','custom')),
  structure_type  TEXT CHECK (structure_type IN ('linear','cyclic',NULL)),
  definition      JSON,
  cached_at       TIMESTAMP
);

universes

Resolved universe definition cache. The canon_scopes column holds the JSON array of valid scope objects for this universe.

CREATE TABLE universes (
  id               TEXT PRIMARY KEY,
  canonical_url    TEXT,
  display_name     TEXT,
  universe_type    TEXT NOT NULL CHECK (universe_type IN ('builtin','absolute','relative')),
  default_calendar TEXT,
  canon_scopes     JSON,
  definition       JSON,
  cached_at        TIMESTAMP
);

Example Queries

Historical/global events by significance

-- Events visible at historical/global zoom, ordered by significance
SELECT id, title, start_value, significance_value
FROM events
WHERE temporal_scope IN ('epochal','millennial','centennial','generational')
  AND geographic_scope IN ('global','continental')
  AND type != 'tombstone'
ORDER BY significance_value DESC;

Events in a geographic bounding box

SELECT id, title, longitude, latitude, significance_value
FROM events
WHERE longitude BETWEEN -74.01 AND -73.97
  AND latitude  BETWEEN  40.70 AND  40.75
  AND type != 'tombstone'
  AND significance_value >= 300;

All events causally connected to a given event

SELECT e.id, e.title, r.relation_type
FROM event_relations r
JOIN events e ON e.id = r.target_event_id
WHERE r.source_event_id = 'TARGET-UUID-HERE'
  AND r.relation_type IN ('causes','caused_by','influences','influenced_by');

Tombstone-safe upsert respecting tombstone priority

When ingesting events from a feed or merge operation, tombstone records must always win regardless of insert order.

INSERT INTO events (id, type, data) VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
  type = CASE
    WHEN excluded.type = 'tombstone' THEN 'tombstone'
    WHEN events.type   = 'tombstone' THEN 'tombstone'
    ELSE excluded.type END,
  data = CASE
    WHEN excluded.type = 'tombstone' THEN excluded.data
    WHEN events.type   = 'tombstone' THEN events.data
    ELSE excluded.data END;