Database · CommonOS

CommonOS keeps everything in one Postgres database. Every row carries its profile_id, so one database serves every profile.

How the schema is applied

There is no migrate step. On every start, cos applies the core schema, then each enabled plugin’s schema, in order. Every statement is CREATE … IF NOT EXISTS, so it is safe to repeat. The schemas run under a Postgres advisory lock, so two processes starting at the same time don’t race.

Core tables

Table	Holds
`profiles`	`id`, `tags`, `created_at`. Written at start-up from the profile folders.
`sessions`	`profile_id`, `source`, `title`, `tags`, `parent_session_id`, `context_snapshot`, times
`turns`	`session_id`, `profile_id`, `parent_turn_id`, `tags`, `depth`, `status`, `started_at`, `finished_at`
`messages`	`session_id`, `turn_id`, `seq`, `role`, `content` (JSON), `active`, and a full-text `search` column
`tool_calls`	`turn_id`, `call_id`, `name`, `args`, `result`, `is_error`, `risk`, times
`approvals`	`tool_call_id`, `status`, `decided_at`

Notes:

sessions.context_snapshot is the frozen context tier. It is written at the session’s first turn.
messages.active becomes false when a message is compacted away. Inactive messages stay for history and search. (Compaction is on the roadmap.)
messages.search is a generated tsvector with a GIN index. session_search uses it.
Deleting a profile row deletes its sessions, turns and messages.

Plugin tables

Table	Plugin	Holds
`model_calls`	CommonBill	One row per successful model call: tokens, `cost_usd`, `cost_status`, tags
`memory_entries`	Memory	`profile_id`, `target` (`memory` or `user`), `content`, `position`

The core never reads plugin tables.

Useful queries

The last ten turns, with their status:

SQL

SELECT t.started_at, t.profile_id, t.status, t.depth, s.title
FROM turns t JOIN sessions s ON s.id = t.session_id
ORDER BY t.started_at DESC
LIMIT 10;

Tool calls that needed approval, and the answer:

SQL

SELECT tc.name, tc.args, a.status, a.decided_at
FROM approvals a JOIN tool_calls tc ON tc.id = a.tool_call_id
ORDER BY a.decided_at DESC;

Cost per client this month:

SQL

SELECT tag, sum(cost_usd) AS usd, count(*) AS calls
FROM model_calls, unnest(tags) AS tag
WHERE tag LIKE 'client:%' AND created_at >= date_trunc('month', now())
GROUP BY tag
ORDER BY usd DESC;