Database

Database

Quick Reference

• Engine: PostgreSQL
• ORM: Drizzle ORM (drizzle-orm/pg-core)
• Tables: 18
• Migrations: 85 (incremental SQL)
• Connection: DATABASE_URL environment variable

ER Diagram

erDiagram
    user ||--o{ account : "OAuth accounts"
    user ||--o{ session : "auth sessions"
    user ||--o{ creditLedger : "credit transactions"
    user ||--o{ subscription : "billing"
    user ||--o{ referral : "referrals (as referrer)"
    user ||--o{ referral : "referrals (as referred)"
    user ||--o{ message : "LLM calls"
    user ||--o{ agentRun : "agent executions"
    user ||--o{ adImpression : "ad views"
    user ||--o{ encryptedApiKeys : "BYOK keys"
    user ||--o{ orgMember : "org memberships"
    user ||--o{ limitOverride : "rate limits"
    org ||--o{ orgMember : "members"
    org ||--o{ orgInvite : "invitations"
    org ||--o{ orgRepo : "tracked repos"
    org ||--o{ orgFeature : "feature flags"
    org ||--o{ creditLedger : "org credits"
    publisher ||--o{ agentConfig : "published agents"
    agentRun ||--o{ agentStep : "execution steps"
    fingerprint ||--o{ session : "device binding"

Tables

user

Core user account table.

Column	Type	Nullable	Default	Description
id	text	❌	crypto.randomUUID()	Primary key
name	text	✅	—	Display name
email	text	❌	—	Unique email address
password	text	✅	—	Hashed password
emailVerified	timestamp	✅	—	Email verification date
image	text	✅	—	Avatar URL
stripe_customer_id	text	✅	—	Stripe customer reference
next_quota_reset	timestamp	✅	now() + 1 month	Next credit reset date
created_at	timestamp	❌	now()	Account creation date
referral_code	text	✅	ref-{uuid}	Unique referral code
referral_limit	integer	❌	5	Max referrals allowed
discord_id	text	✅	—	Discord user ID
handle	text	✅	—	Unique username handle
auto_topup_enabled	boolean	❌	false	Auto credit top-up
banned	boolean	❌	false	Account ban status
fallback_to_a_la_carte	boolean	❌	false	Billing fallback

(packages/internal/src/db/schema.ts:66-91)

creditLedger

Tracks all credit transactions (grants, usage, refunds).

Column	Type	Nullable	Default	Description
operation_id	text	❌	—	Primary key (unique operation)
user_id	text	❌	—	FK → user.id
principal	integer	❌	—	Credits granted/deducted
balance	integer	❌	—	Remaining balance
type	grant_type	❌	—	Grant type enum
description	text	✅	—	Transaction description
priority	integer	❌	—	Consumption priority
expires_at	timestamp	✅	—	Expiration date
org_id	text	✅	—	FK → org.id (org credits)

(packages/internal/src/db/schema.ts:117-153)

agentRun

Logs every agent execution with full hierarchy tracking.

Column	Type	Nullable	Default	Description
id	text	❌	UUID	Primary key
user_id	text	✅	—	FK → user.id
agent_id	text	❌	—	Agent identifier (e.g., publisher/agent@version)
publisher_id	text	✅	generated	Extracted from agent_id
agent_name	text	✅	generated	Extracted from agent_id
agent_version	text	✅	generated	Extracted from agent_id
ancestor_run_ids	text[]	✅	—	Full ancestor chain from root
root_run_id	text	✅	generated	First ancestor or self
parent_run_id	text	✅	generated	Immediate parent
depth	integer	✅	generated	Nesting depth
status	agent_run_status	❌	running	running/completed/failed/cancelled
total_credits	numeric	✅	0	Credits (self + descendants)
duration_ms	integer	✅	generated	Execution time

(packages/internal/src/db/schema.ts:625-736)

message

Logs every LLM API call with token tracking.

Column	Type	Nullable	Default	Description
id	text	❌	—	Primary key
model	text	❌	—	LLM model used
request	jsonb	✅	—	Full request payload
response	jsonb	❌	—	Full response
input_tokens	integer	❌	0	Input token count
output_tokens	integer	❌	—	Output token count
cost	numeric(100,20)	❌	—	Dollar cost
credits	integer	❌	—	Credits consumed
byok	boolean	❌	false	Bring-your-own-key flag
user_id	text	✅	—	FK → user.id

(packages/internal/src/db/schema.ts:208-249)

org

Organization accounts for team billing.

Column	Type	Nullable	Default	Description
id	text	❌	UUID	Primary key
name	text	❌	—	Organization name
slug	text	❌	—	Unique URL slug
owner_id	text	❌	—	FK → user.id
stripe_customer_id	text	✅	—	Stripe customer
credit_limit	integer	✅	—	Monthly credit cap

(packages/internal/src/db/schema.ts:289-322)

subscription

Stripe subscription tracking.

Column	Type	Nullable	Default	Description
stripe_subscription_id	text	❌	—	Primary key
user_id	text	✅	—	FK → user.id
tier	integer	✅	—	Subscription tier
status	subscription_status	❌	active	Subscription state
cancel_at_period_end	boolean	❌	false	Cancellation flag

(packages/internal/src/db/schema.ts:464-500)

All Remaining Tables

account

OAuth provider accounts (NextAuth adapter). PK: (provider, providerAccountId).

session

Auth sessions with type classification (web/pat/cli). PK: sessionToken.

verificationToken

Email verification tokens. PK: (identifier, token).

encryptedApiKeys

User BYOK keys (encrypted). PK: (user_id, type). Types: anthropic, gemini, openai.

orgMember

Organization membership with roles. PK: (org_id, user_id). Roles: owner/admin/member.

orgInvite

Organization invitations with token-based acceptance.

orgRepo

Tracked repositories per organization.

orgFeature

Feature flags per organization with JSON config.

referral

Referral tracking with status progression (pending → completed).

fingerprint

Device fingerprinting for session binding.

adImpression

Ad impression and click tracking with credit grants.

limitOverride

Per-user rate limit overrides.

gitEvalResults

Evaluation benchmark results storage.

agentConfig

Published agent definitions (Agent Store). Versioned with semver.

publisher

Agent Store publisher profiles. Owned by user OR org (enforced by check constraint).

agentStep

Individual execution steps within agent runs.

Enums

Enum	Values	Used By
api_key_type	anthropic, gemini, openai	encryptedApiKeys
grant_type	(from GrantTypeValues)	creditLedger
session_type	web, pat, cli	session
agent_run_status	running, completed, failed, cancelled	agentRun
agent_step_status	running, completed, skipped	agentStep
subscription_status	incomplete, trialing, active, past_due, canceled, unpaid, paused	subscription
org_role	owner, admin, member	orgMember, orgInvite
referral_status	(from ReferralStatusValues)	referral

Indexes & Constraints

Table	Index	Columns	Purpose
creditLedger	idx_credit_ledger_active_balance	user_id, balance, expires_at, priority	Fast active credit lookup
message	message_finished_at_user_id_idx	finished_at, user_id	Usage history queries
agentRun	idx_agent_run_ancestors_gin	ancestor_run_ids (GIN)	Hierarchy traversal
agentRun	idx_agent_run_status	status (partial: running)	Active run lookup
subscription	idx_subscription_status	status (partial: active)	Active subscription check

Migration History

The project uses 85 incremental SQL migrations managed by Drizzle Kit.

Migration files are located at packages/internal/src/db/migrations/.

Configuration: packages/internal/src/db/drizzle.config.ts

Related Pages

• Architecture — System design
• Data Flow — How data moves through the system
• Deployment — Database setup