Documents REST & SQLite Persistence

ELI5

Before two people can collaborate on a document, the document must exist as a row in SQLite. The relay’s REST API is the front door for that row: create it, list yours, archive it, soft-delete it. The “real” content lives in operations; this table just owns identity, ownership, and status.

Technical Deep Dive

Storage Decisions (ADR-003)

Document Model

classDiagram
    class Document {
      +Uuid id
      +String title
      +Option~String~ description
      +String owner_id
      +DocumentStatus status
      +DateTime~Utc~ created_at
      +DateTime~Utc~ updated_at
      +Option~DateTime~Utc~~ deleted_at
    }
    class DocumentStatus {
      <<enum>>
      Active
      Archived
      Deleted
    }
    class DocumentRepository {
      -SqlitePool pool
      +create(title, owner_id) Document
      +get(id) Document "active only"
      +get_any(id) Document "any status"
      +list_by_owner(owner_id) Vec~DocumentSummary~
      +update(id, request) Document
      +touch(id) ()
      +delete(id) () "soft"
      +is_owner(doc, user_id) bool
    }
    Document --> DocumentStatus
    DocumentRepository --> Document

Configuration

AppState::new runs documents.initialize() then operations.initialize() on startup (relay/src/services/state.rs); both apply their migration files via sqlx::raw_sql.

Create + Edit Round-Trip

sequenceDiagram
    autonumber
    participant Client as app (api.ts)
    participant API as /api/documents (handlers/documents.rs)
    participant Repo as DocumentRepository
    participant DB as SQLite
    participant WS
    Client->>API: POST /api/documents { title }
    API->>Repo: create(title, owner_id from auth)
    Repo->>DB: INSERT INTO documents ... id = uuidv7()
    DB-->>Repo: ok
    Repo-->>API: Document
    API-->>Client: 201 { id, ... }
    Client->>WS: ws://.../ws/documents/:id
    WS->>WS: authenticate + join_document
    WS->>Repo: get(id) (verify exists, active)
    WS-->>Client: joined_document { current_version, user_count }

Indexes That Matter for Listing

Health & Uptime

AppState::check_database runs SELECT 1; uptime_seconds returns started_at.elapsed().as_secs(). Both exposed via the /api/health mount in main.rs.

Key Terms

• DocumentSummary → reduced projection used in list responses; avoids streaming the full description for many rows.
• soft delete → row remains, status = 'deleted', deleted_at populated; get returns 404 but get_any still finds it (audit + recovery).
• touch → bumps updated_at only; called when an op lands so list ordering by recency reflects activity, not metadata edits.

Q&A

Q: Why UUIDv7 instead of UUIDv4? A: v7 is time-ordered, so a B-tree index on id reflects creation order — cheap recency sorts without hitting created_at.

Q: Why no foreign key from operations.document_id to documents.id? A: The migration leaves the FK commented; foreign_keys = on is set on the connection pool, but the constraint is omitted to keep the operations table independently creatable in tests. ADR-003 calls this out.

Q: What error does opening a missing local SQLite path produce? A: With mode=rwc SQLx tries to create the file; on a non-existent parent directory it fails — set RELAY_DATABASE_URL=sqlite:./chronicle.db?mode=rwc for local dev.

Examples

A fresh integration test sets RELAY_DATABASE_URL=sqlite::memory:, lets AppState::new run both migrations, then creates a document and connects a WS — the same path production uses, with :memory: standing in for the on-disk file.