Database Schema

The "depends" project persists all data in a single SQLite database file (data/depends.db by default). The schema enables multi-tenant isolation, dependency graphs, state tracking, auditing, and notifications. SQLite operates in WAL (Write-Ahead Logging) mode for better concurrency, with a 5-second busy timeout and foreign keys enabled for referential integrity.

The database supports the core-model.md: users own namespaces containing nodes (services/systems) with states (green, yellow, red), edges (dependencies), events (state changes), and notification rules. Effective states aggregate the worst state across a node and its transitive dependencies, computed on-the-fly rather than stored, as described in graph-computation.md.

Schema Initialization

The full schema appears in src/db.ts and initializes on database creation:

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS tokens (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  token_hash      TEXT NOT NULL UNIQUE,
  email           TEXT,
  legendum_token  TEXT,
  meta            TEXT DEFAULT '{}',
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Additional tables follow (namespaces, nodes, etc.)

The createDb function executes this schema idempotently. In-memory databases support testing via createTestDb.

Table Relationships

• Tokens (users/accounts) own multiple namespaces (projects).
• Namespaces scope nodes, edges, notification_rules, and events.
• Nodes link via edges (from_node depends on to_node).
• Foreign keys use ON DELETE CASCADE to prune child data when parents delete (e.g., deleting a namespace removes its nodes/edges).
• Primary keys are composite where scoped (e.g., (ns_id, id) for nodes).

This design ensures data isolation per user/namespace while allowing efficient queries for graphs and audits.

Tokens Table

Stores user authentication data. See authentication.md.

Namespaces Table

Projects/graphs belong to a token. IDs are lowercase alphanumeric/hyphens, max 64 chars.

CLI init derives namespace from directory name, sanitizing to valid ID.

Nodes Table

Core entities representing services/systems. States use CHECK constraint: 'green' (healthy), 'yellow' (warning), 'red' (broken). default_state sets initial state on auto-creation.

TTL parses strings like "10m" via parseTtl (e.g., 10m → 600s). Effective state considers TTL expiry and propagates "worst" (red > yellow > green) across dependencies.

Edges Table

Directed dependencies: from_node → to_node (A depends on B).

Prevents cycles on insert (project-invariants.md). Indexes: idx_edges_to_node on (ns_id, to_node) for reverse lookups.

Notification Rules Table

Triggers on state changes. See notifications.md.

Events Table

Immutable audit log of state changes. Supports events-auditing.md and CLI events.

Indexes:

• idx_events_ns: (ns_id, created_at) for namespace timelines.
• idx_events_node: (ns_id, node_id, created_at) for per-node history.
• idx_events_node_id: (ns_id, node_id, id) for efficient limits.

API/CLI query with limit, since, order=desc. Retained 30 days; data-purging.md auto-deletes older events hourly in server mode.

Key Behaviors and Invariants

• State Writes: Log to events, update last_state_write/state_changed_at, trigger notifications.md.
• Graph Uploads: Upsert nodes/edges; auto-create missing nodes at default_state or 'yellow'.
• TTL Expiry: Only affects green → yellow; computed in graph-computation.md.
• Cascading Deletes: Namespace deletion prunes all child data (self-hosted-mode.md, hosted-mode.md).
• Rate Limiting: Per-IP in server (rate-limiting.md).
• Query Efficiency: Indexes support cli-commands.md like status, graph, events.

This schema balances simplicity (single file) with scalability for thousands of nodes/events per namespace.