# LoreLoom Backend Persistence Specification

## Goals

LoreLoom needs durable backend persistence for the first time, but the backend should support the existing writing model rather than replace it.

Primary goals:

- protect users from browser storage loss
- preserve the current local-first writing flow
- remain lightweight enough for Raspberry Pi hosting
- stay understandable and maintainable for a small self-hosted deployment
- avoid premature multi-user or cloud-first complexity

## Non-Goals

The first backend version explicitly does not include:

- authentication
- multi-user collaboration
- websockets
- realtime editing
- AI infrastructure
- cloud sync
- microservices
- Kubernetes
- row-level live mutation APIs

The backend is a durable mirror for a single-user local-first app, not a collaborative platform.

## Backend Architecture

### Overview

LoreLoom should use a small monolithic API under `apps/api`:

```text
apps/
  web/
  api/
    package.json
    tsconfig.json
    src/
      server.ts
      app.ts
      config.ts
      routes/
        health.ts
        projects.ts
      validators/
        project-bundle.ts
      db/
        client.ts
        pragmas.ts
        migrations/
          0001_init.sql
      repositories/
        projects.ts
        structure-nodes.ts
        text-blocks.ts
        codex-entries.ts
        revision-snapshots.ts
      services/
        project-bundle-service.ts
        sync-service.ts
```

### Fastify Role

Fastify is the HTTP server and request lifecycle framework.

It is recommended because it is:

- lightweight
- fast on low-power hardware
- easy to reason about
- TypeScript-friendly
- a good fit for small monolithic APIs

Fastify responsibilities:

- define HTTP routes
- handle request parsing
- run validation
- map service results to HTTP responses
- expose health checks

### SQLite Role

SQLite is the durable persistence layer.

It is recommended because it is:

- simple to deploy
- Raspberry Pi friendly
- easy to back up
- reliable for single-user LAN workloads
- understandable without extra operational tooling

SQLite should run in:

- WAL mode

Recommended pragmas:

- `journal_mode = WAL`
- `synchronous = NORMAL`
- `foreign_keys = ON`
- `busy_timeout` configured to a reasonable value

### better-sqlite3 Role

`better-sqlite3` should be the database access library.

It is recommended because it is:

- simple
- synchronous and predictable
- low overhead
- widely used for small SQLite-backed services

This suits LoreLoom because the first backend version is a small monolith with modest request volume.

### Zod Validation Role

Zod should validate:

- request payloads
- route params where useful
- imported project bundle shapes
- internal API DTO boundaries

SQLite constraints should still enforce:

- required fields
- uniqueness
- foreign keys
- delete behavior

Zod is not a replacement for database constraints. It is the first validation boundary.

### Docker and Volume Philosophy

The backend should be designed for `Docker Compose` deployment later.

Recommended approach:

- one `api` container
- one bind-mounted data directory
- SQLite file persisted on the host

Example philosophy:

- app code is disposable
- database file and migration history live in a mounted volume
- reverse proxy support can be added later without changing the core app structure

Recommended persisted path inside the container:

- `/data/loreloom.sqlite`

Current local-development default:

- `apps/api/data/loreloom.sqlite`

Override via environment variable:

- `DATABASE_PATH=/absolute/path/to/loreloom.sqlite`

### Local API Hardening Defaults

The API should stay conservative by default for home and LAN deployment:

- request body size should be capped for project bundles
- Fastify JSON logging should remain enabled in non-test environments
- same-origin requests should be the default
- CORS should only be enabled deliberately for LAN development, not by default

For the current implementation:

- `MAX_REQUEST_BODY_BYTES` defaults to `2097152` (2 MiB)
- `LOG_LEVEL` defaults to `info`
- `DEV_CORS_ORIGIN` is optional and disabled by default

If `DEV_CORS_ORIGIN` is set, it should be used only for explicit local/LAN development origins such as a Vite dev server.

## Data Persisted

The backend should persist the durable project graph:

- projects
- structure nodes
- text blocks
- codex nodes
- codex entries
- revision snapshots

These should retain the same stable IDs the client already generates.

That is important because LoreLoom remains local-first. The browser must be able to create and edit records offline without waiting for server-issued IDs.

### Client-Only State

The backend should not persist transient UI state such as:

- collapsed nodes
- focus mode
- command palette state
- temporary selections
- lookup panels
- autosave indicator state
- currently expanded codex entry

Those belong to the client session, not to durable project data.

## SQLite Schema Proposal

This section proposes the initial normalized schema.

### `schema_migrations`

Purpose:

- track applied backend schema migrations

Fields:

- `version` `INTEGER PRIMARY KEY`
- `name` `TEXT NOT NULL`
- `applied_at` `TEXT NOT NULL`

