Relationships

The schema uses two separate mechanisms for relationships: foreign keys (database-enforced referential integrity) and Drizzle relations (TypeScript-level query API). Both must be defined, and they must stay in sync.

Foreign Keys

Foreign keys are defined inline on the column using .references():

userId: text("user_id")
  .notNull()
  .references(() => user.id, { onDelete: "cascade" }),

Cascade Rules

The convention is onDelete: "cascade" for ownership relationships (deleting the parent deletes the children) and onDelete: "set null" for optional references (deleting the referenced row nulls the FK column).

Cascade examples (parent deletion removes children):

Child table	FK column	Parent table	Rationale
session	userId	user	Sessions belong to a user
account	userId	user	OAuth accounts belong to a user
member	organizationId	organization	Memberships belong to an org
planStep	planId	researchPlan	Steps belong to a plan
aiCallDetails	usageId	aiUsage	Details are 1:1 with usage
researchOutput	planId	researchPlan	Outputs belong to a plan
researchFeedback	planId	researchPlan	Feedback belongs to a plan
personalAccessToken	userId	user	PATs belong to a user

Set-null examples (referenced row deletion preserves the child):

Child table	FK column	Parent table	Rationale
planAuditLog	stepId	planStep	Audit log survives step deletion
researchArtifact	planId	researchPlan	Artifacts can outlive their plan
researchArtifact	stepId	planStep	Artifacts can outlive their step
extractionResult	sourceArtifactId	researchArtifact	Extractions can outlive source
skillExecution	planId	researchPlan	Skill records are diagnostic

No explicit cascade (implicit NO ACTION):

Child table	FK column	Parent table	Rationale
aiBatch	userId	user	Prevent user deletion while batches exist
aiUsage	userId	user	Usage records must survive for billing
aiUsage	batchId	aiBatch	Optional ref, no cascade needed

Indexing Foreign Keys

Every foreign key column gets a regular index. This is explicit in the schema -- PostgreSQL does not automatically index foreign keys:

(table) => [
  index("plan_step_planId_idx").on(table.planId),
  index("plan_step_organizationId_idx").on(table.organizationId),
],

Drizzle Relations

Drizzle relations are defined separately from foreign keys. They enable the relational query API (db.query.table.findMany({ with: { relation: true } })):

export const researchPlanRelations = relations(researchPlan, ({ many, one }) => ({
  steps: many(planStep),
  branchingConditions: many(planBranchingCondition),
  auditLogs: many(planAuditLog),
  artifacts: many(researchArtifact),
  user: one(user, {
    fields: [researchPlan.userId],
    references: [user.id],
  }),
}));

Relation Definition Patterns

One-to-many: The parent defines many(), the child defines one() with explicit field mapping:

// Parent side
export const aiModelRelations = relations(aiModel, ({ many }) => ({
  pricing: many(aiModelPricing),
}));

// Child side
export const aiModelPricingRelations = relations(aiModelPricing, ({ one }) => ({
  model: one(aiModel, {
    fields: [aiModelPricing.modelId],
    references: [aiModel.id],
  }),
}));

One-to-one: The parent defines one() using the child's FK pointing back:

// aiUsage has one aiCallDetails (1:1 via unique FK)
export const aiUsageRelations = relations(aiUsage, ({ one }) => ({
  details: one(aiCallDetails, {
    fields: [aiUsage.id],
    references: [aiCallDetails.usageId],
  }),
}));

Relation Naming

Relation names use the plural form for many() relationships and singular for one() relationships:

• steps, artifacts, auditLogs, extractionResults (many)
• plan, step, user, model, sourceArtifact (one)

This convention makes the relational query API read naturally: findMany({ with: { steps: true, user: true } }).

The Research Domain Relationship Graph

The research domain has the most complex relationship graph in the schema. Here is how the tables connect:

researchPlan (top-level)
  |-- planStep (1:many, cascade)
  |     |-- planBranchingCondition (1:many, cascade)
  |     |-- planAuditLog (1:many, set null on step)
  |     |-- researchArtifact (1:many, set null)
  |     |-- extractionResult (1:many, set null)
  |     \-- mcpToolCallLog (1:many, set null)
  |-- planBranchingCondition (1:many, cascade via planId)
  |-- planAuditLog (1:many, cascade via planId)
  |-- researchArtifact (1:many, set null via planId)
  |     \-- extractionResult (1:many, set null via sourceArtifactId)
  |-- extractionResult (1:many, set null via planId)
  |-- skillExecution (1:many, set null via planId)
  |-- mcpToolCallLog (1:many, set null via planId)
  |-- researchOutput (1:many, cascade)
  \-- researchFeedback (1:many, cascade)

The cascade/set-null decisions reflect a key principle: operational data (outputs, feedback) cascades with the plan, but knowledge data (artifacts, extractions) survives independently. An artifact created during research may be reusable in future plans.

Polymorphic Source References

The document_chunk table uses a polymorphic pattern to reference rows from multiple source tables:

sourceTable: chunkSourceTableEnum("source_table").notNull(),
sourceId: text("source_id").notNull(),

The chunkSourceTableEnum restricts valid source tables to research_artifact, research_output, and extraction_result. This is not a true foreign key -- PostgreSQL cannot enforce a FK that references different tables based on another column's value.

Integrity is maintained by:

1. The enum restricting which tables can be referenced
2. Application code validating the source exists before inserting
3. A composite index for efficient lookups: index("document_chunk_source_idx").on(table.sourceTable, table.sourceId)

This pattern was chosen over separate nullable FK columns (one per source table) because the chunk table needs to support additional source types in the future without schema changes.

Cross-Domain References

Several tables reference user from auth.ts despite living in different domain files. This is the only cross-domain FK reference pattern -- all other relationships are within a single schema file.

Tables that reference user:

• ai.ts: aiBatch, aiUsage (usage tracking)
• research.ts: researchPlan, researchArtifact, extractionResult, skillExecution, mcpToolCallLog, researchOutput, researchFeedback
• search.ts: documentChunk
• mcp.ts: personalAccessToken

The user table is the only table referenced across all domain files because user identity is the universal actor concept in the system.