Database schema

Shumoku Server uses SQLite (via bun:sqlite) for persistent storage. The database file is stored at {dataDir}/shumoku.db.

Overview

┌─────────────────┐     ┌─────────────────────────┐     ┌──────────────────────┐
│  data_sources   │     │  topology_data_sources  │     │      topologies      │
│─────────────────│     │─────────────────────────│     │──────────────────────│
│ id (PK)         │◄────│ data_source_id (FK)     │     │ id (PK)              │
│ name            │     │ topology_id (FK)        │────►│ name                 │
│ type            │     │ purpose (topology|      │     │ share_token          │
│ config_json     │     │          metrics)       │     │ composition_revision │
│ status …        │     │ sync_mode, priority …   │     │ created_at, updated… │
└─────────────────┘     └─────────────────────────┘     └──────────────────────┘
   (incl. type='manual': an ordinary, explicitly-added hand-drawn source)

  ┌──────────────────────────────────────────────┐  ┌──────────────────────────┐
  │  contribution_* (DB-NATIVE — the canonical    │  │  topology_resolved_graph │
  │  composition store; resolve() reads THIS)     │  │──────────────────────────│
  │──────────────────────────────────────────────│  │ topology_id (PK, FK)     │
  │ contribution_source (topology_id, source_id)  │  │ graph_json               │
  │   attachment_id SET  = a source contribution  │  │ layout_json              │
  │   attachment_id NULL = the PROJECT OVERLAY    │  │ icon_dimensions_json     │
  │   (operator curation; source_id='intrinsic')  │  │ built_revision           │
  │ contribution_element / _identity / _link /    │  │ resolver_version         │
  │   _link_via / _attachment  (decomposed graph) │  │ computed_at              │
  └──────────────────────────────────────────────┘  └──────────────────────────┘
                                                       derived artifact, 1/topology
  ┌──────────────────────────┐
  │  topology_observations   │  append-only AUDIT/HISTORY log only.
  │──────────────────────────│  resolve() does NOT read this — it reads
  │ id, topology_id,         │  contribution_*. record() materializes each
  │ source_id, captured_at,  │  snapshot into a contribution in one txn.
  │ status, graph_json …     │
  └──────────────────────────┘

┌─────────────┐  ┌────────────┐  ┌──────────────────────────────┐
│ dashboards  │  │  settings  │  │ migrations (id, name, applied)│
└─────────────┘  └────────────┘  └──────────────────────────────┘
(+ auth / share-token / grafana-alert tables — see migrations 005-007)

Composition model (since the DB-native persistence refactor): a topology is a shell; its sources live in topology_data_sources (m2m, by purpose). Every contribution is stored DB-native in the contribution_* store, keyed by (topology_id, source_id), in two kinds distinguished by attachment_id:

• source contributions (attachment_id SET) — every attached source’s latest graph, INCLUDING an explicitly-added hand-drawn type='manual' source. A Manual source is ordinary: its editor save records an observation that materializes here, the SAME path as any source (no manual-specific code).
• the project overlay (attachment_id NULL, sentinel source_id='intrinsic', one per topology via idx_contrib_one_intrinsic) — the operator’s curation: exclusions, field overrides, metrics bindings, display settings. Owned by the project, not a data source; writing it never creates/attaches anything.

resolve() reads the contribution_* rows (NOT topology_observations), folds the project overlay as the top-priority authored input plus every source contribution by priority, and materializes the result in topology_resolved_graph (invalidated by composition_revision). topology_observations is now just the append-only audit/history log. The metrics mapping is not a column — it is metrics-binding attachments folded onto the resolved graph. See docs/design/db-native-persistence.md (storage), topology-source-priority-merge.md (merge), and manual-source-unification.md (Manual = uniform source).

Tables

data_sources

External data source connections (Zabbix, NetBox, Prometheus, etc.)

config_json examples:

// Zabbix
{
  "url": "https://zabbix.example.com/api_jsonrpc.php",
  "token": "api_token_here",
  "pollInterval": 5000
}

// Prometheus
{
  "url": "http://prometheus:9090",
  "preset": "snmp",
  "hostLabel": "hostname",
  "jobFilter": "snmp"
}