Notes:

- version numbers are backend schema versions only
- they are separate from frontend store versions and backup format versions

### `projects`

Purpose:

- top-level project records

Fields:

- `id` `TEXT PRIMARY KEY`
- `owner_user_id` `TEXT NULL REFERENCES users(id) ON DELETE RESTRICT`
- `name` `TEXT NOT NULL`
- `server_revision` `INTEGER NOT NULL DEFAULT 1`
- `created_at` `TEXT NOT NULL`
- `updated_at` `TEXT NOT NULL`

Indexes:

- `INDEX projects_owner_user_id_idx ON projects(owner_user_id)`
- `INDEX projects_updated_at_idx ON projects(updated_at)`

Notes:

- `server_revision` is used for optimistic project-bundle sync
- IDs remain client-generated and stable
- `owner_user_id` must stay nullable during migration and first-run setup so pre-auth projects can exist safely until the first admin claims them

### `structure_nodes`

Purpose:

- hierarchical project structure

Fields:

- `id` `TEXT PRIMARY KEY`
- `project_id` `TEXT NOT NULL`
- `parent_id` `TEXT NULL`
- `type` `TEXT NOT NULL`
- `custom_type_label` `TEXT NULL`
- `title` `TEXT NOT NULL`
- `order_index` `INTEGER NOT NULL`
- `created_at` `TEXT NOT NULL`
- `updated_at` `TEXT NOT NULL`

Foreign keys:

- `FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE`
- `FOREIGN KEY (parent_id) REFERENCES structure_nodes(id) ON DELETE RESTRICT`

Indexes:

- `INDEX structure_nodes_project_id_idx ON structure_nodes(project_id)`
- `INDEX structure_nodes_parent_id_idx ON structure_nodes(parent_id)`
- `INDEX structure_nodes_project_parent_order_idx ON structure_nodes(project_id, parent_id, order_index)`

Notes:

- `ON DELETE RESTRICT` for `parent_id` matches current frontend expectations that non-empty nodes are not freely removed by cascading tree deletes
- node order remains explicit and durable

### `text_blocks`

Purpose:

- live manuscript units

Fields:

- `id` `TEXT PRIMARY KEY`
- `project_id` `TEXT NOT NULL`
- `structure_node_id` `TEXT NOT NULL`
- `title` `TEXT NOT NULL`
- `content` `TEXT NOT NULL`
- `created_at` `TEXT NOT NULL`
- `updated_at` `TEXT NOT NULL`

Foreign keys:

- `FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE`
- `FOREIGN KEY (structure_node_id) REFERENCES structure_nodes(id) ON DELETE RESTRICT`

Indexes:

- `INDEX text_blocks_project_id_idx ON text_blocks(project_id)`
- `INDEX text_blocks_structure_node_id_idx ON text_blocks(structure_node_id)`
- `INDEX text_blocks_updated_at_idx ON text_blocks(updated_at)`

Notes:

- content stores TipTap HTML
- there is intentionally no separate text block order field yet because the current client model still derives order from timestamps and structure placement

### `codex_entries`

Purpose:

- project-level worldbuilding references attached to codex nodes

Fields:

- `id` `TEXT PRIMARY KEY`
- `project_id` `TEXT NOT NULL`
- `codex_node_id` `TEXT NULL`
- `title` `TEXT NOT NULL`
- `type` `TEXT NOT NULL`
- `content` `TEXT NOT NULL`
- `tags_json` `TEXT NOT NULL`
- `created_at` `TEXT NOT NULL`
- `updated_at` `TEXT NOT NULL`

Foreign keys:

- `FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE`
- `FOREIGN KEY (codex_node_id) REFERENCES codex_nodes(id) ON DELETE SET NULL`

Indexes:

- `INDEX codex_entries_project_id_idx ON codex_entries(project_id)`
- `INDEX codex_entries_codex_node_id_idx ON codex_entries(codex_node_id)`
- `INDEX codex_entries_updated_at_idx ON codex_entries(updated_at)`
- `INDEX codex_entries_title_idx ON codex_entries(title)`

Notes:

- tags can be stored as JSON text in v1 to keep the schema small
- `codex_node_id` starts nullable for migration safety and older flat codex data
- legacy flat codex entries should be normalized under a default top-level `Codex` node during bundle reads and writes
- if tag querying becomes heavier later, tags can be normalized into a join table in a future migration

### `codex_nodes`

Purpose:

- hierarchical codex structure for grouping codex entries

Fields:

