Differences

This shows you the differences between two versions of the page.

--- projects:robin_net [2026/03/11 13:55] – freedomotter
+++ projects:robin_net [2026/03/11 15:34] (current) – removed freedomotter
@@ Line 1: / Line 1: @@
-====== Robin Net ======
-===== SQLite Schema =====
-<code>
-PRAGMA foreign_keys = ON;
--- =========================================================
--- node_info
--- One row per RobinNet node instance
--- =========================================================
-CREATE TABLE IF NOT EXISTS node_info (
-    id INTEGER PRIMARY KEY CHECK (id = 1),
-    node_uuid TEXT NOT NULL UNIQUE,
-    node_name TEXT NOT NULL UNIQUE,
-    operator_name TEXT NOT NULL DEFAULT '',
-    operator_callsign TEXT NOT NULL DEFAULT '',
-    location_text TEXT NOT NULL DEFAULT '',
-    transport_profile TEXT NOT NULL DEFAULT 'lan',
-    created_at TEXT NOT NULL,
-    updated_at TEXT NOT NULL
-);
--- =========================================================
--- peers
--- Known neighboring RobinNet nodes
--- =========================================================
-CREATE TABLE IF NOT EXISTS peers (
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    peer_uuid TEXT,
-    peer_name TEXT NOT NULL,
-    base_url TEXT NOT NULL,
-    transport TEXT NOT NULL DEFAULT 'lan',
-    enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0,1)),
-    last_seen_at TEXT,
-    last_sync_at TEXT,
-    notes TEXT NOT NULL DEFAULT '',
-    created_at TEXT NOT NULL,
-    updated_at TEXT NOT NULL,
-    UNIQUE(base_url)
-);
-CREATE INDEX IF NOT EXISTS idx_peers_enabled
-    ON peers(enabled);
-CREATE INDEX IF NOT EXISTS idx_peers_name
-    ON peers(peer_name);
--- =========================================================
--- messages
--- Core store-and-forward message table
--- =========================================================
-CREATE TABLE IF NOT EXISTS messages (
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    msg_uuid TEXT NOT NULL UNIQUE,
-    msg_hash TEXT NOT NULL,
-    msg_type TEXT NOT NULL,         -- bulletin, direct, status
-    scope TEXT NOT NULL DEFAULT 'network', -- local, regional, network
-    origin_node_uuid TEXT NOT NULL,
-    origin_node_name TEXT NOT NULL,
-    origin_time TEXT NOT NULL,
-    author TEXT NOT NULL DEFAULT '',
-    author_callsign TEXT NOT NULL DEFAULT '',
-    destination TEXT NOT NULL DEFAULT '',   -- freeform for v0.1
-    title TEXT NOT NULL,
-    body TEXT NOT NULL,
-    created_at TEXT NOT NULL,
-    expires_at TEXT,
-    priority INTEGER NOT NULL DEFAULT 2 CHECK (priority BETWEEN 0 AND 9),
-    status TEXT NOT NULL DEFAULT 'active',  -- active, expired, cancelled
-    is_local_only INTEGER NOT NULL DEFAULT 0 CHECK (is_local_only IN (0,1)),
-    is_rf_eligible INTEGER NOT NULL DEFAULT 0 CHECK (is_rf_eligible IN (0,1)),
-    imported_from_peer_uuid TEXT,
-    imported_from_peer_name TEXT,
-    first_seen_at TEXT NOT NULL,
-    last_seen_at TEXT NOT NULL,
-    CHECK (length(trim(title)) > 0),
-    CHECK (length(msg_uuid) > 0),
-    CHECK (length(msg_hash) > 0)
-);
-CREATE INDEX IF NOT EXISTS idx_messages_type
-    ON messages(msg_type);
-CREATE INDEX IF NOT EXISTS idx_messages_scope
-    ON messages(scope);
-CREATE INDEX IF NOT EXISTS idx_messages_created_at
-    ON messages(created_at DESC);
-CREATE INDEX IF NOT EXISTS idx_messages_origin_node_uuid
-    ON messages(origin_node_uuid);
-CREATE INDEX IF NOT EXISTS idx_messages_status
-    ON messages(status);
-CREATE INDEX IF NOT EXISTS idx_messages_expires_at
-    ON messages(expires_at);
--- Helpful when checking integrity collisions or reimports
-CREATE INDEX IF NOT EXISTS idx_messages_hash
-    ON messages(msg_hash);
--- =========================================================
--- message_trace
--- Per-message trail of where a message was seen/imported
--- Useful for troubleshooting and future RF accountability
--- =========================================================
-CREATE TABLE IF NOT EXISTS message_trace (
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    msg_uuid TEXT NOT NULL,
-    event_type TEXT NOT NULL,   -- created, imported, exported, synced, rejected
-    event_time TEXT NOT NULL,
-    local_node_uuid TEXT NOT NULL,
-    local_node_name TEXT NOT NULL,
-    peer_uuid TEXT,
-    peer_name TEXT,
-    detail TEXT NOT NULL DEFAULT '',
-    FOREIGN KEY (msg_uuid) REFERENCES messages(msg_uuid) ON DELETE CASCADE
-);
-CREATE INDEX IF NOT EXISTS idx_message_trace_msg_uuid
-    ON message_trace(msg_uuid);
-CREATE INDEX IF NOT EXISTS idx_message_trace_event_time
-    ON message_trace(event_time DESC);
--- =========================================================
--- sync_state
--- Tracks per-peer sync knowledge so you can avoid repeating work
--- =========================================================
-CREATE TABLE IF NOT EXISTS sync_state (
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    peer_id INTEGER NOT NULL,
-    msg_uuid TEXT NOT NULL,
-    delivery_state TEXT NOT NULL DEFAULT 'pending', -- pending, sent, failed, skipped
-    attempt_count INTEGER NOT NULL DEFAULT 0,
-    first_attempt_at TEXT,
-    last_attempt_at TEXT,
-    delivered_at TEXT,
-    last_error TEXT NOT NULL DEFAULT '',
-    created_at TEXT NOT NULL,
-    updated_at TEXT NOT NULL,
-    FOREIGN KEY (peer_id) REFERENCES peers(id) ON DELETE CASCADE,
-    FOREIGN KEY (msg_uuid) REFERENCES messages(msg_uuid) ON DELETE CASCADE,
-    UNIQUE(peer_id, msg_uuid)
-);
-CREATE INDEX IF NOT EXISTS idx_sync_state_peer_id
-    ON sync_state(peer_id);
-CREATE INDEX IF NOT EXISTS idx_sync_state_msg_uuid
-    ON sync_state(msg_uuid);
-CREATE INDEX IF NOT EXISTS idx_sync_state_delivery_state
-    ON sync_state(delivery_state);
--- =========================================================
--- local_settings
--- Small key/value table for future expansion
--- =========================================================
-CREATE TABLE IF NOT EXISTS local_settings (
-    setting_key TEXT PRIMARY KEY,
-    setting_value TEXT NOT NULL,
-    updated_at TEXT NOT NULL
-);
-</code>