Differences

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

--- projects:robin_net [2026/03/11 13:57] – 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>
-This gives you the basics without boxing you in later.
-node_info
-Stores the identity of the current RobinNet node.
-You only ever want one row here, so I used:
-id INTEGER PRIMARY KEY CHECK (id = 1)
-That makes it easy to enforce “single node identity per DB.”
-peers
-Stores neighboring nodes.
-Each peer has:
-friendly name
-base URL
-transport type
-enabled flag
-last seen / last sync timestamps
-That’s enough for LAN testing and later expansion.
-messages
-This is the heart of RobinNet.
-Important fields:
-msg_uuid → global tracking ID
-msg_hash → SHA256 integrity hash
-origin_node_uuid / origin_node_name → where it started
-origin_time → when it started
-destination → freeform for now
-imported_from_peer_* → last known import source
-first_seen_at / last_seen_at → local traceability
-That gives you solid “where did this come from?” tracking.
-message_trace
-This is the piece you were asking for when you mentioned tracking messages.
-Instead of only storing one hash and calling it done, this gives you a real event trail like:
-created locally
-imported from bravo
-exported to charlie
-rejected because duplicate
-later maybe approved for RF
-Example rows might look like:
-<code>
-msg_uuid: abc123
-event_type: created
-local_node_name: alpha
-detail: Local bulletin created by operator
-</code>
-<code>
-msg_uuid: abc123
-event_type: imported
-local_node_name: bravo
-peer_name: alpha
-detail: Imported during sync
-</code>
-That is going to be much more useful than only an MD5.
-sync_state
-Tracks sync per peer per message.
-That lets you later say:
-message pending to peer X
-already sent to peer Y
-failed to send to peer Z
-Without having to guess from logs.
-This is very useful once you move beyond a toy prototype.
-local_settings
-Small utility table for:
-schema version
-sync cursor
-admin flags
-defaults
-You’ll be glad it’s there later.
-Suggested enum values
-These are not SQLite enums, just recommended values you should keep consistent in code.
-msg_type
-bulletin
-direct
-status
-scope
-local
-regional
-network
-status
-active
-expired
-cancelled
-event_type in message_trace
-created
-imported
-exported
-synced
-rejected
-expired
-delivery_state in sync_state
-pending
-sent
-failed
-skipped
-Recommended canonical hash input
-For the msg_hash, hash only the canonical message content, not local metadata.
-I’d recommend this exact field order:
-msg_type
-scope
-origin_node_uuid
-origin_node_name
-origin_time
-author
-author_callsign
-destination
-title
-body
-expires_at
-priority
-is_local_only
-is_rf_eligible
-That way:
-the same content always hashes the same
-local import/export events do not alter the hash
-tampering is detectable