PostgreSQL

PostgreSQL is the authoritative data store for every domain in chris-os: messaging, health, calendar, contacts, financial, activity, config, memory, and more. All pipelines write here; all analytics read from here.

Migration level: 302 Total tables: 210 across 5 schemas Total rows: ~11.5M+ (active tables) Indexes: 660 Functions: ~85 application-defined

---

Schema Overview

Schema	Tables	Purpose
public	183	All production data: messages, health, calendar, activity, config, dispatch
memory	17	Memory server: embeddings, entities, knowledge graph
brewing	13	Homebrew recipes and sessions (BrewersFriend sync)
ha	2	Home Assistant state change events
therapy	4	Tier 1 Sacred — isolated, empty, therapy_app role only

---

Table Groups

Messaging

Table	Rows	Notes
messages	450K+	Primary iMessage/email/SMS table. Columns: source, direction (‘inbound’/‘outbound’), received_at TIMESTAMPTZ, body, contact_id, embedding vector, search_vector tsvector.
social_messages	150	Facebook/Instagram DMs. UUID PK.
discord_messages	870	Discord messages. UUID PK. Columns include guild_id, channel_id, author_id, is_bot, director_escalated.
dialogue	107,555	GLaDOS session dialogue log.
imessage_conversations	2,444	iMessage conversation metadata.

Canonical cross-platform view: all_messages — see Views section below.

Health

Table	Rows	Notes
health_records	9.5M+	Apple Health export. TEXT PK (hash-based). Columns: record_type, source_name, value, start_date TIMESTAMPTZ, record_hash (unique).
peak_state_scores	583	Daily scores. Columns: overall_score, physical_score, social_score, scheduling_score, environment_score, financial_score, indicators JSONB.
workouts	0	Workout sessions. Columns include workout_type, duration_minutes, started_at TIMESTAMPTZ, workout_hash (unique).
time_blocks	301	Time Portrait blocks. Columns: start_time, end_time, category, subcategory, source, confidence, corroboration_score.

Calendar and Location

Table	Rows	Notes
calendar_events	6,850	Google Calendar. TEXT PK. Columns: source, external_id, start_time TIMESTAMPTZ, attendees JSONB, embedding vector. Unique on (source, external_id).
weather_data	53	Daily weather records.
activity_timeline	683,052	Cross-source activity feed (materialized).

Contacts and Relationships

Table	Rows	Notes
contacts	5,400+	Master contact record. TEXT PK. Columns: name, identifiers[], phone_numbers[], email_addresses[], relationship, priority, discord_id.
contact_cadence_stats	4,922	Computed communication cadence per contact.
relationship_scores	4,922	Computed relationship strength scores.

Activity and Signals

Table	Rows	Notes
activity_timeline	683,052	Materialized cross-source activity feed.
web_activity	338,983	Browser activity.
signals	35,779	Raw signal events.
github_commits	816	GitHub commit history mirror.
github_issues	588	GitHub issues mirror.
photo_metadata	41,964	Photo library metadata.

Documents and Content

Table	Rows	Notes
documents	484+	Master document store. UUID PK. Columns: title, slug, content, format, category, tags[], version, parent_id, metadata JSONB, status, superseded_by. Trigger archives to document_versions on UPDATE.
claim_registry	35	Validated factual claims with confidence, evidence_query, superseded_by chain. UUID PK.
discovery_questions	0	Cornerstone Discovery Engine interview questions.
cornerstones	0	Cornerstone identity entries.

Config and Manifest

Table	Rows	Notes
config_registry_item	7	The Manifest: items to track. Columns: component, host, property, expected_expression, stale_after INTERVAL, tracking_mode (‘asserted’/‘observed’).
config_registry_observation	118,440	Observed values per config item.
config_registry_event_inbox	219,285	Inbox for incoming config events.
schema_migrations	34	Migration tracking. Columns: version TEXT PK, applied_at TIMESTAMPTZ, checksum, description.
reportcard_snapshots	153	Daily operational health snapshots.

Dispatch and Director

Table	Rows	Notes
dispatch_sessions	175	Agent dispatch session records.
dispatch_agents	4,195	Individual agent records.
dispatch_activity	10,533	Activity log per agent.
ops_escalations	105	Active and historical escalations.
session_lifecycle_events	914	Preflight/postflight lifecycle events.
morning_council_snapshots	8	Morning Council briefing snapshots.

