Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
// Writers-Room DDL — folders, works, draft versions, and exercises. Extracted
// verbatim from ensureSchemaImpl() in server/lib/db.js (#2832); idempotent,
// runs on every boot.
export const writersRoomDdl = [
// Folder tree. Self-ref parent_id (soft, no FK — nested tree). sort_order +
// name promoted (the library renders the tree ordered by them). Mirrors
// init-db.sql.
`CREATE TABLE IF NOT EXISTS writers_room_folders (
id TEXT PRIMARY KEY,
parent_id TEXT,
name TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
data JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ
)`,
`CREATE INDEX IF NOT EXISTS idx_wr_folders_parent ON writers_room_folders (parent_id, sort_order)`,
// Idempotent upgrade for installs predating folder federation (#1645): without
// the soft-delete columns an old install boots the new code and hard-DELETEs a
// folder (no tombstone) — peers never learn it was removed and resurrect it.
`ALTER TABLE writers_room_folders ADD COLUMN IF NOT EXISTS deleted BOOLEAN DEFAULT FALSE`,
`ALTER TABLE writers_room_folders ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ`,
// Work manifests. The drafts[] array moves OUT of `data` into
// writers_room_draft_versions (the one decomposition — draft versions are a
// genuine 1-to-many the library + Phase-5 staleness analysis query). imageStyle
// / liveMode / usage counters stay in `data`. `folder_id`, `title`, `kind`,
// `status`, the promote/bridge links, and `active_draft_version_id` are
// promoted for the library list + the resolver (#1018) + the bridge CTAs.
// SOFT-DELETE added here (`deleted`/`deleted_at`): the file backend hard-deletes
// via rm -rf; the DB backend aligns with the other stores (import sets
// deleted = FALSE for all existing works). Soft ref everywhere — no FK.
// Mirrors init-db.sql.
`CREATE TABLE IF NOT EXISTS writers_room_works (
id TEXT PRIMARY KEY,
folder_id TEXT,
title TEXT NOT NULL,
kind VARCHAR(32),
status VARCHAR(32),
active_draft_version_id TEXT,
pipeline_series_id TEXT,
pipeline_issue_id TEXT,
cd_project_id TEXT,
media_collection_id TEXT,
data JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ
)`,
`CREATE INDEX IF NOT EXISTS idx_wr_works_folder ON writers_room_works (folder_id) WHERE deleted = FALSE`,
`CREATE INDEX IF NOT EXISTS idx_wr_works_series ON writers_room_works (pipeline_series_id) WHERE pipeline_series_id IS NOT NULL`,
// Draft-version metadata index (file-primary bodies). The .md body stays on
// disk at data/writers-room/works/<workId>/drafts/<draftId>.md; this row is
// the queryable index over it: `content_file` (relative path), `content_hash`
// (sha256 for staleness), `word_count`, `segment_index` (outline), version
// lineage. asset-file-db-indexed pattern applied to prose. Mirrors init-db.sql.
`CREATE TABLE IF NOT EXISTS writers_room_draft_versions (
id TEXT PRIMARY KEY,
work_id TEXT NOT NULL,
label TEXT,
content_file TEXT NOT NULL,
content_hash TEXT,
word_count INTEGER DEFAULT 0,
segment_index JSONB NOT NULL DEFAULT '[]'::jsonb,
created_from_version_id TEXT,
data JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ DEFAULT NOW()
)`,
`CREATE INDEX IF NOT EXISTS idx_wr_drafts_work ON writers_room_draft_versions (work_id, created_at)`,
// Exercise sessions (sprint timer). Monolithic exercises.json → flat table.
// `work_id`, `status`, started_at promoted for the per-work list (ordered by
// started_at DESC). prompt/durations/word counts/appendedText stay in `data`.
// Mirrors init-db.sql.
`CREATE TABLE IF NOT EXISTS writers_room_exercises (
id TEXT PRIMARY KEY,
work_id TEXT,
status VARCHAR(16),
data JSONB NOT NULL DEFAULT '{}'::jsonb,
started_at TIMESTAMPTZ,
finished_at TIMESTAMPTZ,
deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ
)`,
`CREATE INDEX IF NOT EXISTS idx_wr_exercises_work ON writers_room_exercises (work_id, started_at DESC)`,
// Idempotent upgrade for installs predating exercise federation (#1645). The
// tombstone columns let a peer's deletion propagate (LWW never propagates a
// hard delete); no current path deletes an exercise but a future peer might.
`ALTER TABLE writers_room_exercises ADD COLUMN IF NOT EXISTS deleted BOOLEAN DEFAULT FALSE`,
`ALTER TABLE writers_room_exercises ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ`,
];