stargazer/db/
migrations.rs

1//! Embedded SQL schema migrations.
2//!
3//! All tables use `IF NOT EXISTS` for idempotent re-execution. The schema is
4//! applied as a single transaction via [`run`] on every startup, so adding new
5//! tables or indices is a no-op when they already exist.
6//!
7//! # Tables
8//!
9//! | Table | Purpose |
10//! |---|---|
11//! | `reflectors` | Registry of discovered D-STAR reflectors (Tier 1). |
12//! | `activity_log` | Timestamped observations of callsign activity (Tier 2). |
13//! | `connected_nodes` | Snapshot of nodes currently linked to a reflector. |
14//! | `streams` | Captured voice transmissions with audio and metadata (Tier 3). |
15//!
16//! # Relationships
17//!
18//! `activity_log.reflector` and `connected_nodes.reflector` both reference
19//! `reflectors.callsign` via foreign key. `streams.reflector` is intentionally
20//! **not** foreign-keyed so that streams captured from transiently-seen
21//! reflectors survive registry cleanup.
22
23use sqlx::PgPool;
24
25/// SQL statements executed in order to bring the schema up to date.
26///
27/// Each statement is idempotent (`IF NOT EXISTS`), so running this on an
28/// already-migrated database is safe and fast.
29const SCHEMA_SQL: &str = r"
30-- Reflector registry populated by Tier 1 discovery sweeps.
31-- Primary key is the reflector callsign (e.g. 'REF001', 'XLX001', 'DCS001').
32CREATE TABLE IF NOT EXISTS reflectors (
33    callsign        TEXT PRIMARY KEY,
34    protocol        TEXT NOT NULL,
35    ip_address      INET,
36    dashboard_url   TEXT,
37    country         TEXT,
38    last_seen       TIMESTAMPTZ,
39    tier2_available BOOLEAN DEFAULT FALSE,
40    created_at      TIMESTAMPTZ DEFAULT now()
41);
42
43-- Timestamped callsign observations from Tier 2 XLX monitors or Tier 1
44-- scrapes. Each row records one callsign heard on a reflector module at a
45-- specific instant, with 'source' indicating the origin (e.g. 'xlx_monitor',
46-- 'ircddb', 'pistar').
47CREATE TABLE IF NOT EXISTS activity_log (
48    id          BIGSERIAL PRIMARY KEY,
49    reflector   TEXT NOT NULL REFERENCES reflectors(callsign),
50    module      CHAR(1),
51    callsign    TEXT NOT NULL,
52    source      TEXT NOT NULL,
53    observed_at TIMESTAMPTZ NOT NULL
54);
55-- Composite index for the common query pattern: activity on a specific
56-- reflector/module within a time window.
57CREATE INDEX IF NOT EXISTS idx_activity_log_lookup
58    ON activity_log (reflector, module, observed_at);
59
60-- Snapshot of nodes currently linked to each reflector module. Updated by
61-- Tier 2 monitors; stale entries are evicted when a monitor reconnects.
62-- The composite primary key (reflector + node_callsign) means each node can
63-- only appear once per reflector.
64CREATE TABLE IF NOT EXISTS connected_nodes (
65    reflector       TEXT NOT NULL REFERENCES reflectors(callsign),
66    node_callsign   TEXT NOT NULL,
67    module          CHAR(1),
68    protocol        TEXT,
69    connected_since TIMESTAMPTZ,
70    last_heard      TIMESTAMPTZ,
71    PRIMARY KEY (reflector, node_callsign)
72);
73
74-- Voice transmissions captured by Tier 3 deep connections. Each row is one
75-- D-STAR voice stream (header + N voice frames + optional EOT). The audio_mp3
76-- column holds the decoded PCM->MP3 blob once encoding completes.
77-- upload_status tracks the Rdio upload lifecycle: pending -> uploaded | failed.
78CREATE TABLE IF NOT EXISTS streams (
79    id              BIGSERIAL PRIMARY KEY,
80    reflector       TEXT NOT NULL,
81    module          CHAR(1) NOT NULL,
82    protocol        TEXT NOT NULL,
83    stream_id       INTEGER NOT NULL,
84    callsign        TEXT NOT NULL,
85    suffix          TEXT,
86    ur_call         TEXT,
87    dstar_text      TEXT,
88    dprs_lat        DOUBLE PRECISION,
89    dprs_lon        DOUBLE PRECISION,
90    started_at      TIMESTAMPTZ NOT NULL,
91    ended_at        TIMESTAMPTZ,
92    frame_count     INTEGER DEFAULT 0,
93    audio_mp3       BYTEA,
94    upload_status   TEXT DEFAULT 'pending',
95    upload_attempts INTEGER DEFAULT 0,
96    last_upload_error TEXT,
97    uploaded_at     TIMESTAMPTZ,
98    created_at      TIMESTAMPTZ DEFAULT now()
99);
100-- Index for the upload processor to find pending streams efficiently.
101CREATE INDEX IF NOT EXISTS idx_streams_upload
102    ON streams (upload_status);
103-- Composite index for querying streams by reflector, module, and time.
104CREATE INDEX IF NOT EXISTS idx_streams_lookup
105    ON streams (reflector, module, started_at);
106";
107
108/// Executes the embedded schema DDL against the database.
109///
110/// All statements use `IF NOT EXISTS`, so this is safe to call on every
111/// application startup. Runs as a single implicit transaction via
112/// `sqlx::raw_sql`.
113///
114/// # Errors
115///
116/// Returns `sqlx::Error` if any DDL statement fails (e.g., permission denied,
117/// type mismatch with an existing column).
118pub(crate) async fn run(pool: &PgPool) -> Result<(), sqlx::Error> {
119    let _result = sqlx::raw_sql(SCHEMA_SQL).execute(pool).await?;
120    tracing::info!("schema migrations applied successfully");
121    Ok(())
122}
stargazer/db/migrations.rs

stargazer/db/
migrations.rs