Infrastructure

Table	Rows	Notes
unifi_clients	86	UniFi network clients.
backup_log	140	R2 offsite backup run log.
pipeline_runs	3,098	Pipeline execution history.
sync_log	2,159	General sync operation log.

Brewing (brewing schema)

Table	Rows	Notes
recipes	103	Homebrew recipes synced from BrewersFriend.
sessions	80	Brewing sessions.

Home Assistant (ha schema)

Table	Rows	Notes
ha.events	4,118	HA state change events. Indexed on (entity_id, event_time DESC).

Memory Server (memory schema)

Table	Rows	Notes
memories	129,738	Core memory store. HNSW index on binary_quantize(embedding)::bit(4096) using hamming distance.
entities	85	Named entities extracted from memories.
entity_relations	6	Relations between entities.
bridge_sync_log	170	Sync state between source tables and the memory server.
synthesis_artifacts	1	Synthesized knowledge artifacts.

Therapy (therapy schema)

All 4 tables are empty and isolated. Accessible only to the dedicated therapy role. No remote or app role access. No cross-schema views include this schema.

Table
therapy.sessions
therapy.session_themes
therapy.action_items
therapy.medication_changes

Tier 1 Sacred

The therapy schema is a hard isolation boundary. No other database role can access it. It does not appear in all_messages or any cross-schema views. Application code that queries therapy data must use the dedicated therapy role and must never be run from a Claude session (all session content is sent to Anthropic).

---

Key Views

all_messages — The Canonical Messaging View

The most important view in the schema. A UNION of messages, social_messages (non-Discord), and discord_messages. All cross-platform analytics must use this view — never query the source tables directly.

Provides: id, contact_id, direction (‘inbound’/‘outbound’), timestamp, content, platform, source_table, source_id. Filters to rows where contact_id IS NOT NULL (linked messages only). Discord direction is resolved via system state for Chris’s own user ID.

Caution

Never run cross-platform message analytics against the raw tables. Use all_messages. Querying messages alone excludes ~1,000 rows from Discord and social platforms.

Other Notable Views

View	Purpose
config_registry_current_v1	Current Manifest state per config item. Four states: in_sync, drift, stale, unknown.
config_registry_prompt_snapshot_v1	Manifest snapshot formatted for LLM prompt injection.
calendar_events_active	Non-excluded calendar events only.
health_records_active	Non-excluded health records only.
contact_communication_overview	Aggregated communication stats per contact across all sources.
data_freshness	Freshness status across all tracked pipeline sources.
relationship_health_summary	Relationship analytics from cadence stats and scores.
time_blocks_daily	Daily aggregates of Time Portrait blocks.
memory.belief_evolution	How beliefs and claims have changed over time.
memory.vw_bridge_sync_status	Overall memory bridge sync status.

---

Access Control

Role Reference

Role	Purpose
superuser	Superuser / DBA. Never used for application queries.
app	Primary application role. INSERT/UPDATE/SELECT on all non-therapy tables.
readonly	Read-only access for reporting and staging queries.
remote_rw	Remote write access via MCP proxy. No DELETE, no DDL. Does not have INSERT on some tables (e.g., document_versions).
n8n_role	n8n workflow engine access.
ha_role	Home Assistant access (ha.* schema only).
grafana_role	Grafana read-only monitoring.
authelia_role	Authelia SSO session store.
therapy_app	Isolated therapy schema access. Only role that can read or write therapy.*.

Rule: Use the app role for all application code. Never use the superuser role for queries.

---

Patterns and Conventions

Timezone Discipline

All time handling follows a single source-of-truth rule. Violating this causes silent off-by-one-day bugs at Pacific midnight.

Column type	Function to use
TIMESTAMPTZ	NOW()
TIMESTAMP (no tz)	pacific_now()
DATE	pacific_today()

Caution

Never use CURRENT_DATE for DATE columns. It returns UTC date, which is wrong for Pacific-timezone data after 4 PM PT. Always use pacific_today().

Utility functions: pacific_now(), pacific_today(), pacific_date(ts), pacific_timestamp(ts).

Primary Key Types

Mixed PK strategy (historical artifact from early migrations):