- `id` `TEXT PRIMARY KEY`
- `project_id` `TEXT NOT NULL`
- `parent_id` `TEXT NULL`
- `type` `TEXT NOT NULL`
- `title` `TEXT NOT NULL`
- `custom_type_label` `TEXT NULL`
- `tags_json` `TEXT NOT NULL`
- `order_index` `INTEGER NOT NULL DEFAULT 0`
- `created_at` `TEXT NOT NULL`
- `updated_at` `TEXT NOT NULL`

Foreign keys:

- `FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE`
- `FOREIGN KEY (parent_id) REFERENCES codex_nodes(id) ON DELETE CASCADE`

Indexes:

- `INDEX codex_nodes_project_id_idx ON codex_nodes(project_id)`
- `INDEX codex_nodes_parent_id_idx ON codex_nodes(parent_id)`
- `INDEX codex_nodes_project_parent_order_idx ON codex_nodes(project_id, parent_id, order_index)`

Notes:

- codex nodes preserve client-generated ids
- a default top-level `Codex` node can be created during migration or bundle normalization for older flat projects

### `revision_snapshots`

Purpose:

- text block recovery points

Fields:

- `id` `TEXT PRIMARY KEY`
- `scene_id` `TEXT NOT NULL`
- `project_id` `TEXT NOT NULL`
- `title` `TEXT NOT NULL`
- `content` `TEXT NOT NULL`
- `created_at` `TEXT NOT NULL`

Foreign keys:

- `FOREIGN KEY (scene_id) REFERENCES text_blocks(id) ON DELETE CASCADE`
- `FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE`

Indexes:

- `INDEX revision_snapshots_scene_id_idx ON revision_snapshots(scene_id)`
- `INDEX revision_snapshots_project_id_idx ON revision_snapshots(project_id)`
- `INDEX revision_snapshots_scene_created_at_idx ON revision_snapshots(scene_id, created_at DESC)`

Notes:

- snapshots store full rich text HTML content
- snapshot creation rules remain client-driven
- the backend persists and returns snapshots but does not invent them

## API Contract Draft

The first backend version should expose project-bundle routes rather than row-level live mutation routes.

### `GET /health`

Purpose:

- simple process and dependency health check

Response summary:

```json
{
  "status": "ok",
  "service": "loreloom-api",
  "database": "ok"
}
```

### `GET /api/projects`

Purpose:

- fetch project summaries for startup hydration and dashboard syncing

Response summary:

```json
{
  "projects": [
    {
      "id": "project-id",
      "name": "Project Name",
      "serverRevision": 7,
      "createdAt": "ISO",
      "updatedAt": "ISO"
    }
  ]
}
```

Notes:

- do not return full project bundles here
- this route is for lightweight listing only

### `GET /api/projects/:id/bundle`

Purpose:

- fetch one full project bundle

Response summary:

```json
{
  "project": {
    "id": "project-id",
    "name": "Project Name",
    "createdAt": "ISO",
    "updatedAt": "ISO"
  },
  "serverRevision": 7,
  "structureNodes": [],
  "scenes": [],
  "codexNodes": [],
  "codexEntries": [],
  "revisionSnapshots": []
}
```

Notes:

- route returns the normalized current server representation
- the bundle mirrors the durable project graph the frontend already uses
- the API currently uses the legacy `scenes` field name for text blocks to stay compatible with the existing frontend store shape

### `PUT /api/projects/:id/bundle`

Purpose:

- upsert a full project bundle from the client

Request summary:

```json
{
  "baseRevision": 7,
  "bundle": {
    "project": {
      "id": "project-id",
      "name": "Project Name",
      "createdAt": "ISO",
      "updatedAt": "ISO"
    },
    "structureNodes": [],
    "scenes": [],
    "codexNodes": [],
    "codexEntries": [],
    "revisionSnapshots": []
  }
}
```

Successful response summary:

```json
{
  "ok": true,
  "serverRevision": 8,
  "updatedAt": "ISO"
}
```

Conflict response summary:

HTTP `409 Conflict`

```json
{
  "error": "revision_conflict",
  "serverRevision": 8,
  "bundle": {
    "project": {},
    "structureNodes": [],
    "scenes": [],
    "codexNodes": [],
    "codexEntries": [],
    "revisionSnapshots": []
  }
}
```

Notes:

- server should validate that `:id` matches `bundle.project.id`
- server should replace the stored project graph atomically inside one transaction
- this route is intentionally bundle-oriented rather than patch-oriented
- the request body currently uses the legacy `scenes` key for text blocks for compatibility with the current client model
- older bundle payloads without `codexNodes` should still be accepted and normalized under a default top-level codex node

### `DELETE /api/projects/:id`

Purpose:

- delete a full project bundle

Response summary:

```json
{
  "ok": true
}
```

Notes:

- delete cascades should remove all project-owned rows
- frontend can treat deletion as durable once this succeeds

