Database and Storage

Full specification: 00-product-spec.md

Role of PostgreSQL

PostgreSQL is not the YZOS product, it is the local index that speeds up AI retrieval:

Purpose	Description
Hybrid retrieval	Upstream AI queries memory/knowledge over MCP; PostgreSQL returns FTS + pgvector results in milliseconds
Relational queries	Joins across activity, AI tool sessions, contacts, and voice segments
Rollup acceleration	Daily/weekly summaries are pre-aggregated so queries do not rescan raw data
Maintenance	Scheduled REINDEX / VACUUM / consistency reconciliation

The OKF Markdown files are the source of truth. If the database is lost or corrupted, it can be fully rebuilt by reconciling against ${paths.contexts}/*/knowledge/ and memory/.

Storage paths are configurable. The PostgreSQL data directory, OKF files, raw observation data, and model weights can each be set under the paths section of config.yaml (all default to subdirectories of ~/.yzos/). See “Storage Paths” below and 13-inference-and-dependencies.md section 3.7.

Decision

YZOS ships with embedded PostgreSQL 18.4 (postgresql_embedded = 0.20.4) plus pgvector 0.8.3. PGLite is tracked as a longer-term lightweight alternative.

See 13-inference-and-dependencies.md section 6 for the extension list, halfvec acceleration, Chinese-language full-text search, connection pooling, and version pinning.

Alternatives Considered

Option A: Embedded PostgreSQL (chosen)

// paths.database comes from config and is resolved to an absolute path at startup
let settings = SettingsBuilder::new()
    .data_dir(config.paths.database)  // defaults to ~/.yzos/pg, can be moved to external storage
    .port(0)
    .username("yzos")
    .password(&keychain_secret)
    .temporary(false)
    .build();

Dimension	Rating (out of 5)
pgvector + FTS	5, native support
Maintenance operations	5, full REINDEX / VACUUM / ANALYZE
Rust ecosystem	5, sqlx is mature
macOS support	5, aarch64 and x86_64
Footprint	3, roughly 35MB

Option B: PGLite

An in-process WASM build of Postgres. Small footprint, but its behavior under heavy desktop workloads and pgvector performance are unproven. Tracked as a lightweight v2 option.

Option C: SQLite

Ruled out. Multi-source joins, pgvector, concurrent writes, and maintenance operations (REINDEX) are not sufficient for this workload.

Dual-Write Architecture

flowchart TD
  OBS["Observe"] --> CRY["Crystallize"]
  CRY --> OKF["OKF Writer"]
  OKF --> FILES["OKF Markdown files (source of truth)"]
  FILES --> IDX["Indexer"]
  IDX --> PG[("PostgreSQL index")]
  FW["File Watcher (manual edits / kb_reorganize)"] -->|"re-index"| IDX
  REC["Cron reconcile"] -->|"reconcile"| FILES
  REC -->|"reconcile"| PG

Indexing Strategy

Write-time indexing (real time)

Triage crystallizes an event into an OKF file, the indexer chunks it, generates embeddings, and upserts into the chunks table.

Scheduled maintenance (cron)

Job	Frequency	Operation
reindex	Weekly	REINDEX INDEX CONCURRENTLY on HNSW and GIN indexes
vacuum	Daily	VACUUM ANALYZE on high-traffic tables
embedding_refresh	Weekly	Regenerate embeddings after a model upgrade
reconcile	Daily	Reconcile OKF hashes against PostgreSQL, rebuild missing index entries
rollup_daily	Daily at 02:00	Pre-aggregate the daily summary
rollup_weekly	Mondays at 03:00	Pre-aggregate the weekly summary

Retrieval Path

The upstream AI calls an MCP tool, PostgreSQL performs a hybrid search (RRF fusion of vector and full-text search), and results are returned as chunks plus their OKF file paths.

PostgreSQL Configuration

-- Required
CREATE EXTENSION IF NOT EXISTS vector;      -- pgvector 0.8.3
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Recommended
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;

-- Phase 2: Chinese-language full-text search (requires a precompiled pg_jieba)
-- CREATE EXTENSION IF NOT EXISTS pg_jieba;

max_connections = 20
shared_buffers = 256MB
work_mem = 16MB
maintenance_work_mem = 512MB   -- HNSW index builds need more headroom
hnsw.scan_mem_multiplier = 2
wal_level = minimal
fsync = on

PostgreSQL listens on 127.0.0.1 only; the password is stored in the macOS Keychain.

Storage Paths

YZOS splits index data, source-of-truth files, model weights, and raw observation data across independently configurable directories, so users can manage model size and disk pressure on their own terms.

Configuration

~/.yzos/config.yaml

paths:
  home: ~/.yzos
  database: /Volumes/Data/yzos-pg      # PostgreSQL data_dir (L3 index)
  contexts: ~/.yzos/contexts           # OKF source of truth (L1), usually kept on the system disk for sync
  observations: /Volumes/Data/yzos-obs # L4 screenshots/recordings (large)
  models: /Volumes/SSD/yzos-models     # GGUF / ONNX / sherpa
  voice: ${paths.observations}/../voice  # can be split out, defaults to ${home}/voice
  logs: ~/.yzos/logs
  runtime: ~/.yzos/run                 # socket, pid

storage:
  min_free_gb: 10                      # Preflight checks database and models volumes separately
  pg_max_size_gb: 8                    # optional soft cap, cron warns when exceeded

How paths relate to PostgreSQL

Path key	Data layer	Held in PostgreSQL	Typical size	Recommended volume
paths.contexts	L1 OKF	Indexed (chunk metadata + path)	Small, grows slowly	System disk / iCloud
paths.database	L3 index	PostgreSQL’s own files	Medium, grows with use	Fast disk (can be external)
paths.observations	L4 raw	Metadata row + external file_path reference	Large	High-capacity disk
paths.models	Models	None (manifest hash only)	~3.5 GB+	External SSD

PostgreSQL columns such as file_path, transcript_path, and audio_path store resolved absolute paths (or paths relative to paths.home, normalized on read). When migrating to a new machine:

1. keep paths.* identical, or
2. bulk UPDATE the path prefix after moving the data, or
3. skip syncing L4 entirely and rebuild OKF and PostgreSQL from the source of truth.

Implementation

yzos-core/src/config/paths.rs

pub struct ResolvedPaths {
    pub home: PathBuf,
    pub database: PathBuf,      // -> postgresql_embedded data_dir
    pub contexts: PathBuf,
    pub observations: PathBuf,
    pub models: PathBuf,
    pub models_llm: PathBuf,
    pub runtime: PathBuf,
}

impl ResolvedPaths {
    pub fn resolve(cfg: &PathsConfig) -> Result<Self>;
    pub fn persist_to_config_kv(pool: &PgPool) -> Result<()>;  // paths_resolved JSON
}

• During the Booting phase, the daemon resolves paths before creating or opening PostgreSQL (paths.database must exist and be writable).
• yzos paths verify checks directory permissions and storage.min_free_gb before Preflight runs.
• Changing paths.database does not migrate existing PostgreSQL files automatically; use yzos db migrate-data --to <new> (planned CLI command) or move the directory manually and restart.

Disk Planning Reference

Profile	models	database	observations	Notes
Default 16GB Mac	External 32GB SSD	System disk, ~2GB	System disk, capped at 20GB	Keeps models and screenshots off the main disk
Single machine, large capacity	Same disk, ${home}/models	Same disk	Same disk	Default layout
Two-machine sync	Local models per machine	Local pg per machine	Local per machine	Only contexts and overrides are synced

---

Secrets Management

• PostgreSQL password: macOS Keychain
• HTTP API secret: Keychain
• Inference API keys: Keychain
• config.json holds only non-sensitive configuration

Data Retention

Data	Default retention	Cleanup
Activity screenshots	360 days	Cron cleanup
Voice segments	90 days	Cron cleanup
AI tool session snapshots	180 days	Cron cleanup
OCR frames	Follows screenshots	Cascade delete
Memory / Knowledge (OKF)	Permanent	Maintenance jobs reorganize only
PostgreSQL index	Follows OKF	Rebuilt by reconcile
Rollup summaries	Permanent	-