System Design

Database Schema

Complete reference for all PostgreSQL tables, columns, relationships, and enums.

Database Schema

Cognova uses PostgreSQL with Drizzle ORM. The schema is defined in server/db/schema.ts and migrations are stored in server/drizzle/migrations/.

This page is the single source of truth for all table and column definitions. API documentation references types defined here.

Entity Relationship Overview

user ──┬── session
       ├── account
       ├── projects ──── tasks ──── reminders
       ├── documents
       ├── cronAgents ── cronAgentRuns
       ├── secrets
       └── userSettings

conversations ──── conversationMessages

hookEvents          (standalone)
memoryChunks        (standalone)
tokenUsage          (standalone)

Auth Tables

These tables are managed by BetterAuth and follow its expected schema. Do not modify column names without checking BetterAuth compatibility.

user

The single admin user. Created automatically on first startup via the seed script.

Column	Type	Nullable	Default	Description
id	text	No	-	Primary key (BetterAuth-generated)
name	text	No	-	Display name
email	text	No	-	Unique email address
email_verified	boolean	No	`false`	Email verification status
image	text	Yes	-	Avatar URL
created_at	timestamptz	No	`now()`	Account creation time
updated_at	timestamptz	No	`now()`	Last profile update

session

Active login sessions. BetterAuth creates and validates these automatically.

Column	Type	Nullable	Default	Description
id	text	No	-	Primary key
user_id	text	No	-	FK to `user.id` (cascade delete)
token	text	No	-	Unique session token
expires_at	timestamptz	No	-	Session expiry (default: 7 days)
ip_address	text	Yes	-	Client IP at login
user_agent	text	Yes	-	Browser user agent at login
created_at	timestamptz	No	`now()`	Session creation time
updated_at	timestamptz	No	`now()`	Last session refresh

account

Authentication provider accounts. For credential auth, stores the hashed password.

Column	Type	Nullable	Default	Description
id	text	No	-	Primary key
user_id	text	No	-	FK to `user.id` (cascade delete)
account_id	text	No	-	Provider-specific account ID
provider_id	text	No	-	Auth provider name (`credential`)
access_token	text	Yes	-	OAuth access token
refresh_token	text	Yes	-	OAuth refresh token
access_token_expires_at	timestamptz	Yes	-	Access token expiry
refresh_token_expires_at	timestamptz	Yes	-	Refresh token expiry
scope	text	Yes	-	OAuth scope
id_token	text	Yes	-	OIDC ID token
password	text	Yes	-	Hashed password (credential auth only)
created_at	timestamptz	No	`now()`	Record creation time
updated_at	timestamptz	No	`now()`	Last update time

verification

Email verification and password reset tokens.

Column	Type	Nullable	Default	Description
id	text	No	-	Primary key
identifier	text	No	-	Email or other identifier
value	text	No	-	Verification token
expires_at	timestamptz	No	-	Token expiry
created_at	timestamptz	No	`now()`	Record creation time
updated_at	timestamptz	No	`now()`	Last update time

Conversation Tables

conversations

Chat sessions between the user and Claude via the web UI.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
session_id	text	No	-	Unique client session identifier
sdk_session_id	text	Yes	-	Claude Agent SDK session ID
title	text	Yes	-	Conversation title (auto-generated)
summary	text	Yes	-	Brief summary of the conversation
status	text	No	`'idle'`	Enum: `idle`, `streaming`, `interrupted`, `error`
total_cost_usd	real	No	`0`	Cumulative API cost
started_at	timestamptz	No	`now()`	Conversation start time
ended_at	timestamptz	Yes	-	Conversation end time
message_count	integer	No	`0`	Total messages in conversation

Relations: Has many conversationMessages.

conversation_messages

Individual messages within a conversation.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
conversation_id	uuid	No	-	FK to `conversations.id` (cascade delete)
role	text	No	-	Enum: `user`, `assistant`
content	text	No	-	JSON string of content blocks
cost_usd	real	Yes	-	Cost for this message
duration_ms	integer	Yes	-	Response generation time
created_at	timestamptz	No	`now()`	Message timestamp

Relations: Belongs to conversations.

System Tables

hook_events

