Database Schemas

Requake stores scan outputs in a SQLite database named requake.sqlite in the selected output directory.

The database currently contains seven domain tables:

catalog
event_keys
trace_keys
event_pairs
trace_metadata
families
template_detections

Schema Version

The SQLite PRAGMA user_version field is used to track schema version. The current version is 1.

Connection and Concurrency Settings

At connection startup, Requake applies SQLite pragmas to improve integrity and concurrency:

PRAGMA foreign_keys = ON
PRAGMA busy_timeout = 30000
PRAGMA journal_mode = WAL

Write operations also use a bounded retry policy for transient lock errors (SQLITE_BUSY and SQLITE_LOCKED style conditions), with exponential backoff and jitter.

Catalog Table

The catalog table stores events read by requake read_catalog.

CREATE TABLE catalog (
  evid            TEXT PRIMARY KEY,
  orig_time       TEXT NOT NULL,
  lat             REAL,
  lon             REAL,
  depth_km        REAL,
  mag_type        TEXT,
  mag             REAL,
  mag_author      TEXT,
  author          TEXT,
  catalog         TEXT,
  contributor     TEXT,
  contributor_id  TEXT,
  location_name   TEXT,
  trace_id        TEXT
)

Event Keys Table

The event_keys table maps event IDs to integer keys.

CREATE TABLE event_keys (
  event_id        INTEGER PRIMARY KEY AUTOINCREMENT,
  evid            TEXT NOT NULL UNIQUE
)

Trace Keys Table

The trace_keys table maps trace IDs to integer keys.

CREATE TABLE trace_keys (
  trace_key_id    INTEGER PRIMARY KEY AUTOINCREMENT,
  trace_id        TEXT NOT NULL UNIQUE
)

Event Pairs Table

The event_pairs table stores cross-correlation results from requake scan_catalog and is optimized for storage efficiency:

repeated text identifiers are replaced by integer lookup keys;
cross-correlation is encoded as cc_x100;
lag is stored as lag_samples.

Pairs that cannot be analyzed (for example due to missing waveforms) are still stored for resume logic, with lag_samples and cc_x100 set to NULL.

Lag in seconds is reconstructed at read time from lag_samples and trace_metadata.sampling_rate_hz.

CREATE TABLE event_pairs (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  event1_id       INTEGER NOT NULL,
  event2_id       INTEGER NOT NULL,
  trace_key_id    INTEGER NOT NULL,
  lag_samples     INTEGER,
  cc_x100         INTEGER,
  FOREIGN KEY (event1_id)
    REFERENCES event_keys(event_id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (event2_id)
    REFERENCES event_keys(event_id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (trace_key_id)
    REFERENCES trace_keys(trace_key_id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  UNIQUE (event1_id, event2_id, trace_key_id)
)

cc_x100 stores cc_max with 0.01 precision using round(cc_max * 100).

Trace Metadata Table

The trace_metadata table stores sampling rate, coordinates, and elevation/depth using time-valid intervals per trace_id.

CREATE TABLE trace_metadata (
  trace_id          TEXT NOT NULL,
  valid_from_utc    TEXT NOT NULL,
  valid_to_utc      TEXT,
  sampling_rate_hz  REAL NOT NULL,
  trace_lon         REAL,
  trace_lat         REAL,
  elevation         REAL,
  local_depth       REAL,
  updated_at        TEXT,
  PRIMARY KEY (trace_id, valid_from_utc)
)

Index:

idx_trace_metadata_lookup on trace_metadata(trace_id, valid_from_utc, valid_to_utc)

Families Table

The families table stores event-family assignments produced by requake build_families.

CREATE TABLE families (
  evid            TEXT NOT NULL,
  trace_id        TEXT NOT NULL,
  orig_time       TEXT NOT NULL,
  lon             REAL,
  lat             REAL,
  depth_km        REAL,
  mag_type        TEXT,
  mag             REAL,
  family_number   INTEGER NOT NULL,
  valid           INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY (evid)
    REFERENCES catalog(evid)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  PRIMARY KEY (evid, trace_id, family_number)
)

Index:

idx_families_number on families(family_number)

Template Detections Table

The template_detections table stores detections produced by requake scan_templates.

CREATE TABLE template_detections (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  family_number   INTEGER NOT NULL,
  trace_id        TEXT NOT NULL,
  evid            TEXT NOT NULL,
  orig_time       TEXT NOT NULL,
  lon             REAL,
  lat             REAL,
  depth_km        REAL,
  cc_max          REAL,
  UNIQUE (family_number, trace_id, evid)
)

Indexes:

idx_template_detections_family on template_detections(family_number)
idx_template_detections_trace on template_detections(trace_id)