Data Model & Database Store
Relevant source files
The following files were used as context for generating this wiki page:
Cogmo utilizes a unified PostgreSQL schema managed via Drizzle ORM as the source of truth for its data model. The system is designed for a single technical user, emphasizing persistent memory, secure secret management, and a robust agentic loop. The database layer handles everything from core conversation state and LLM provider configurations to complex sandbox container metadata and Model Context Protocol (MCP) server integration.
The architecture follows a "Store Pattern" where business logic is decoupled from infrastructure. Domain-specific stores handle data access, while the core infrastructure provides connection pooling and transaction helpers. The system uses UUIDv7 for primary keys and relies heavily on JSONB columns with Zod validation to maintain flexibility without sacrificing type safety.
Sources: CLAUDE.md, README.md, changelog.d/2026-03-23-rewrite-data-model-drizzle-source.md
Database Architecture & Core Rules
Cogmo's database layer is built on PostgreSQL 18 with the pgvector extension to support vector-based memory features. The project uses Drizzle ORM to manage schemas and migrations.
Implementation Principles
- Source of Truth: Drizzle schemas are the primary definition of the data model.
- Primary Keys: UUIDv7 is used for all primary keys to ensure sortability and uniqueness.
- Constraints: Columns are
NOT NULLby default. - Type Safety: JSONB columns are paired with Zod schemas at the store boundary for both read and write operations.
- Transactions: The system defaults to
REPEATABLE READisolation levels and uses atx-first method shape in stores.
Sources: CLAUDE.md:129-132, CLAUDE.md:180-185, changelog.d/2026-03-23-rewrite-data-model-drizzle-source.md, design/testing.md:58-62
Database Infrastructure Flow
The following diagram illustrates how the application interacts with the database through the Store pattern.
Primary Data Entities
The data model is organized into several functional areas, reflecting the system's modular architecture.
Core & Integration Tables
| Table | Description | Derived Constraints/Details |
|---|---|---|
llm_providers | Configured LLM providers (Anthropic, OpenAI, etc.). | Linked to profiles via Foreign Key (FK). |
secrets | Encrypted credentials (API keys, bot tokens). | AES-256-GCM encrypted, HKDF-derived from master key. |
mcpServers | Configuration for Model Context Protocol servers. | Name regex: /^[a-z][a-z0-9_]*$/. |
mcpServerTools | Cached tool definitions from MCP servers. | Unique constraint on (server_id, tool_name). |
Sources: CLAUDE.md:52-53, README.md:46-51, changelog.d/2026-05-04-mcp-integration-design-doc-phase.md
Autonomous Coding & Sandbox Tables
As part of the self-evolution and coding delegation features, the database tracks containerized environments and long-running coding tasks.
| Table | Description |
|---|---|
coding_tasks | Tracks state, autonomy gates (plan/tool/merge), and status of coding jobs. |
coding_repos | Metadata for repositories where coding tasks are executed. |
containers | Infrastructure metadata for Sysbox-powered sandboxes. |
cogmo_instances | Links specific application instances to sandbox containers. |
Sources: changelog.d/2026-04-17-design-drafted-autonomous-coding-container.md
Memory & Hindsight Integration
While core relational data stays in PostgreSQL, the agent's "long-term memory" is managed through the Hindsight server. The data model supports this through several structures:
Model Context Protocol (MCP) Store
The MCP implementation introduces a specific set of storage requirements to handle dynamic tool discovery and approval workflows.
MCP Store Components
The McpStore interface (implemented by DrizzleMcpStore) manages server lifecycles and tool pinning. It handles:
- Server CRUD: Adding, removing, and listing servers with status tracking (
last_connected_at,last_error). - Approval Workflow:
approval_statustransitions (pending,approved,needs_reapproval). - Tool Snapshotting: Storing
schema_hashandschema_snapshotin JSONB to detect changes in external tools. - Value Sources: A discriminated union (
literal|secret) allows mixing plain constants with encrypted secret references in server configurations.
Sources: changelog.d/2026-05-04-mcp-integration-design-doc-phase.md
Testing & Validation
Testing the data model involves a three-tier strategy, utilizing PGlite for unit-level SQL validation.
- JSONB Raw-SQL Bypass: Tests explicitly attempt to update JSONB columns with malformed data using raw SQL to ensure the Store boundary's Zod validation correctly rejects invalid data on read.
- Store happy-path: Verifies atomic updates, idempotent replays (for Inngest retries), and constraint collision handling (e.g.,
UniqueViolationError). - PGlite Integration:
createTestDatabase()boots a WASM-based PostgreSQL instance with thepg_uuidv7extension to mirror the production environment.
Sources: design/testing.md:15-20, design/testing.md:60-70, design/testing.md:85-95
Summary
Cogmo's Data Model & Database Store provides a robust foundation for an evolving personal agent. By combining the strict relational integrity of PostgreSQL 18 with the flexible validation of Drizzle and Zod, the system ensures that complex states—ranging from encrypted secrets to containerized coding tasks—are stored securely and accessed through well-defined domain interfaces. The reliance on event decoupling and thin infrastructure layers allows the data model to scale alongside the agent's self-evolution capabilities.
Sources: CLAUDE.md:144-155, changelog.d/2026-03-23-claude-design-philosophy-early-abstractions.md