## Sync Model

### Local Autosave

Local autosave remains immediate and unchanged.

The browser continues to:

- update the live text block title and content continuously
- preserve a local cache for offline editing

The backend does not replace this behavior.

### Backend Sync Timing

Backend sync should be:

- debounced
- opportunistic
- performed at safe boundaries

Recommended safe points:

- a few seconds after local changes settle
- when leaving a project page
- when leaving a text block editor
- when using Continue in New Text Block
- when importing a project backup
- on page hide / unload where feasible

### Project Bundle Download

Startup behavior should be:

1. load local client cache immediately
2. request project summaries from the backend
3. fetch project bundles as needed
4. merge or replace local durable state with server data based on revision logic

This preserves fast startup and local-first feel.

### Project Bundle Upload

The client should upload the full project bundle, not row-level mutations.

That keeps the first backend version:

- simpler
- more durable
- easier to reason about
- closer to the current frontend store shape

### `server_revision` / `baseRevision`

Each project should maintain a server-side integer revision.

Client upload flow:

- client sends `baseRevision`
- server compares it with the stored `server_revision`
- if they match, server accepts the write and increments revision
- if they do not match, server returns `409 Conflict`

### `409 Conflict` Behavior

On conflict:

- server returns the latest full server bundle
- frontend keeps local unsynced data intact
- frontend can later present a conflict recovery flow

For v1, the important rule is:

- do not silently overwrite newer server data

Automatic merge logic is out of scope for the first backend version.

## Backup Model

Backend persistence does not replace LoreLoom backups.

The system should preserve the existing writer-owned backup model:

- JSON project backup export/import remains available
- story TXT export remains separate
- revision snapshots remain part of project backups

### Backend and JSON Backups

Relationship:

- backend persistence protects against browser storage loss
- JSON backup export protects explicit user-controlled portability and archival

The backend should not remove or hide manual backup export/import.

### Story TXT Export

Story TXT export remains:

- manuscript-oriented
- snapshot-free
- codex-free
- preview-safe

It is not a persistence or recovery format.

### Revision Snapshots

Snapshots remain:

- client-created at meaningful session boundaries
- server-persisted durably
- included in JSON backups
- excluded from story TXT export and book preview

### SQLite Host-Level Backups

The SQLite file itself becomes an additional recovery layer.

Recommended host-level backup philosophy:

- back up the mounted `/data` directory
- stop the container or use SQLite-safe copy procedures if needed
- keep JSON export/import as an app-level portability tool even when host-level DB backups exist

## Migration and Versioning

LoreLoom should keep these version domains separate:

### Frontend Store Version

Purpose:

- migrate local browser-persisted client state

Examples:

- Zustand persist version
- browser cache normalization rules

### Project Backup Version

Purpose:

- version the human-readable JSON backup format

Current example:

- backup format version `4`

### Backend Schema Migration Version

Purpose:

- track SQLite schema evolution

Examples:

- `0001_init.sql`
- `0002_add_revision_snapshots.sql`

### API Contract Version

Purpose:

- optional future route-versioning layer if breaking HTTP contract changes are ever needed

For v1:

- explicit API versioning is not required yet
- route shape should remain simple

Important rule:

- do not couple these four version domains together

## Phased Implementation Plan

### Phase 1: Backend Foundation

- create `apps/api`
- set up Fastify + TypeScript
- set up SQLite connection and WAL pragmas
- add migration runner
- create schema
- implement `GET /health`
- implement project list and bundle routes

### Phase 2: Frontend Sync

- keep local autosave unchanged
- add debounced project bundle sync
- add dirty project tracking
- sync at safe navigation and lifecycle points

### Phase 3: Startup Hydration

- fetch backend project summaries on startup
- fetch project bundles as needed
- reconcile local cache with server state
- keep browser cache as the primary immediate startup source

### Phase 4: Conflict Handling

- enforce `baseRevision` checks
- return `409 Conflict` on mismatch
- preserve unsynced local work
- add a minimal conflict recovery flow in the frontend later

### Phase 5: Docker and Raspberry Pi Hardening

- add `Dockerfile` for `apps/api`
- add `docker-compose.yml`
- mount SQLite data volume
- test on ARM / Raspberry Pi
- document reverse proxy support for future LAN exposure

## Summary

The first LoreLoom backend should be a small monolith that:

- preserves the local-first browser editing model
- stores the durable project graph in SQLite
- syncs whole project bundles instead of fine-grained live mutations
- avoids silent overwrite with simple revision checks
- stays light enough for Raspberry Pi and home LAN hosting

This is the smallest backend architecture that meaningfully improves durability without pulling LoreLoom into unnecessary SaaS or collaboration complexity.
