Database

Persistence model

@cadcamfun/db opens SQLite through bun:sqlite and exposes a Drizzle database inside the Effect Database service. The sidecar stores its application database at <dataDir>/cadcamfun.db. The connection enables write-ahead logging and foreign-key enforcement before applying migrations.

The database contains the working copy of projects and their related resources. Project version-control objects, pull requests, and workflow runs are also stored locally. AI provider credentials use a separate owner-readable JSON file; the application bearer token is supplied at launch rather than persisted as a preference.

Schema and entry points

packages/db/src/schema.ts defines tables, indexes, relationships, defaults, and inferred select/insert types. src/index.ts exports Database, DatabaseError, the Drizzle Db type, and the schema namespace. src/upgrade.ts owns migration planning, backups, and transactional application; src/migrate.ts is the command-line entry point. Generated SQL and the migration journal live in packages/db/drizzle.

Every connection opened by packages/db/src/index.ts switches SQLite to journal_mode = WAL and turns on foreign_keys. Closing the database runs wal_checkpoint(TRUNCATE) so the main file is complete when the process exits. If the checkpoint is busy or fails, a warning is logged and shutdown continues.

Stored values

Domain identifiers default to UUIDs where configured by the table. Drizzle maps timestamp columns to JavaScript dates and JSON columns to the declared record types. HTTP repositories convert dates into their wire representation and validate JSON at the domain boundary. A TypeScript type annotation on a stored JSON column does not itself prove that imported or older data matches the current domain schema.

Projects own drawings, components, and toolpaths. Toolpaths can reference a drawing, material, cutter, and machine configuration; their data field can carry a portable manufacturing document. Separate drawing, component, and toolpath version tables retain resource versions in addition to whole-project Git history.

Query through the service

Database.layer accepts a file path or :memory:, an optional migrations folder, and an optional backup directory. It acquires the SQLite handle for its Effect scope. Callers use query(operation, run) to execute synchronous or asynchronous Drizzle work; foreign exceptions become DatabaseError with the operation name and original cause.

import { Database, schema } from "@cadcamfun/db"
import { Effect } from "effect"

const loadProjects = Effect.gen(function* () {
  const database = yield* Database
  return yield* database.query("projects.list", (db) =>
    db.select().from(schema.projectTable).all())
})

Repositories own access rules and row interpretation; the shared query helper supplies failure attribution. It does not automatically authorize a query or create a transaction around an arbitrary sequence of service calls.

Upgrade behavior

Opening a layer with a migrations folder reads the migration files and compares their names with __drizzle_migrations. A journal from the older format is backfilled by matching migration timestamps and hashes. An unmatchable journal fails the upgrade instead of guessing a migration identity.

When an existing on-disk database needs an upgrade, the service creates a pre-migration SQLite backup using VACUUM INTO. It writes to a partial file, renames the completed snapshot, and retains the latest five matching pre-migration backups. The default backup directory is backups beside the database. New and in-memory databases do not need this pre-upgrade backup.

Journal changes and pending SQL statements run in one immediate transaction. A failed migration rolls the transaction back and fails layer construction. The application should not announce readiness when database initialization fails.

Migration commands

bun run db:generate
bun run db:migrate

Generate migrations after changing schema.ts, and keep the generated SQL and journal together. The migration CLI uses DATABASE_URL when supplied, removes an initial file: prefix, and otherwise opens ./data/cadcamfun.db. The root wrapper runs this script with packages/db as its working directory, so its default target is packages/db/data/cadcamfun.db. Web Studio uses data/cadcamfun.db at the repository root. The packaged sidecar uses CADCAMFUN_DATA_DIR. Select an explicit database path when running maintenance so these entry points do not operate on different files unintentionally.

Without a migrations folder, Database.layer opens the database but does not create application tables. Tests that need the complete schema provide the migration directory with an in-memory database.

Data domains

• Identity and collaboration: users, accounts, sessions, authenticators, verification tokens, subscriptions, organizations, membership, invitations, conversations, messages, notifications, and push subscriptions.
• Design and manufacture: projects, drawings, components, their versions and comments, materials, tools, machine configurations, toolpaths, and production cost records.
• Application services: files, library items, plugin registry/configuration, API keys, analytics and activity records, marketplace data, MCP configuration, and local key-value entries.
• Project history: Git repositories, objects and refs, pull requests and reviews, and workflow runs.