// NetBox
{
  "url": "https://netbox.example.com",
  "token": "api_token_here",
  "groupBy": "site"
}

topologies

Topology shell only. Graph content lives in the contribution_* store (source contributions + the project overlay); the metrics mapping lives as metrics-binding attachments folded onto the resolved graph (no column). Legacy columns content_json (dropped m010), topology_source_id / metrics_source_id / mapping_json (dropped in the composition-store refactor) are gone.

topology_observations

One row per source-scan snapshot — the append-only audit/history log (retention enforced on write). resolve() does NOT read this; it reads the contribution_* store. ObservationsService.record() writes the audit row AND materializes the snapshot into a contribution in one transaction (so they can’t diverge). A hand-drawn Manual source’s editor save records here too (the human is the “scanner”).

topology_resolved_graph

Materialized resolver output (1 per topology) so reads/polls skip re-resolve + re-layout. Served only when built_revision equals the topology’s current composition_revision and resolver_version matches the code constant; otherwise recomputed.

contribution_* (DB-native contribution store)

The canonical composition store (migration 016). Each source’s graph — and the project overlay — is decomposed into queryable rows here; resolve() reads these (via buildContributions/buildGraph) and ingestGraph writes them. A contribution is keyed by (topology_id, source_id).

Key indexes: idx_contrib_one_per_attach (one contribution per attach row), idx_contrib_one_intrinsic (one project overlay per topology, attachment_id NULL). All FKs are explicitly indexed (SQLite does not auto-index FKs). See docs/design/db-native-persistence.md for the full schema + invariants.

topology_data_sources

Junction table for topology-datasource relationships (many-to-many).

Unique constraint: (topology_id, data_source_id, purpose)

dashboards

Dashboard layouts for displaying multiple topologies.

settings

Key-value store for application settings.

migrations

Tracks applied database migrations.

Indexes

(idx_topologies_topology_source / idx_topologies_metrics_source were dropped with their columns in the composition-store refactor.)

Migrations

Schema migrations use numbered SQL files in src/db/migrations/. On startup, the server automatically applies any pending migrations.

(009 and 015 are intentionally skipped. Two startup-time imperative migrations run AFTER SQL migrations — they need the resolver / TS, so they aren’t SQL files: the mapping_json → bindings backfill, and migrateManualToProject which folds any legacy Manual content into each topology’s project overlay and retires the Manual data sources, both settings-guarded one-shots.)

Adding a new migration:

1. Create src/db/migrations/NNN_description.sql (increment number)
2. Write SQL statements (CREATE TABLE, ALTER TABLE, etc.)
3. Restart server - migration applies automatically

Note: Migrations are tracked in the migrations table, not settings.

TypeScript Interfaces

See apps/server/src/types.ts for corresponding TypeScript interfaces:

• DataSource - data_sources table
• Topology - topologies table (shell)
• TopologyDataSource - topology_data_sources table
• TopologyObservation - topology_observations table
• Dashboard - dashboards table
• MetricsMapping (core plugin-types.ts) - the mapping shape derived from metrics-binding attachments at read/poll time (no longer a stored column)

Database Configuration

• Location: {dataDir}/shumoku.db (default: ./data/shumoku.db)
• Journal mode: WAL (Write-Ahead Logging) for better concurrency
• Foreign keys: Enabled (PRAGMA foreign_keys = ON)
• ID generation: nanoid (12 characters)

Backup

DB は WAL モードで動作します。直近の書き込みは shumoku.db-wal に残り、停止して もチェックポイントは強制されないため、shumoku.db 単体のコピーは不完全になり得ます （ほぼ空になることもある）。shumoku.db / -wal / -shm を揃えて取得してください。

サーバー停止中（推奨）:

cp ./data/shumoku.db* ./backup/    # shumoku.db / shumoku.db-wal / shumoku.db-shm

サーバー稼働中（WALモード対応、sqlite3 が必要）:

sqlite3 ./data/shumoku.db ".backup './backup/shumoku.db'"

リストア:

# サーバーを停止してから 3 ファイルを戻す
cp ./backup/shumoku.db* ./data/

マイグレーション前のバックアップ: スキーマ変更を含むアップデート前には手動でバックアップを取ることを推奨します。