Skip to content
structured_log

Data model

The storage schema (log-server-storage) backs everything else in the system. This document walks through the entities and their lifecycle fields; for the exhaustive field list and every scenario, see specs/log-server-storage/spec.md.

Entity relationships

Section titled “Entity relationships”

Entities at a glance:

  • Group — your organization’s top-level container; owns projects and teams.
  • Project — where log entries actually live; belongs to one Group.
  • Team — a named set of users inside a Group, grantable a role as a unit.
  • User — an account; gets roles via direct grants or team membership.
  • RoleAssignment — a grant of one role to one subject (a user or a team) over one scope (global, a group, or a project).
  • LogEntry — one structured log record.
  • ProjectUsage — running totals used to enforce a project’s quota.
erDiagram
    USER ||--o{ ROLE_ASSIGNMENT : "subject (user)"
    USER ||--o{ TEAM_MEMBER : "belongs to"
    USER ||--o{ REFRESH_TOKEN : owns
    USER ||--o{ PASSWORD_RESET_TOKEN : owns
    GROUP ||--o{ PROJECT : owns
    GROUP ||--o{ TEAM : owns
    TEAM ||--o{ TEAM_MEMBER : has
    TEAM ||--o{ ROLE_ASSIGNMENT : "subject (team)"
    PROJECT ||--o{ PROJECT_SECRET_KEY : has
    PROJECT ||--|| PROJECT_USAGE : "usage counter"
    PROJECT ||--o{ LOG_ENTRY : contains
    USER ||--o{ AUDIT_LOG_ENTRY : "actor (nullable)"

    USER {
        int id PK
        string username UK "unique, forever (decision 27)"
        string password_hash
        string display_name
        string email UK "nullable, unique if set"
        bool is_active "block/unblock, decision 25"
        datetime deleted_at "nullable soft-delete, decision 27"
        bool is_primary_admin "true on at most 1 row, decision 28"
        int token_version "revocation counter, decision 10"
    }
    GROUP {
        int id PK
        string name
    }
    TEAM {
        int id PK
        int group_id FK "exactly one group"
        string name
    }
    PROJECT {
        int id PK
        int group_id FK "NOT NULL"
        string name
        int retention_days "mandatory, decision 13"
        int max_entries "nullable"
        int max_bytes "nullable"
        bool is_blocked "decision 25"
    }
    ROLE_ASSIGNMENT {
        int id PK
        string subject_type "user | team"
        int subject_id
        string role "admin | owner | user"
        string scope_type "global | group | project"
        int scope_id "nullable for global"
    }
    LOG_ENTRY {
        int id PK
        int project_id FK
        datetime timestamp "client-supplied"
        datetime received_at "server clock, not client-trusted"
        string level
        string category
        string logger
        int size_bytes
        json context_json "full original entry"
    }

Why these entities and not others

Section titled “Why these entities and not others”
  • Group owns Projects; Team is a separate entity from Group. A Project’s group_id is NOT NULL — it can’t exist outside a group. Team exists purely for bulk role grants to a subset of a group’s users; it is not a rename of “users in a group.” Decision 6 rejects collapsing the two: a group can hold several teams with different rights, and a Group simultaneously owning projects and being a grant recipient would conflate two different concepts.
  • RoleAssignment is one polymorphic table, not per-scope columns. subject_type: user|team × scope_type: global|group|project covers every grant shape the RBAC model needs (decision 6, 7) — see rbac-and-lifecycle.md for how it’s interpreted. A team-scoped grant is resolved through current membership at authorization time, not copied onto each member — adding someone to a team changes their effective rights immediately, with nothing to keep in sync.
  • LogEntry keeps typed columns and the full original JSON. project_id/timestamp/level/category/logger/correlation fields get their own indexed columns because they’re the common filter targets (composite index on (project_id, level, timestamp)); every other field — including arbitrary user-supplied context keys with no fixed schema — lives in context_json, queried via SQLite’s JSON1 (json_extract) when needed. Nothing is lost to fit a schema that can’t know about a caller’s custom fields in advance.
  • received_at is not timestamp. timestamp is whatever the client claims; received_at is when the server actually saw the batch. They can diverge under retry/backoff delivery delays, and both are kept so an incident investigation can tell “when it happened” from “when we found out.”
  • Refresh tokens and password-reset tokens are marked revoked/used, never deleted. Keeping the row (with revoked_at/used_at set) lets the server distinguish “this token never existed” from “this token existed and was already consumed” — the latter is the signal used to detect refresh-token reuse and revoke the whole chain (see auth.md).

User lifecycle fields, at a glance

Section titled “User lifecycle fields, at a glance”

User carries three independent-but-related state fields — conflating them was a real design trap this schema avoids:

Field Meaning Set by Reversible?
is_active Can this account authenticate right now? block/unblock (decision 25), also flipped by delete Yes, via unblock — except see below
deleted_at Was this account ever deleted (self or admin)? DELETE /v1/users/me / DELETE /v1/users/:id (decision 27) No — unblock explicitly refuses accounts with deleted_at set
is_primary_admin Is this the one account bootstrap created first? The first bootstrap only — auto-creation on an empty database (decision 49) or create-admin (decision 28) N/A — no API path sets or clears it

Deletion sets is_active = false and deleted_at = now() — it reuses blocking’s revocation mechanics (refresh-token revocation, token_version bump) rather than inventing a second one, but adds the permanent marker on top. See rbac-and-lifecycle.md for the full state machine and who can trigger which transition.

Indexes that matter

Section titled “Indexes that matter”
  • log_entries: project_id, timestamp, level, category, session_id, request_id, plus a composite (project_id, level, timestamp) for the single most common query shape (scope + level + time range).
  • users.username: unique, not scoped to deleted_at IS NULL — a deleted account’s username stays reserved until the row is physically purged (decision 27; purge itself is an explicit non-goal).
  • users.email: unique partial index (WHERE email IS NOT NULL), same non-exclusion of deleted rows.
  • users.is_primary_admin: unique partial index (WHERE is_primary_admin = true) — a schema-level guarantee that a second row can never carry the flag, even if either bootstrap path had a bug (decisions 28/49).

Storage engine

Section titled “Storage engine”

drift over embedded SQLite (NativeDatabase, journal_mode=WAL): one writer connection, and — since reads no longer share it — a pool of reader connections, each its own isolate (--db-read-pool-size) — see technology-stack.md for why drift specifically and for the read pool, and the “one process” principle in README.md (why the server doesn’t shard across processes) for what a single writer still constrains elsewhere in the design.

PostgreSQL: an operator-chosen alternative backend

Section titled “PostgreSQL: an operator-chosen alternative backend”

Implemented — --db-backend=postgres (StructuredLogDatabase.openPostgres, lib/src/storage/database.dart) is a real, tested alternative to the default SQLite path, not a plan. The full decision record (context, alternatives, risks, and every dialect-specific fix found along the way) lives in openspec/changes/add-postgres-backend/ (proposal.md/design.md/specs/); this is a summary for readers of the architecture, not a replacement for it.

Embedded SQLite fits this server’s “one process, no external dependency” positioning (see README.md), but not every deployment wants that trade-off — an operator who already runs a managed PostgreSQL (backups, monitoring, HA already solved there) gains nothing from a second, separately-operated storage mechanism. The storage backend is an explicit, operator-chosen setting at deployment time (--db-backend=sqlite|postgres, SQLite default, unchanged behavior) — never a runtime toggle, and never an automatic data migration between the two; an operator choosing Postgres starts from an empty database.

What this does not change: the object schema (one set of drift tables, generated for both dialects via drift_postgres), and every behavior visible through the HTTP API — round-trip of arbitrary context fields, filtering by them, id ordering under concurrent ingestion. Those stay identical regardless of backend; only the mechanism underneath differs — confirmed, not assumed: every dialect-specific code path has a test exercising it against a real Postgres instance (test/storage/postgres_*.dart, tag postgres).

What genuinely differs by backend, architecturally:

  • No reader-isolate pool under Postgres. --db-read-pool-size exists specifically because one SQLite file has one writer connection; PostgreSQL is a real client-server database with its own connection pool (package:postgres’s Pool) and native concurrent writers (MVCC) — there is no separate “reader pool” concept to port, only a differently-shaped setting (--db-postgres-pool-size, default 10) for the size of one shared pool. Setting --db-read-pool-size under this backend only warns at startup; it has no effect.
  • No PRAGMA tuning under Postgres. journal_mode=WAL/ busy_timeout/synchronous=NORMAL exist to work around what a single-writer SQLite file needs; PostgreSQL’s durability and concurrent-read/write behavior are its own defaults, not something this server has to configure.
  • A separate, Postgres-specific implementation of the batch-insert hot path (DriftLogStore._insertPostgres, lib/src/storage/log_store.dart). The SQLite path’s last_insert_rowid() + id-range read (the specific mechanism behind the read-pool/group-commit throughput numbers in technology-stack.md) relies on one connection holding a transaction that nothing else can insert into concurrently — sound for SQLite, not a safe assumption for Postgres in general. The Postgres path instead issues one multi-row INSERT ... RETURNING *, which ties the returned rows to the inserted ones directly, without relying on id adjacency — verified under concurrent batches specifically, not just a single one (test/storage/postgres_log_store_test.dart).
  • context.<key> filtering ports from SQLite’s JSON1 json_extract to Postgres’s jsonb #>> path operator (LogFilter.appendConditions, lib/src/storage/log_filter.dart) — the one function neither dialect has in common. One accepted, documented asymmetry survives the port: a JSON boolean context value reads back as 1/0 on SQLite (no native boolean type) but as the text true/false on Postgres; the in-memory predicate the live stream uses (LogFilter.matches) always renders the SQLite convention, since it has no dialect to consult.
  • Raw-SQL placeholders don’t carry over at all — ? is SQLite/ODBC syntax that Postgres’s wire protocol rejects outright ($1, $2, … positional parameters instead). Every hand-written raw-SQL fragment (query.dart’s buildLogQuerySql, audit_query.dart’s buildAuditQuerySql, the batch insert above) runs its finished SQL text through one small dialect-aware renumbering pass (placeholdersForDialect) rather than building the placeholder syntax into each condition individually.

Everything else that reads as “SQLite-specific” in the codebase today — a DB-side createdAt default expression (confirmed to already be dialect-portable, no change needed — the “surprising” part of this decision), a couple of raw-SQL boolean literals, one partial-index DDL statement — is a portability detail with its own resolution decided and implemented in design.md, not an architectural fork; see that document for the complete list.