spb/earth-now Public License

1-- earth-now.co2-- Author:  Simon-Pierre Boucher3-- Contact: contact@spboucher.ai4-- File:    infra/migrations/001_initial.sql5-- Purpose: Initial TimescaleDB schema — observations (hypertable), counter_models, registry sync, share tokens, ingest audit67CREATE EXTENSION IF NOT EXISTS timescaledb;89-- ---------------------------------------------------------------------------10-- observations — APPEND-ONLY.11-- Normalized ingested data points. Rows are NEVER updated or deleted: a12-- correction from a source is a new row (later ingested_at wins at read time).13-- This is what makes every counter value traceable and re-fittable forever.14-- ---------------------------------------------------------------------------15CREATE TABLE observations (16    id            bigserial,17    metric_id     text        NOT NULL,18    source_id     text        NOT NULL,19    observed_time timestamptz NOT NULL,20    value         double precision NOT NULL,21    ingested_at   timestamptz NOT NULL DEFAULT now(),22    raw_ref       text,23    -- Hypertables require the partitioning column in every unique constraint.24    PRIMARY KEY (id, observed_time)25);2627SELECT create_hypertable('observations', 'observed_time');2829CREATE INDEX observations_metric_time_idx30    ON observations (metric_id, observed_time DESC);31CREATE INDEX observations_source_time_idx32    ON observations (source_id, observed_time DESC);3334-- ---------------------------------------------------------------------------35-- counter_models — versioned, NEVER UPDATEd.36-- Every re-fit inserts a NEW row; `deployed` marks the row currently served.37-- Deploying a model = insert new row with deployed = true (the previous38-- deployed row is superseded by fitted_at ordering, not mutated).39-- ---------------------------------------------------------------------------40CREATE TABLE counter_models (41    id            bigserial PRIMARY KEY,42    metric_id     text        NOT NULL,43    model_version text        NOT NULL,44    model         jsonb       NOT NULL,45    fitted_at     timestamptz NOT NULL DEFAULT now(),46    deployed      boolean     NOT NULL DEFAULT false47);4849CREATE INDEX counter_models_metric_fitted_idx50    ON counter_models (metric_id, fitted_at DESC);51CREATE INDEX counter_models_deployed_idx52    ON counter_models (metric_id) WHERE deployed;5354-- ---------------------------------------------------------------------------55-- metrics_registry_sync — last synced state of packages/registry per metric.56-- ---------------------------------------------------------------------------57CREATE TABLE metrics_registry_sync (58    metric_id text        PRIMARY KEY,59    yaml_hash text        NOT NULL,60    synced_at timestamptz NOT NULL DEFAULT now()61);6263-- ---------------------------------------------------------------------------64-- share_tokens — revocable tokens for /m/:token pages (per-metric visibility).65-- Revocation = set revoked_at (kept for audit, never hard-deleted).66-- ---------------------------------------------------------------------------67CREATE TABLE share_tokens (68    token      text        PRIMARY KEY,69    metric_ids text[]      NOT NULL,70    revoked_at timestamptz,71    created_at timestamptz NOT NULL DEFAULT now()72);7374-- ---------------------------------------------------------------------------75-- ingest_runs — full auditability of every ingestion run (success or failure).76-- ---------------------------------------------------------------------------77CREATE TABLE ingest_runs (78    id          bigserial   PRIMARY KEY,79    source_id   text        NOT NULL,80    started_at  timestamptz NOT NULL DEFAULT now(),81    finished_at timestamptz,82    status      text        NOT NULL DEFAULT 'running',83    error       text,84    raw_ref     text,85    checksum    text86);8788CREATE INDEX ingest_runs_source_started_idx89    ON ingest_runs (source_id, started_at DESC);90