A table's presence establishes a persistence model. Network integrations and application features depend on their actual handlers and services, rather than on table existence alone.

Local key-value storage

LocalKeyValue persists JSON-compatible application state. The sidecar KvStore supports get, upsert, delete, and prefix-filtered listing. Keys must contain non-whitespace text and be at most 512 characters. Values allow null, strings, booleans, finite numbers, arrays, and plain objects; cycles, functions, non-finite numbers, and non-plain objects are rejected.

Entries include an update timestamp. Upserts replace the value for the same key. Prefix listing sorts by key and filters in memory. Use this store for preferences and assistant context; credentials belong to their dedicated credential service.

Shutdown and recovery

The database scope finalizer requests PRAGMA wal_checkpoint(TRUNCATE) before closing the handle. A busy checkpoint or close failure is logged as a warning so remaining finalizers can run. The connection owner, rather than individual HTTP handlers, closes the handle.

For a consistent manual backup, stop the application cleanly before copying its data directory, or use SQLite's backup facilities while it is running. A live database can have state in its WAL file. To inspect a pre-migration backup, work on a copy and preserve the original database and backups until recovery is confirmed.

Repository responsibilities

Keep SQLite connection creation and migration logic in this package. Sidecar repositories use the provided database service and map storage failures to the HTTP errors of their resource. Keep geometry calculation in the core and secrets in the credential or token services. Database backups contain project and account data and need the same access controls as the active data directory.

Inspect a database without changing it

After a clean shutdown, use the SQLite CLI against an explicit database path. Read-only mode avoids accidentally creating a new empty file when a path is wrong. These checks inspect structure and consistency without exposing account or credential rows.

sqlite3 -readonly /absolute/path/cadcamfun.db 'PRAGMA quick_check;'
sqlite3 -readonly /absolute/path/cadcamfun.db 'PRAGMA foreign_key_check;'
sqlite3 -readonly /absolute/path/cadcamfun.db "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;"
sqlite3 -readonly /absolute/path/cadcamfun.db 'SELECT name, applied_at FROM __drizzle_migrations ORDER BY id;'

quick_check normally reports ok; foreign_key_check returns rows only for violations. A journal without the name/applied_at columns is an older format, which the application upgrade code must reconcile before using the last query. Check paths and migration identity before attempting repairs.

The application sets foreign-key enforcement on its own connection. A separately opened maintenance connection must explicitly enable it before making authorized writes; the PRAGMA is connection-specific.

Source: packages/db/src/index.ts, upgrade.ts, and database.test.ts.

Use a disposable database in code

A complete in-memory service scope is useful for repository examples and isolated diagnostics. The scope owns the connection and applies the checked-in migrations before the effect reads the service.

import { Database, schema } from "@cadcamfun/db"
import { Effect } from "effect"

const migrationsFolder = new URL("../../packages/db/drizzle", import.meta.url).pathname
const program = Effect.gen(function* () {
  const database = yield* Database
  return yield* database.query("diagnostic.projects", (db) =>
    db.select({ id: schema.projectTable.id }).from(schema.projectTable).all())
}).pipe(Effect.provide(Database.layer({ path: ":memory:", migrationsFolder })))
console.log(await Effect.runPromise(program)) // []

Save this script as apps/sidecar/diagnostic.ts and run bun apps/sidecar/diagnostic.ts from the repository root. This package supplies the database and Effect dependencies, and the migration URL resolves from that file. A missing or wrong migrations directory is not equivalent to an empty application schema. The Drizzle query callback may run a synchronous query or return a promise; the helper wraps failures under the supplied operation label.

Source: packages/db/src/database.test.ts and index.ts.

Diagnose storage failures

• No such table: confirm the exact database file and migrations folder. Opening without migrations is allowed by the service, but querying application tables then fails.
• Foreign-key constraint failed: inspect the referenced owner/project/resource. Deletion restrictions are schema-specific: deleting a user with an owned project is restricted, while session rows cascade when their user is deleted.
• Backup failed during upgrade: check free space and write access in the database and backup directories. Backup failure stops migration; do not delete the original database to bypass it.
• Legacy migration journal does not match local migrations: preserve the database and recover the matching migration set. Renaming or removing journal rows would hide the incompatible upgrade history.
• Checkpoint busy on shutdown: inspect other open connections before copying only the database file. Retain WAL/SHM siblings until a clean checkpoint or consistent SQLite backup completes.