• TEXT (cast UUID): messages, health_records, contacts, calendar_events — older public tables
• UUID (gen_random_uuid()): documents, social_messages, discord_messages, transactions, workouts, peak_state_scores, time_blocks, all memory.* tables — newer tables

Deduplication

Most import tables carry a *_hash column with a unique constraint:

• health_records.record_hash
• workouts.workout_hash
• contacts.contact_hash
• calendar_events: unique on (source, external_id)
• browser_history: unique on (md5(url), visit_time)

Soft Delete

Tables with excluded + excluded_reason: health_records, calendar_events, workouts. Filtered by *_active views. Does not use a deleted_at pattern.

Contact Linkage

contact_id is the foreign key connecting messages and events to contacts. contact_identifiers provides typed lookup (email, phone, discord_id). contact_aliases handles name variations. The all_messages view only includes rows where contact_id IS NOT NULL.

Vector Search (Memory Schema)

memory.memories uses 4096-dimension vectors with binary quantization HNSW index (binary_quantize(embedding)::bit(4096), hamming distance). Two-stage retrieval: HNSW for candidate set, exact cosine for reranking. Similarity threshold: 0.45 (migration 301).

messages and calendar_events also carry vector embedding columns for per-row semantic search.

Full-text search: messages, documents, and memory.memories carry tsvector search_vector columns maintained by triggers.

PostgreSQL NOTIFY (Real-Time Dashboard)

notify_dashboard_event() and related functions use PostgreSQL NOTIFY for real-time SSE streaming to the dashboard. Channels: dashboard_events, dispatch_update, council_update, github_updated, entity_extraction.

Config Registry (The Manifest)

config_registry_item defines what to track. config_registry_observation records observed values after inbox processing. config_registry_event_inbox receives raw events. config_registry_compute_state() determines four-state status per item. Views config_registry_current_v1 and config_registry_prompt_snapshot_v1 expose current state for monitoring and LLM injection.

---

Migration System

302 migrations applied as of capture date. All migrations are append-only and tracked in schema_migrations.

Rules:

• Migrations are numbered sequentially after a consolidated baseline (000_, 001_, 002_).
• No migration may be modified after it is applied. If a change is needed, create a new migration.
• Multi-statement SQL in n8n Postgres nodes must be wrapped in a function (n8n executes as prepared statements that reject multiple statements).

Key recent migrations:

• 302: Dropped stale social_messages index
• 301: knowledge_search default threshold 0.25 -> 0.45
• 294-297: Time Portrait schema (time_blocks, time_portrait_sources, reconciler tables)
• 263: knowledge_search function + source_config; CTE fence fix (shared CTE caused HNSW bypass)
• 261: binary_quantize HNSW index for memory embeddings

---

Key Functions

Timezone

Function	Purpose
pacific_now()	Current TIMESTAMP (no tz) in Pacific time
pacific_today()	Current DATE in Pacific time
pacific_date(ts)	Converts TIMESTAMPTZ to Pacific DATE
pacific_timestamp(ts)	Converts TIMESTAMPTZ to Pacific TIMESTAMP

Search

Function	Purpose
memory.knowledge_search(...)	14-param semantic + FTS search over memories. Two-stage HNSW retrieval. Default threshold 0.45.
memory.entity_search(...)	Entity-focused semantic search
memory.hybrid_search(...)	Combined vector + FTS search
search_dialogue(...)	FTS search over dialogue table

Relationship

Function	Purpose
refresh_relationship_scores()	Recomputes all relationship scores
relationship_briefing_data(contact_id)	Rich relationship summary for a contact
relationships_needing_attention()	Contacts with drifting cadence
inner_circle()	High-tier relationship contacts

Health and Activity

Function	Purpose
calculate_peak_state_score(date)	Computes Peak State score for a date
refresh_activity_timeline()	Refreshes the activity_timeline materialized view
day_timeline(date)	Full activity timeline for a specific day
check_data_freshness()	Returns freshness status for all tracked sources

Notifications

Function	Purpose
notify_dashboard_event(channel, payload)	PostgreSQL NOTIFY for dashboard SSE
notify_dispatch_update(...)	Notifies dispatch update channel
archive_document_version()	Trigger: archives document to document_versions on UPDATE