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
);

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:

msg_uuid: abc123
event_type: created
local_node_name: alpha
detail: Local bulletin created by operator

msg_uuid: abc123
event_type: imported
local_node_name: bravo
peer_name: alpha
detail: Imported during sync

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

import hashlib
import json


def canonical_message_payload(message: dict) -> str:
    payload = {
        "msg_type": message["msg_type"],
        "scope": message["scope"],
        "origin_node_uuid": message["origin_node_uuid"],
        "origin_node_name": message["origin_node_name"],
        "origin_time": message["origin_time"],
        "author": message.get("author", ""),
        "author_callsign": message.get("author_callsign", ""),
        "destination": message.get("destination", ""),
        "title": message["title"],
        "body": message["body"],
        "expires_at": message.get("expires_at"),
        "priority": message.get("priority", 2),
        "is_local_only": int(bool(message.get("is_local_only", False))),
        "is_rf_eligible": int(bool(message.get("is_rf_eligible", False))),
    }
    return json.dumps(payload, sort_keys=True, separators=(",", ":"), ensure_ascii=False)


def compute_message_hash(message: dict) -> str:
    canonical = canonical_message_payload(message)
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()