Migration backups are pre-upgrade recovery points with a five-file retention policy, not continuous backup of subsequent edits. Back up the complete data directory separately according to the installation's recovery requirements.

Source: packages/db/src/index.ts, upgrade.ts, schema.ts, and database.test.ts.

Migration history

The checked-in migrations are applied in folder order. Each folder holds a migration.sql and the drizzle-kit snapshot.json used to compute the next diff. The journal row stores the folder name, its hash, the folder timestamp in milliseconds (created_at), and the ISO time it was applied (applied_at).

Folder names come from drizzle-kit: a UTC timestamp followed by a random name. Never rename or edit a folder once it has been applied anywhere; the upgrade planner identifies applied migrations by name and, for older journals, by timestamp and hash.

Upgrade decision flow

Backfill matching works like this: a legacy row's created_at is truncated to whole seconds and compared with each migration's folder timestamp. If exactly one migration matches, it is used. Otherwise the hash breaks the tie, and as a last resort any migration with the same hash is used.

Backup file anatomy

backups/cadcamfun-20261015-034512.123-6f1c2d4e-8a90-4b7c-9e21-3f5a6b7c8d9e-pre-migrate.db
        └ prefix ┘ └ UTC timestamp ┘ └ random UUID                      ┘

• The prefix is the database file name without .db, followed by a dash.
• The timestamp is UTC with millisecond precision. If the newest existing backup has the same or a later timestamp, the new one is set 1 ms after it, so lexical order always equals creation order.
• The snapshot is written to <name>.partial with VACUUM INTO, then renamed. A crash leaves at most a .partial file, which is removed in a finally block.
• After a successful backup, only the five newest files matching the exact pattern are kept. Files you copy into the directory under other names are never deleted.
• VACUUM INTO produces a compact, self-contained database with no WAL sibling, so a backup can be opened directly.

DATA="/absolute/path/to/data"
cp "$DATA/cadcamfun.db" "$DATA/cadcamfun.db.broken"         # keep the failed state
rm -f "$DATA/cadcamfun.db-wal" "$DATA/cadcamfun.db-shm"
cp "$DATA/backups/cadcamfun-<timestamp>-<uuid>-pre-migrate.db" "$DATA/cadcamfun.db"
sqlite3 -readonly "$DATA/cadcamfun.db" 'PRAGMA quick_check;'   # expect: ok

Core table shapes

These are the tables the desktop editor, history and kernel read most often. Column names are the SQL names; Drizzle exposes them under the same keys.

Deleting a project removes its drawings, components, toolpaths, history objects and refs, pull requests, workflow runs, mesh assets and operation logs. Version and comment rows cascade from their parent resource. A CadOperationLog row survives the deletion of its mesh asset with meshAssetId set to null. Toolpath links to materials, tools and machine configurations have no cascade, so a referenced material cannot be deleted while a toolpath points at it.

Full table catalog

The schema defines 60 tables. The list below was produced from the Drizzle table definitions: SQL name, exported constant, column count, number of declared indexes, and foreign keys with their delete rule. "no action" is SQLite's default: the delete fails while referencing rows exist (foreign keys are enforced on the application connection).

Writing safely

• Wrap multi-statement changes in a Drizzle transaction inside one query call: database.query("op", (db) => db.transaction((tx) => ...)). Separate query calls are separate transactions.
• Give each query a stable, dotted operation name (projects.list, toolpaths.update). It is the only context a DatabaseError carries besides the cause, and it appears in logs.
• Generate ids with newId() (a UUID) or let the column default do it; do not build ids from names.
• Validate JSON columns when reading them. The declared TypeScript type is not checked by SQLite.
• Use LocalKeyValue through the sidecar key-value service rather than writing rows directly, so the key and value validation is applied.

import { Database, schema } from "@cadcamfun/db"
import { eq } from "drizzle-orm"
import { Effect } from "effect"

const renameProject = (id: string, name: string) => Effect.gen(function* () {
  const database = yield* Database
  return yield* database.query("projects.rename", (db) =>
    db.transaction((tx) => {
      const updated = tx.update(schema.projectTable)
        .set({ name, updatedAt: new Date() })
        .where(eq(schema.projectTable.id, id))
        .returning().all()
      if (updated.length === 0) throw new Error(`project ${id} not found`)
      return updated[0]
    }))
})

To create rows with explicit ids, use schema.newId(); @cadcamfun/db re-exports the schema module as the schema namespace, not as top-level names.

In 3D