Analytics log for Claude Code hook executions. Used to track tool usage patterns and blocked operations.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
event_type	text	No	-	Enum: `SessionStart`, `SessionEnd`, `PreToolUse`, `PostToolUse`, `PostToolUseFailure`, `UserPromptSubmit`
session_id	text	Yes	-	Claude Code session identifier
project_dir	text	Yes	-	Working directory for the session
tool_name	text	Yes	-	Tool that triggered the event
tool_matcher	text	Yes	-	Hook matcher pattern
event_data	text	Yes	-	JSON string with event-specific data
exit_code	integer	Yes	-	Hook script exit code
blocked	boolean	No	`false`	Whether the operation was blocked
block_reason	text	Yes	-	Why the operation was blocked
duration_ms	integer	Yes	-	Hook execution time
hook_script	text	Yes	-	Path to the hook script
created_at	timestamptz	No	`now()`	Event timestamp

memory_chunks

Persistent memory extracted from Claude Code conversations. Stores concise facts, decisions, and patterns for injection into future sessions.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
session_id	text	Yes	-	Claude session that created this memory
project_path	text	Yes	-	Project scope for filtering
chunk_type	text	No	-	Enum: `decision`, `fact`, `solution`, `pattern`, `preference`, `summary`
content	text	No	-	The extracted memory (concise statement)
source_excerpt	text	Yes	-	Original context for reference
relevance_score	real	No	`1.0`	0.0--1.0 relevance weight (decays over time)
access_count	integer	No	`0`	Retrieval count
last_accessed_at	timestamptz	Yes	-	Last time this memory was retrieved
created_at	timestamptz	No	`now()`	Extraction time
expires_at	timestamptz	Yes	-	Expiry for dynamic forgetting

token_usage

Unified cost and token tracking across all AI operations.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
source	text	No	-	Enum: `chat`, `agent`, `memory_extraction`
source_id	text	Yes	-	ID of the originating resource
source_name	text	Yes	-	Human-readable source label
input_tokens	integer	No	`0`	Input tokens consumed
output_tokens	integer	No	`0`	Output tokens generated
cost_usd	real	No	`0`	Total cost in USD
duration_ms	integer	Yes	-	Operation duration
num_turns	integer	Yes	-	Conversation turns (for agents)
created_at	timestamptz	No	`now()`	Record timestamp

user_settings

Per-user application preferences stored as a JSON string.

Column	Type	Nullable	Default	Description
id	uuid	No	`gen_random_uuid()`	Primary key
user_id	text	No	-	Unique FK to `user.id` (cascade delete)
settings	text	No	`'{}'`	JSON string of user preferences
created_at	timestamptz	No	`now()`	Record creation time
updated_at	timestamptz	No	`now()`	Last update time

Relations: Belongs to user (one-to-one).

Enum Reference

All enum values used across the schema:

Table	Column	Values
tasks	status	`todo`, `in_progress`, `done`, `blocked`
documents	share_type	`public`, `private`
conversations	status	`idle`, `streaming`, `interrupted`, `error`
conversation_messages	role	`user`, `assistant`
cron_agents	last_status	`success`, `error`, `budget_exceeded`, `cancelled`
cron_agent_runs	status	`running`, `success`, `error`, `budget_exceeded`, `cancelled`
hook_events	event_type	`SessionStart`, `SessionEnd`, `PreToolUse`, `PostToolUse`, `PostToolUseFailure`, `UserPromptSubmit`
memory_chunks	chunk_type	`decision`, `fact`, `solution`, `pattern`, `preference`, `summary`
token_usage	source	`chat`, `agent`, `memory_extraction`

The projects, tasks, and documents tables use soft delete. Records are never physically removed -- instead, deleted_at is set to the current timestamp and deleted_by records who performed the deletion.

API queries filter out soft-deleted records by default:

.where(isNull(schema.tasks.deletedAt))

Restore operations set deleted_at and deleted_by back to null.

Audit Fields

Tables with user-driven mutations include three audit columns:

Column	Set When
`created_by`	Record is created
`modified_by`	Record is updated
`deleted_by`	Record is soft-deleted

All audit columns are nullable foreign keys to user.id with ON DELETE SET NULL, so deleting a user does not cascade-delete their created content.

System Design

Architecture overview of Cognova's layers, tech stack, and design principles.

Security Model

Authentication, authorization, path validation, encryption, and production hardening.

Database Schema

Database Schema

Entity Relationship Overview

Auth Tables

user

session

account

verification

Application Tables

projects

tasks

reminders

documents

Conversation Tables

conversations

conversation_messages

Agent Tables

cron_agents

cron_agent_runs

System Tables

secrets

hook_events

memory_chunks

token_usage

user_settings

Enum Reference

Soft Delete Pattern

Audit Fields