Skip to content

STORAGE.md

Latest commit

 

History

History
216 lines (141 loc) · 51 KB

STORAGE.md

File metadata and controls

216 lines (141 loc) · 51 KB
Raw

Storage Classification Contract

PortOS stores data in two places: PostgreSQL (app-native relational records, search/vector indexes, sync cursors, lineage) and the filesystem under ./data/ (large binary assets, externally-editable prose, model weights, transient queues, and explicitly file-sync-oriented domains).

PostgreSQL is a required install/runtime dependency (see Backup & Restore and scripts/setup-db.js). Files remain first-class for the things a relational DB is bad at. This document is the contract for deciding which home a given domain belongs in — and the checklist a reviewer should apply before any new feature defaults to "just write another data/*.json."

For the full domain-by-domain inventory (every current table and data/ store, with Postgres-fit notes), see the plan doc: docs/plans/2026-06-06-create-postgres-storage-inventory.md. This page covers the contract and decision rules, not the exhaustive list.

The Four Storage Classes

Class Bytes live Searchable metadata Use when PortOS examples
db-primary PostgreSQL PostgreSQL App-native relational records: relationships, indexes, status, lineage, sync cursors, tombstones catalog_ingredients, catalog_ingredient_relations, memories; target: universes, series, issues, Creative Director, media metadata
file-primary Filesystem Filesystem (DB may index) The record IS an external file, or it must survive in a file-sync workflow (iCloud, Git, hand-editing) Writers Room draft .md bodies, MortalLoom / Health / Meatspace iCloud stores
asset-file-db-indexed Filesystem PostgreSQL Large binary payloads whose metadata must be queryable/searchable Generated images/videos/audio + DB asset rows referencing them via asset_key / media_key
ephemeral-file Filesystem None (or DB job ref only) Transient/regenerable runtime state — queues, uploads, caches data/uploads/*, runtime media job queue, browser profile/cache

The one rule that ties them together: the DB points to files; it does not absorb the bytes. Any file asset referenced from the DB gets a stable asset_key / media_key row plus integrity metadata. Bytes never go into a column.

db-primary — app-native relational records

Definition. Records that PortOS itself authors and relates: they have foreign keys, statuses, audit trails, search/vector indexes, and federated sync cursors/tombstones. The DB is the source of truth; there is no meaningful file representation of the record.

When to use. The record participates in relationships (series.universeId, issue.seriesId, catalog refs), needs cross-record queries ("everything related to this universe"), needs full-text or vector search, or needs per-table sequence cursors for peer sync.

Where it lives. PostgreSQL via server/lib/db.js + server/scripts/init-db.sql. Service modules own the storage adapter (e.g. server/services/catalogDB.js, server/services/memoryDB.js).

Examples.

  • catalog_ingredients — typed creative records with JSONB payload, tags, embeddings, generated search_tsv, soft delete, sync sequence.
  • catalog_ingredient_relations — directed ingredient→ingredient graph edges (the strongest argument for Postgres as the catalog graph store).
  • memories / memory_links — long-term memory + pgvector similarity.
  • post_runs / post_attempts — normalized MeatSpace POST test, benchmark, and training history. A run owns its planned composition and lifecycle timestamps; attempts carry queryable module/drill, difficulty/config version, correctness/score, latency/completion, hint/confidence, input mode, and scorer provenance, with the compatibility payload retained in JSONB. The complete attempt set is replaced in one transaction and stable client ids make retries idempotent. Migrated from data/meatspace/post-sessions.json and post-training-log.json by server/scripts/migratePostRunsToDB.js; the sources are parked as .imported recovery copies. Intentionally machine-local — never federated because cognitive-performance history is personal activity data. Adapter: server/services/postRunStore.js (legacy JSON only under the dev/test file escape hatch).
  • user_action_events — the operator-action ledger (#5594, epic #5593): one row per action the HUMAN took in PortOS (queued a CoS task, edited/deleted/approved/force-spawned one, rated an agent run, hit Run Now on a scheduled task, saved settings; phase 3 / #5596 also records event-only creative/Brain pointers, instance-feature toggles, and CoS schedule updates that skip PUT /api/settings). The leftover-branch idle detector is a consumer of this ledger (plus live git state), not a store of its own. db-primary because the value is in querying it — by type, actor, target, and time window — which is exactly what a JSONL append log cannot do. Columns carry the queryable axes (type/actor/happened_at/target/success) with structured detail in payload JSONB and the hook site in source JSONB; a unique (type, dedupe_key) index plus ON CONFLICT DO NOTHING makes a retried request idempotent. Bounded inline after each insert by BOTH a 20,000-row cap and a 90-day age cap — no cron. Credential-shaped payload keys are dropped at write time and their paths listed under payload.redactedKeys. Intentionally machine-local — never federated: it records what one operator did on one machine, and PII must not ride the federation layer (ADR privacy records machine-local); guarded in server/services/sharing/peerSync.test.js. Deliberately NOT in auditedTables — auditing an audit log doubles every row, same rationale as post_runs. Adapter: server/services/userActions.js (file backend only under the dev/test escape hatch).
  • creative_director_projects — Creative Director project/treatment/scene/run state, one row per project (id/status/timestamps as columns, the full record in data JSONB). Migrated from the monolithic data/creative-director-projects.json in Phase 3 (#997); CD is local-only, so the row carries no sync cursor/tombstone. Adapter: server/services/creativeDirector/projectsDB.js.
  • catalog_user_types — user-defined ingredient types (the registry that defines catalog row semantics), one row per type (id PK, the definition in data JSONB, updated_at/deleted_at mirroring the federation LWW clock + tombstone). Migrated from the data/settings.json catalogUserTypes slice in Phase 4 lead-in (#1001) so type evolution versions/syncs alongside the catalog data it governs. Federates via the catalog sync catalogTypes envelope block (wire shape unchanged by the move). Adapter: server/services/catalogUserTypes/db.js, dispatched via store.js.
  • universes / universe_runs — Universe Builder records (canon bibles, categories, composite sheets, locks, influences, and portable character production packages) one row per universe with the full sanitized record in data JSONB and name/schema_version/ephemeral/updated_at/deleted/deleted_at mirrored into columns; render-run history one row per run (local-only, capped 200, never federated). Character production packages carry only versioned voice direction and approved managed-image roles; local profiles, recordings, provider ids, and training artifacts are excluded from the federated wire. Migrated from data/universes/{id}/index.json (collectionStore) in Phase 3 Create slice 1 (#1014). NO sync_sequence — universes federate via the EXISTING dataSync snapshot/push model (LWW on the body's updatedAt), so the storage swap is invisible to peers (no schema-version bump). The store bumps an in-process mutation epoch on every write that dataSync folds into its checksum fingerprint, since a DB edit no longer changes the data/universes/ directory the fingerprint used to watch. universe_runs is intentionally never federated — a regenerable render cache under a 200-row global cap that two producers would mutually evict, while the durable universe record already syncs (ADR tribe + universe-runs local, #1724). Adapter: server/services/universeBuilder/db.js, dispatched via store.js.
  • voice_profiles / voice_profile_renders — machine-local DB-primary records for approved (universeId, characterId) bindings and the latest rendered dialogue line per (issueId, lineId). They store the promoted Kokoro/Piper preset, profile revision, route availability, benchmark provenance, and reproducible dialogue delivery details (engine/model revision, timing, controls, and mastering). The portable Universe character and federated pipeline issue keep only portable voice data and audio filenames; they never store a local profile id. Rendered benchmark WAVs, safe-basename source-asset metadata, and future local engine artifacts live under data/voice-profiles/<profileId>/. Both the PostgreSQL dump and that managed directory are included in normal backup, while peer sync intentionally carries neither. Adapter: server/services/voice/profiles.js.
  • tribe_people / tribe_touchpoints / tribe_memory_links — the Tribe relationship/CRM graph (people + their care cadence, contact touchpoints, and cross-links into brain memories). Intentionally machine-local — never federated (ADR tribe + universe-runs local, #1724): it is relationship-graph data, mirroring the deliberate "memory_links are instance-local" boundary in memorySync.js (memory nodes federate, the link graph does not), and is coupled to machine-local domains — tribe_memory_links extends the non-federated memory_links layer and tribe_touchpoints carry per-machine calendar-account refs. NO sync_sequence, no peer-sync record kind, no dataSync category. Adapter: server/services/tribe.js.
  • creative_commissions — Creative Commissions (Autonomous Creation Engine, #2657/#2686): standing recurring creative briefs that fire on a cron cadence and drive the Creative Director directive pipeline unattended. One row per commission, the full sanitized record in data JSONB with name/enabled/created_at/updated_at mirrored into columns for the scheduler's "arm every enabled commission" query. The brief/identity federates as creativeCommission so synced feedback can attach to the same commission; schedule, runs, assignment, enabled, and feedback view stay machine-local (the per-reaction commissionFeedback records federate separately). The opt-in Digital Twin music-taste configuration is bounded brief metadata; raw taste sources and per-run recipes never cross the wire. The file backend is the NODE_ENV=test/MEMORY_BACKEND=file escape hatch only. Adapter: server/services/creativeCommissions/db.js, selected pg-vs-file by server/services/creativeCommissions/store.js (file backend is the NODE_ENV=test/MEMORY_BACKEND=file escape hatch only).
  • games — Game studio workspaces (#3177): one row per managed-app asset plan, with the full reusable sprite/music binding set, current compiled-manifest pointer, compile history, and user-requested AI feedback history in data JSONB; app_id/name/updated_at are mirrored for list and relationship queries. The record is machine-local because managed-app registration, sprite atlases, and music-library bytes are machine-local; there is no peer-sync cursor or tombstone, and deletes are hard deletes. Compiled manifests are immutable, SHA-256-addressed artifacts under data/games/{id}/manifests/; their pointers and hashes live in the DB record. Adapter: server/services/games/db.js, selected pg-vs-collectionStore by server/services/games/store.js (collectionStore is test/unsupported file escape hatch only).
  • fableloom_stories — FableLoom branching narratives: one row per loom (a branching-narrative story), with episodes, scene-node graphs, and intent transitions in data JSONB; name/universe_id/series_id/updated_at are mirrored for list and relationship queries (both refs are soft — no FK). db-primary because looms relate to universes and series and the index queries by recency. Federates through the opt-in per-record fableLoom category: whole-record LWW merges carry soft-delete tombstones, conflict-journal recovery, and hashed manifests for scene images/videos; there is no snapshot cursor or sync_sequence. Adapter: server/services/fableLoom/db.js, selected pg-vs-collectionStore by server/services/fableLoom/store.js (collectionStore is the test/unsupported file escape hatch only). Feature doc: FableLoom.
  • threejs_models — generated procedural 3D-model workspaces: one row per model with gallery-image lineage, provider/model attribution, generation/refinement status, and the validated declarative scene spec in data JSONB. The referenced image bytes remain under data/images/; deterministic Three.js source is derived from the stored spec rather than persisted as a second mutable artifact. The table is local-only in this first slice, with soft-delete columns retained so federation can be added without a record-shape migration.
  • privacy_subjects / privacy_vault_records / privacy_consents / privacy_orgs / privacy_org_holdings / privacy_change_events / privacy_brokers / privacy_broker_cases — the Privacy Center (epic #2138): the household subjects the suite works on behalf of (self plus consenting partners/children/parents — every other table carries a subject_id FK defaulted to the seeded self row, #3658), the encrypted PII vault, the trusted-organization registry with per-field holdings, the change-of-address inventory, and the data-broker opt-out ledger. Relational by nature (org ↔ holdings ↔ vault records ↔ change events ↔ broker cases), which is why they are db-primary. Vault values are AES-256-GCM ciphertext (v1:<iv>:<tag>:<ct>, key from PRIVACY_VAULT_KEY) — the DB holds bytes of ciphertext, never plaintext PII, and plaintext never appears in logs (server/lib/vaultCrypto.js). Intentionally machine-local — never federated, and this is a product guarantee, not a deferred feature (ADR privacy records machine-local, #2148). NO sync_sequence, NO peer-sync record kind, NO dataSync category, NO PORTOS_SCHEMA_VERSIONS entry, and no deleted/deleted_at tombstones — deletes are hard deletes, mirroring tribe. The reasoning is the same class as the Tribe graph but stronger: the peer-sync pull path (GET /api/peer-sync/record) carries no peer identity, masked_value is plaintext by design so even ciphertext-only sync would leak a PII fingerprint, and a shared PRIVACY_VAULT_KEY would widen the at-rest blast radius to every peer's .env. A second machine gets the vault by restoring a backup and copying the key by hand — a deliberate act, not continuous replication. Enforced by server/services/sharing/privacyNeverFederates.test.js. Adapters: server/services/privacySubjects.js, privacyVault.js, privacyOrgs.js, privacyChanges.js, privacyBrokers.js, privacyOptOut.js.

Postgres-First target. Pipeline series/issues, Story Builder sessions, and searchable media metadata are still db-primary targets — they currently live in data/ JSON but carry relationships and status that belong in the DB. The schema for the Create domains is designed in docs/plans/2026-06-07-create-relational-schema-design.md (#999), with implementation tracked as #1014–#1018. (Creative Director project/scene/run state moved to Postgres in Phase 3 / #997; catalog user-defined types moved in Phase 4 lead-in / #1001; universes moved in Phase 3 Create slice 1 / #1014 — see the universes entry above. Pipeline series/issues #1015, Story Builder #1016, Writers Room #1017, and the catalog ref resolver #1018 are the remaining slices.)

file-primary — external-file or sync-sensitive records

Definition. The record either is an external file (long prose, a model, a repo) or must remain a file to preserve a sync/editing workflow PortOS does not own (iCloud, external editors, Git). A DB row may index it, but the file is authoritative for the body.

When to use. The payload is long externally-editable prose; the domain syncs through iCloud/file-sync outside PortOS; or forcing the record through the app DB would break an existing sync boundary.

Where it lives. Filesystem under ./data/ (or an OS-managed sync container). DB may hold metadata/index rows (hashes, word counts, segment indexes) but not the body.

Examples.

  • Writers Room draft bodies — data/writers-room/works/{workId}/drafts/{draftId}.md. Keep .md file-backed; store metadata/index rows in DB.
  • MortalLoom / Health / Meatspace health data — data/health, data/meatspace, MortalLoom iCloud store. Kept file-backed to preserve iCloud/file sync and avoid routing sensitive health records through the app DB before that boundary is designed.
  • App scaffolds / cloned repos / browser profiles — data/repos, data/browser-profile — inherently filesystem-oriented.
  • Eidoverse PortOS integration and world logs — data/eidoverse/portos-world.json stores the PortOS-owned private-world identity, versioned design selection, user overrides, deterministic asset-resolution lock (paths, fingerprints, size, and provenance only; never model bytes), migration report, and last-good reconciliation checkpoint; data/eidoverse/worlds stores the external runtime's append-only world files. Both are file-primary, included in filesystem backups, and intentionally machine-local — never federated by PortOS. PortOS selects the runtime's world-store location through .env.portos but does not edit the external checkout to build content. The separately licensed git checkouts and Eidoverse-owned asset library/cache live under the existing re-cloneable data/repos/ backup class.
  • Sprite animation-track definitions — data/sprites/animation-tracks.json (#3152). A small hand-editable authoring config: which animation types exist beyond the compiled-in walk (label, directionality, frame/fps bounds, prompt template, and the on-disk setKind strings). file-primary rather than db-primary because it is machine-local and inseparable from the on-disk sprite tree it describes — a row names the setKind an approved set under data/sprites/{id}/ already carries, so the two travel together or neither means anything — and because it has no cross-record queries, no relationships beyond kinds strings, and no sync cursor. Read synchronously and cached per process (server/services/sprites/animationTrackStore.js): server/lib/validation.js builds sprite Zod ranges from it at module load, so it must resolve without await. Seeded from data.reference/sprites/animation-tracks.json (migration 211), which is also the fallback read when no user copy exists yet. Backed up in full by the rsync snapshot.
  • Quota-burn plan — data/cos/quota-burn.json (#3390). The install's burn plan: master switch, poll interval, and per-provider-family windows + ordered job list. file-primary and intentionally machine-local — never federated: quota belongs to a particular machine and provider account, so a synced plan would have each peer spending against the other's window budget, and the plan's agent-prompt jobs name managed apps that only exist on this machine. No sync cursor, no tombstone. Its four companions are ephemeral-file — regenerable telemetry, all safe to delete: data/cos/quota-burn-dispatches.json (per-window dispatch counts, self-pruning at 30 days), data/cos/quota-burn-runs.json (capped run log), data/cos/quota-burn-inflight.json (entries a burn job has enqueued but whose renders have not completed, self-pruning at 6 hours — deleting it only risks re-queueing a render already in flight), and data/cos/quota-burn-denials.json (per-family blocks from an observed provider refusal, cleared by the next successful burn or a 5-hour TTL — deleting it only risks one dispatch into a still-exhausted window). Backed up with the rest of data/cos/; a restored plan simply re-applies on this machine. See Quota Burn.
  • YouTube brain ingests — data/brain/youtube/{videoId}.md (transcript), {videoId}.mp3 (optional audio), plus data/brain/youtube/index.json (the ingest index) and data/brain/youtube-ingest-settings.json. The transcript IS an external file: it is mirrored into the user's Obsidian vault, edited there, and syncs through iCloud — the same boundary that keeps the Daily Log file-backed. The index is intentionally machine-local — never federated: every field in it is a local filesystem path, an Obsidian vault id, or a local video-history id, so a peer's copy would be meaningless and would poison brain reconcile exactly the way the daily log's journal-obsidian-locations.json sidecar would. The playlist/video reference shelf at data/youtube/playlists.json follows the same local-only rule: it is a bounded cache of browser-scraped YouTube metadata and links, not a federated Brain record. The durable, federated record of "I consumed and kept this" is the brain links entry (db-primary via the brain store) plus a media.watch row in human_activity_events. No tombstone. Adapters: server/services/youtubeIngest.js and server/services/youtubePlaylists.js.
  • Spotify brain playlist shelf — data/spotify/playlists.json. file-primary, intentionally machine-local and never federated: it is a bounded cache of Spotify playlist and track metadata used as local reference material, while listening evidence remains in the federated Brain activity record. No sync cursor or tombstone. Adapter: server/services/spotifyPlaylists.js.
  • IdeaLoom lists — data/brain/idealoom-lists/{uuid}/index.json with a schema-stamped collection index holding the disabled-by-default local integration settings. Lists retain their ordered idea strings, prompt/title/category/status/help, timestamps, and importer-owned local sync metadata. Explicit exchange reads/writes only the configured vault's Idea Loom/ folder through the specialized server/services/idealoomObsidian.js parser/renderer; new notes use a date/title filename and imported note paths remain stable. Intentionally machine-local — never federated, reconciled, or memory-bridged: a vault id, note path, and content hash are meaningful only on the install that configured them. Native Brain ideas remain a separate federated collection. Exchange is base-hash reconciled: a note and a list that both changed since the stored hash report conflicted and neither is written, and a note deleted in the vault reports missing rather than being recreated (an iCloud note that is merely un-downloaded is unavailable, a separate outcome). Opt-in automatic export (autoSync, off by default, debounced by server/services/idealoomAutoSync.js) can only update an existing note — it never deletes, recreates, or resolves a conflict. Backed up in full with the rest of data/brain/; the vault notes themselves are the user's Obsidian data and are outside PortOS's snapshot. Adapters: server/services/idealoomLists.js (records), server/services/idealoomObsidian.js (exchange).
  • Local-model assessments — data/local-llm/assessments.json (#4539). Measured evidence for one installed local model per (backend, model): the fit verdict (fits/does-not-fit/incompatible/unknown), per-context throughput/TTFT samples, resident footprint, and the coarse hardware environment the measurement was taken in. file-primary — a flat, capped, single-JSON projection with no cross-record queries and no relationships; the newest measurement replaces the old one per model rather than accumulating history. Intentionally machine-local — never federated: an assessment is a claim about THIS box, so a peer inheriting a 128 GB machine's its verdict for its 8 GB laptop would be actively wrong. No sync cursor, no tombstone, no PORTOS_SCHEMA_VERSIONS entry. Backed up (a run costs the user minutes of local compute), and the environment record deliberately carries no hostname/username/path. Adapter: server/services/localModelAssessmentStore.js (durable store + environment capture; no path to a provider, so read-only consumers like the catalog fit badge can import it); the run lives in server/services/localModelAssessments.js and the scoring in server/lib/localModelAssessment.js. Each record's environment is re-compared against the live machine on read, so a reading taken before a RAM upgrade or backend update is flagged stale rather than silently trusted.
  • LoRA training datasets — data/lora-datasets/{id}/index.json + images/*.png (collectionStore). The record is inseparable from the image bytes it organizes, has no cross-record queries beyond a small characterId scan, and is machine-local like data/loras/ itself (training artifacts tied to this machine's GPU output — never federates, no sync cursor/tombstone). Backed up in full: uploads and hand-edited captions are not re-creatable. Training RUN records are db-primary (lora_training_runs); run artifacts (checkpoints/samples) live under data/training-runs/{runId}/ with checkpoints/cache excluded from backup.

asset-file-db-indexed — bytes on disk, metadata in DB

Definition. Large binary payloads (images, video, audio, model weights) stay on disk as bytes, while their searchable metadata — provenance, gen params, favorites, notes, lineage, collection membership — lives in PostgreSQL as asset rows that reference the file by a stable key.

When to use. You have generated or imported binary assets that the user needs to search, filter, favorite, or relate to other records, but the bytes themselves are large and have no business in a column.

Where it lives. Bytes under ./data/ (data/images/*, data/videos/*, data/audio/*, data/music/*, thumbnails). Metadata in DB asset rows keyed by asset_key / media_key, with integrity metadata (SHA-256 — see server/lib/assetHash.js). The DB row references the file; it never embeds the bytes.

Examples.

  • Generated images — data/images/* bytes + .metadata.json sidecars; indexed into the media_assets table (#1000) keyed image:<filename>. Sidecars remain authoritative; the DB row is a derived, queryable mirror. Adapter: server/services/mediaAssetIndex/.
  • Generated videos — data/videos/*, data/video-thumbnails/* bytes, tracked in data/video-history.json; indexed into media_assets keyed video:<jobId>. History file remains authoritative.
  • Game asset manifests — immutable data/games/{id}/manifests/game-assets-v{N}.json artifacts reference sprite atlas and music-library bytes by stable path + SHA-256; the games DB record owns the current pointer and history. Both halves are backed up: PostgreSQL by the required dump, manifests and referenced media by the rsync snapshot.
  • Media collections — many-to-many links over assets/universes/series/catalog media pointers (db-primary link tables) pointing at asset-file-db-indexed bytes. Still data/media-collections/* JSON today — a follow-up slice of #1000.

Media asset index (media_assets, #1000). One row per generated image/video: media_key (<kind>:<ref>) PK, kind/ref/created_at mirror columns for queries, the full metadata record in data JSONB. It is a derived index — the on-disk sidecars + video-history.json stay authoritative — reconciled from disk at boot (upsert every asset, prune rows whose file is gone) and kept warm by a generation-completed hook. Local-only (rebuilt from disk), so no sync cursor/tombstone. Adapter: server/services/mediaAssetIndex/{logic,db,index}.js.

Asset license provenance (#5638). Every finished image and video stamps data.provenance at finalize time: the renderer/model id, every LoRA applied, and each one's license string and source URL as known when the pixels were made. Unknown stays null (displayed as "unknown") — never a permissive default. A license re-read months later can differ from the one in force at render, so the stamp is written into the authoritative sidecar / video-history row (the derived media_assets.data JSONB mirrors it). LoRA installs persist license on the .metadata.json sidecar so it is available at render rather than re-fetched. Collection and export surfaces roll the distinct sources up into an Attribution & licenses section.

Standalone media-library federation (mediaLibrary, #1566). For full-sync peers, the standalone media-library bytes (generated images + sidecars, videos, pipeline audio, uploaded music) mirror across the pair — not just bytes referenced by a synced creative record. The sender advertises a library-level manifest at GET /api/peer-sync/library-manifest ({ schemaVersion, manifestHash, assets:[{kind,filename,sha256,sidecarSha256?}] }); the receiver's periodic sweep (syncMediaLibraryFromPeer, driven from initSharing) diffs it against local disk, receiver-pulls missing bytes through the SAME diffAssetManifestAgainstLocal + pullOneAsset machinery as the per-record path, then rebuilds the derived media_assets index. Video thumbnails are regenerated locally on video pull (not byte-federated); video-history.json metadata already union-merges via the videoHistory dataSync category; the generic data/history.jsonl action log is machine-local and never federated. Byte replication is gated to peer.fullSync and honors backup DEFAULT_EXCLUDES (a media dir excluded from backup isn't federated). Manifest envelope versioned by PORTOS_SCHEMA_VERSIONS.mediaLibrary (a non-record category — see NON_RECORD_SCHEMA_CATEGORIES); the receiver gently skips a sender ahead of its version.

Postgres-First target (remaining). data/history.jsonl (action log) and the durable portions of data/media-jobs.json (job history / lineage) are still file-backed — follow-up slices. Do not move generated image/video/audio bytes into PostgreSQL.

ephemeral-file — queues / uploads / transient state

Definition. Regenerable, short-lived runtime state. Losing it costs at most an in-flight job or a cache rebuild — never durable user data. It should never be the only home for anything the user expects to persist.

When to use. Upload staging, in-flight job queues, caches, and scratch state. If a record must survive a reinstall or be queryable across records, it is not ephemeral-file — promote it.

Where it lives. Filesystem under ./data/, frequently excluded from backups (see DEFAULT_EXCLUDES in server/services/backup.js). The DB may hold a durable job reference even when the staging bytes are ephemeral.

Examples.

  • Upload staging — data/uploads/*. Ephemeral; do not put in DB except as job references.
  • Media job queue — runtime queue state can stay file-backed short term, but job history and artifact lineage are db-primary and should move to the DB.
  • Browser CDP profile / downloads — data/browser-profile/, data/browser-downloads/ — cache, non-overridable backup excludes.
  • Brain parity audit results — data/brain_parity_reports.json (server/services/brainParity.js, #4519). The last record-level brain-parity report per peer, keyed by peer instanceId (the same key data/instances_sync_cursors.json uses). ephemeral-file because it is a point-in-time observation about two installs, fully regenerable by re-running the audit, and stale the moment either side syncs — the durable state it describes lives in the brain stores. Intentionally machine-local — never federated: it is this install's view of a peer, and each peer computes its own. No sync cursor, no tombstone, no migration (an absent file reads as "nothing audited yet"). Per-type record lists are capped at 25 ids with a truncated flag so a badly diverged install can't grow the file without bound.
  • CoS event ledgers — ordinary diagnostics use data/cos/run-events.jsonl + run-events.1.jsonl; persistent-mind trajectory uses the separate mind-events.jsonl + mind-events.1.jsonl pair (server/services/agentRunEventLog.js, #4540, #5082). Both are append-only, machine-local ephemeral-file replay aids with no federation cursor, tombstone, or PORTOS_SCHEMA_VERSIONS entry. The durable ordinary run record remains data/runs/{id}/metadata.json; mind rollups remain in their own bounded cache. Ordinary generations rotate at 5000 events and additionally expire after 30 days. Mind generations rotate at 10000 events without an age cutoff so a stopped mind keeps its unsummarized window, while high-volume mind chatter cannot evict ordinary run diagnostics. Migration 301 moves existing mind.* lines out of the shared files and stamps predecessor sequence provenance used to distinguish a legitimate timestamp jump from lost retained events. Payloads are redacted at append time, and both bounded pairs are backed up with the rest of data/cos/.
  • Remote-API metadata cache — data/cache/huggingface-repos.json (server/services/huggingFaceRepoCache.js). Hugging Face per-repo records (file sizes, native context window) backing the local-LLM catalog's quant pickers. ephemeral-file rather than db-primary because it is a pure projection of someone else's API with no queries, no relationships, and no sync cursor — and because it is machine-local by construction: it exists to spare THIS install's cold-start requests, so federating it would be pure noise. Long TTL (7 days) since published GGUF file sizes are immutable, but bounded because a repo can gain a new quant. Non-overridable backup exclude — regenerable on demand, and stale by restore time anyway. Purgeable from Data Manager (cache category).
  • Rapid Reader's Accelerando source cache — data/cache/accelerando.html (server/services/rapidReader.js). The official author-hosted HTML edition is fetched only when the user asks to load it, then retained locally for repeat and offline reads. ephemeral-file rather than db-primary: it is a machine-local copy of a separately licensed remote work, has no PortOS records or queries, and can be re-downloaded or purged without data loss. It is not bundled, federated, or backed up; Data Manager purges it with the cache category.
  • Peer AI-usage digests — data/peer-usage.json (server/services/peerUsage.js). One aggregate usage digest per FEDERATED instance, keyed by origin instanceId and replaced whole under an LWW capturedAt stamp. ephemeral-file: it is entirely derived, replicated state whose authority is each peer's own data/usage.json, so a corrupt or missing file self-heals on the next 60s sync cycle. It is deliberately NOT merged into local usage.json — summing peer counters into our own file would double-count on the very next round trip and corrupt this machine's history irreversibly. Federated by the default-ON usage snapshot category: no per-record sequence cursor (a digest is replaced whole under an LWW capturedAt), but it DOES carry tombstones (server/lib/tombstones.js, keyed on instanceId) — removing a peer must retire its digest everywhere, since our own snapshot forwards every digest we hold and a surviving peer would otherwise hand a deleted row straight back. Capped at 64 instances (oldest capturedAt evicted), and each arriving digest is rebuilt to the known wire shape rather than stored as it came. Backed up with the rest of data/ — harmless either way, since a restore is re-converged on the next cycle. See ADR AI usage metrics federate on by default.
  • Rapid Reader shelf — data/rapid-reader-library/{id}/index.json (collectionStore). Durable user-curated prose is file-primary: it is machine-local, never federated, and included in normal filesystem backups. It has no relationship graph or full-text search requirement that justifies Postgres.

Postgres-First Target Boundaries

The contract draws a single line:

  • PostgreSQL owns app-native records, relationships, indexes, sync cursors, tombstones, lineage, status, and searchable metadata.
  • Files own large binary payloads, long externally-editable prose bodies, model weights, temporary uploads, and iCloud-backed health/life stores.
  • File assets referenced from the DB get a stable asset_key / media_key row plus integrity metadata. The DB points to files; it does not absorb the bytes.

Defaulting a new Create feature to a fresh data/*.json file is the anti-pattern this contract exists to stop. Monolithic JSON in hot paths (media-jobs.json, video-history.json — and creative-director-projects.json before #997 moved it to Postgres) causes write contention and growth risk; string-id cross-references across separate JSON stores drift with no integrity check. New relational surfaces should be db-primary from the start.

Legacy migration-source cleanup

Each file→Postgres migrator parks its source aside (<domain>.imported, per-record index.json.imported / manifest.imported.json) instead of deleting it, as a one-release recovery copy. server/scripts/pruneImportedLegacyFiles.js (run at boot from server/index.js, after every store's file→DB warm, registered in the ledger by scripts/migrations/077-prune-imported-legacy-files.js) removes the .imported copies only when it has verified, by record identity, that every migrated record those artifacts hold is still present in the database. It reads the record ids straight off the parked artifacts — the parsed index.json.id of each per-record directory (the same id the migrator inserted, which can differ from the folder name), or the parsed JSON .id of the creative-director export, each writers-room manifest/folder/exercise, the universe config.runs[], and each manifest's drafts[] — and checks those exact ids exist via WHERE id = ANY(...). Identity rather than a row count, because a count can be satisfied by unrelated rows after a wipe+restore to a different record set (and the migrators' imported count comes from INSERT … ON CONFLICT DO NOTHING, so it can undercount). Anything that can't be verified withholds the whole domain's prune: a missing id, a present-but-unparseable artifact, an id-less record, or a domain whose migration is still pending (legacy source on disk, no marker). The prune deliberately does not touch the deeper *.bak-NNN monolith backups (the file→file split migrations 034–036) or the file-split backups (037 history, 059 media-collections): those predate the DB, can hold records the split migrator skipped, and carry soft-deleted records the live DB legitimately lacks, so they're neither identity-verifiable nor safe to auto-delete — they're left for manual cleanup. None of these artifacts are excluded from rsync backups: while a prune is blocked they are the only recovery source and pg_dump is capturing the incomplete DB, so a snapshot must keep them — once pruned from disk they leave subsequent snapshots naturally.

PostgreSQL is required — MEMORY_BACKEND=file is test-only

PortOS treats PostgreSQL as a mandatory install/runtime dependency for every install and every federated peer machine (decision: ADR — PostgreSQL as the Primary Datastore). Run it as either:

  • System (native) PostgreSQL on :5432 — PGMODE=native, or
  • Docker PostgreSQL on :5561 — PGMODE=docker (the default).

Provision either path with npm run setup:db (also run automatically by npm run setup and npm start). It auto-detects an already-healthy local PostgreSQL and uses native mode; otherwise it starts/initializes the Docker container, or — when Docker is unavailable — offers to bootstrap native PostgreSQL. See Setup path below.

MEMORY_BACKEND=file is a development/test-only escape hatch — NOT a deployment mode

The file backend (server/services/memory.js, JSON under ./data/) is unsupported for production and for federated peers. It exists only so the test suite (and ad-hoc local development) can boot without a database. It is not a fallback, a "lite" mode, or a way to run PortOS without Postgres:

  • It is reached only via the explicit MEMORY_BACKEND=file env var (set from PGMODE=file in .env, mapped by the launcher) or automatically under NODE_ENV=test. There is no menu choice for it (scripts/setup-db.js offers only Docker and Native), and npm run setup:db with PGMODE=file prints an "unsupported" notice and refuses to provision it.
  • When MEMORY_BACKEND is unset, PortOS requires a healthy database and does NOT silently fall back to file storage — an unreachable/unmigrated DB is an error condition. server/services/memoryBackend.js fails fast with an actionable message (run npm run setup:db) rather than serving a half-broken install. This no-silent-fallback behavior is intentional; do not "fix" it.

Why file storage cannot be a supported mode:

  • No creative-catalog / vector equivalent. The catalog graph, memory similarity, and hybrid search depend on PostgreSQL + pgvector (HNSW vector search fused with tsvector full-text). There is no file-backed implementation of these — a file-backed install would serve a half-broken app the moment a user touched the catalog, memory search, or any db-primary Create domain. As each Create domain migrates to Postgres (universes #1014, pipeline #1015, Story Builder #1016, Writers Room #1017, catalog refs #1018), its file path survives only under this dev/test escape hatch.
  • Federation assumes Postgres. Cross-machine sync (snapshot/push + last-writer-wins) and the db-primary sequence cursors/tombstones are designed around the database. A file-backed peer is not a supported member of a federation.
  • Backup assumes Postgres. The backup/restore contract treats the pg_dump logical dump as required system state (see Backup & Restore) — a file-backed install has no dump to capture or verify.

The escape hatch is guarded from bitrot by the test suite (tests boot with NODE_ENV=test and exercise the file backend), so the path stays runnable — but "the tests use it" is not an argument that it is a deployment option. It isn't.

Setup path (npm run setup:db)

npm run setup:db → scripts/setup-db.js is the single command that makes PostgreSQL ready, and is wired into npm run setup and npm start so a normal install never has to think about it. Its happy path:

  1. Already healthy? If the configured role can authenticate to the configured database and the memories table from server/scripts/init-db.sql exists, it reports ready and exits (fast path — no re-provisioning on every npm start).
  2. PGMODE=docker (default): starts the pgvector/pgvector:pg17 container (docker-compose.yml), waits for it to accept connections and finish applying the init schema, then reports ready on :5561. If Docker is missing or not running, it prints platform-specific install/start hints and — if a healthy native PostgreSQL is already present — switches to native automatically.
  3. PGMODE=native: runs scripts/db.sh setup-native (idempotent: brew install, role, db, extensions, schema) and verifies at the domain level (role can auth + schema present) before reporting ready on :5432.
  4. Failure is non-zero exit. A started-but-unresponsive container, a failed native bootstrap, or a non-interactive context with no usable DB exits non-zero with an actionable message — so the &&-chained npm start halts here instead of crash-looping under PM2 against an unready database.

PGPASSWORD/PGUSER/PGDATABASE/PGPORT are resolved from process.env first, then .env, then the backward-compatible defaults (portos/portos/portos/5432). The default portos password is an intentional local-development fallback (see the Distribution model note in AGENTS.md); production deployments override it via PGPASSWORD.

Boot schema upgrades & lock windows

ensureSchema() in server/lib/db.js applies idempotent schema upgrades on every boot (CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS). Every index it creates — including the HNSW vector index and the GIN full-text index on catalog_scraps — is a plain, non-CONCURRENT build.

There is one Postgres per install, shared by every process that opens it (the server, portos-cos, and every CoS agent worktree), so two processes calling ensureSchema() at once is routine — most visibly when an update restart overlaps an outgoing server still shutting down with the incoming one. The DDL block is idempotent within one session but not atomic across sessions: the per-table audit triggers are installed as a DROP TRIGGER IF EXISTS / CREATE TRIGGER pair, and two interleaved sessions can both pass the DROP before either reaches CREATE, so the second CREATE throws "already exists" (#5977). ensureSchemaImpl() takes a dedicated client and holds a session-level pg_advisory_lock around the entire DDL block (upgrades + catalog) to serialize this cluster-wide; the lock is released in finally on both the success and throw path, and a dropped connection (a hard kill mid-boot) releases it automatically so a later boot is never blocked by a stale lock.

The first time a given index materializes on a table that already holds many rows (an existing install upgrading into a newly-added index), Postgres takes a SHARE lock that blocks writes (INSERT/UPDATE/DELETE) to that table until the build completes. So an existing install can see a one-time write stall at boot proportional to the table's row count — HNSW builds are the slowest. Fresh installs never see this: they build every index on an empty table, so the lock is effectively instant.

This is left as-is deliberately rather than switched to CREATE INDEX CONCURRENTLY, because CONCURRENTLY cannot run inside a transaction block, needs its own retry/cleanup path (a failed concurrent build leaves an INVALID index that must be dropped by hand), and roughly doubles build time — too fragile to run unattended on every boot for a stall that only bites large-table upgrades.

If a future index must land on a table known to already carry a large row count on existing installs, note that the standard db-migration runner (server/scripts/run-db-migrations.js) wraps every migration in a withTransaction() block — so CREATE INDEX CONCURRENTLY cannot run there either. It would have to be issued from a dedicated non-transactional path (a standalone maintenance script or manual step run outside any transaction), not from ensureSchema() or a standard db-migration.

Catalog generated-column rewrite

Catalog schema v2 expanded catalog_ingredients.search_tsv, a stored generated column, to index physicalDescription. PostgreSQL cannot alter a stored generation expression in place, so the compatibility path drops and re-adds the column on an upgrading v1 install. Adding it back computes the value for every existing ingredient and holds an ACCESS EXCLUSIVE lock on catalog_ingredients for the rewrite; the following GIN index recreation adds its own write-blocking build window. Reads and writes that reach this table from another still-running PortOS process wait behind those locks.

PortOS deliberately accepts this one-time, boot-time maintenance window instead of carrying a second shadow column, trigger, batched-backfill checkpoint, and recovery protocol indefinitely. The expression check in both schema sources makes the path bounded by state: fresh installs add the column to an empty table, v2 installs skip the rewrite, and only a pre-v2 catalog pays the row-proportional cost. ensureSchema() completes before the new server reports ready, so it never exposes a partially upgraded catalog.

For an install with an unusually large pre-v2 catalog, treat the update as planned maintenance:

  1. Take a normal PortOS backup before updating.
  2. Stop other PortOS processes that can use the same PostgreSQL database; federated peers use their own databases and upgrade independently.
  3. Run the normal update during a low-use window and allow startup to finish without interruption. There is no safe universal duration estimate: row count, payload size, disk speed, and PostgreSQL settings all affect the rewrite.
  4. Wait for the Database schema upgrades applied startup log before resuming use. If startup is interrupted, rerun the normal startup; the expression gate and IF NOT EXISTS statements safely converge on the v2 shape.

Adding a new data store? Answer these

Apply this checklist to every new feature that persists data, and require it in PR review. A new data/*.json store must justify itself against these questions — the default for app-native records is PostgreSQL.

  • Which class is it? Tag the domain db-primary, file-primary, asset-file-db-indexed, or ephemeral-file. If you cannot pick one cleanly, the design is probably mixing concerns.
  • Does it relate to other records? FKs, cross-record queries, "show everything related to X", graph edges → db-primary. Do not encode relationships as string ids across separate JSON files (they drift with no integrity check).
  • Does it need search? Full-text or vector search → PostgreSQL (search_tsv / pgvector), not an app-level scan over JSON files.
  • If you chose a new data/*.json, why not the DB? Acceptable reasons: large binary bytes (asset-file-db-indexed — index the metadata, keep bytes on disk), long externally-editable prose, an iCloud/file-sync workflow PortOS does not own, or genuinely transient runtime state (ephemeral-file). "It was faster to write a JSON file" is not acceptable for app-native relational records.
  • Bytes vs. pointer. If binary assets are involved, confirm bytes stay on disk and the DB holds only an asset_key / media_key row + integrity metadata. Never store bytes in a column.
  • Federation. If the record syncs to peers, does it have a per-table/per-record sequence cursor and tombstone strategy? (See server/lib/schemaVersions.js, server/lib/syncWire.js.) Cross-machine sync is first-class — see the Distribution model in AGENTS.md.
  • Migration. On-disk/DB format changes need a migration in scripts/migrations/ (applied-list tracked per install in data/migrations.applied.json) and seed files in data.reference/. Other installs and other federated machines upgrade independently.
  • Backup coverage. Will the new store be captured by backup? db-primary is covered by the Postgres dump; file-primary / asset-file-db-indexed by the rsync snapshot; ephemeral-file is correctly excluded (DEFAULT_EXCLUDES). Confirm the store lands in the right bucket. See Backup & Restore.

See also