101 lines
3.9 KiB
Markdown
101 lines
3.9 KiB
Markdown
---
|
|
source: raw/schema.sql
|
|
content_hash: 71b47e47b483834c887de14c76d5b16506d90ad64198d76a92a7b3731132dab8
|
|
namespace: work
|
|
last_synced: 2026-06-01
|
|
confidence: 0.9
|
|
tags: [personal-os, postgres, schema, database]
|
|
---
|
|
|
|
# Personal OS — Postgres Schema
|
|
|
|
The `personal_os` database is the durable store behind the Personal OS. It
|
|
holds Asana state, the agent's annotations, the wiki, and semantic memory.
|
|
Created with `psql -U admin -d personal_os -f schema.sql`.
|
|
|
|
## Extensions
|
|
|
|
- `vector` — pgvector, used for embeddings (tasks, wiki, memory)
|
|
- `pg_trgm` — trigram GIN index on `tasks.name` for fuzzy search
|
|
|
|
## Core Tables
|
|
|
|
### `tasks`
|
|
One row per Asana task GID, upserted every sync. Mirrors enough of the Asana
|
|
task object to answer questions without round-tripping. `source` tracks
|
|
*how* we discovered the task: `my_tasks` | `following` | `delegated` |
|
|
`project` — highest-priority single value when multiple apply. Includes
|
|
`possibly_deleted` + `last_seen_in_full_sync` for soft deletion. The full
|
|
API response is preserved in `raw_json` (JSONB).
|
|
|
|
### `stories`
|
|
Raw Asana event log per task. `resource_subtype` is Asana's event type
|
|
(`comment_added`, `assigned`, `due_date_changed`, `section_changed`, etc.).
|
|
Both `text` and `html_text` are kept — the HTML lets us parse embedded task
|
|
GID references for [[task-edges]].
|
|
|
|
### `task_edges`
|
|
Directed graph of task→task relationships. `relation_type`:
|
|
`subtask` | `dependency` | `dependent` | `project_sibling` | `mention`.
|
|
`related_gid` may not yet exist in `tasks` (edges can point to unfetched
|
|
tasks).
|
|
|
|
### `task_annotations`
|
|
Agent-written (or user-written) notes about tasks. `annotation_type`:
|
|
`irrelevant` | `watching` | `needs_action` | `snoozed`. The `note` column
|
|
holds the agent's reasoning. Tracked by `annotated_by` (`agent` | `user`).
|
|
|
|
### `sync_state`
|
|
One row per logical sync stream. Keys include `my_tasks`, `following`,
|
|
`delegated`, `project:{gid}`, `workspace_events`. `cursor` is either an ISO
|
|
timestamp (`modified_since`) or an Events API sync_token.
|
|
|
|
## Embeddings & Wiki
|
|
|
|
### `task_embeddings`
|
|
1024-dim Voyage AI embeddings per task, HNSW index for cosine similarity.
|
|
|
|
### `file_references`
|
|
Files / URLs queued for wiki ingest. `bookmark_data` stores macOS
|
|
security-scoped bookmarks (BYTEA). `wiki_stale=true` → next [[wiki-ingest-process]]
|
|
run will process it. `modification_log` (JSONB) keeps a hash-change trail.
|
|
|
|
### `wiki_pages`
|
|
LLM-synthesised pages. `content` is markdown synthesis, **never** a raw
|
|
copy. `sources` (JSONB) links back to `file_references`. `superseded_by`
|
|
chains old versions. 1536-dim embedding for semantic search.
|
|
`confidence` — 0.6 inferred, 0.8 default, 0.9 structured source, 1.0
|
|
human-written.
|
|
|
|
### `memory_store`
|
|
Semantic memory accumulated from Discord / Claude / manual entries.
|
|
`type`: `fact` | `preference` | `decision` | `person`. `source`:
|
|
`discord` | `claude` | `manual`. Optional `expires_at` (null = permanent).
|
|
|
|
## Namespacing
|
|
|
|
All core tables carry `namespace TEXT NOT NULL DEFAULT 'work'`, constrained
|
|
to `work` | `personal` | `family`. Lets one DB serve multiple Personal OS
|
|
contexts without cross-leakage.
|
|
|
|
## Views
|
|
|
|
### `active_tasks`
|
|
Convenience view: not completed, not annotated `irrelevant`, not snoozed
|
|
into the future. Ordered by `source` priority (my_tasks → delegated →
|
|
following → project), then `due_on`, then `modified_at DESC`. The agent's
|
|
default starting point for daily review.
|
|
|
|
## Key Design Choices
|
|
|
|
- Asana stays the source of truth — DB is a queryable cache.
|
|
- `raw_json` on tasks preserves un-modeled fields without schema churn.
|
|
- `task_edges.related_gid` is intentionally not a foreign key — we record
|
|
references to tasks we haven't fetched.
|
|
- Annotations are append-keyed by `(task_gid, annotation_type)` so the
|
|
agent can have one note per category without overwriting history via
|
|
the [[corrections-log]] table.
|
|
|
|
## Related
|
|
[[personal-os-state-2026-04-27]] [[wiki-ingest-process]] [[personal-os-catchup-plan-2026-04-27]] [[ddg-asana-workflow]]
|