CherryHQ-cherry-studio/docs/references/data/best-practice-default-values-and-nullability.md

Default Values & Nullability

Standards for placing default values across the data stack and judging column nullability. Read this when designing a new SQLite table, defining a Zod entity schema, or reviewing PRs that introduce defaults at any layer.

Problem

A default value can technically be placed in six distinct locations in this stack:

#	Layer	Timing	Direction
1	DB column DEFAULT 'X'	INSERT (SQL)	Write
2	Drizzle $defaultFn / $default	INSERT (JS, before SQL)	Write
3	Zod schema .default() (entity / Create / Update)	.parse()	Write
4	Service explicit dto.x ?? DEFAULT	Pre-INSERT	Write
5	rowToEntity row.x ?? DEFAULT	Post-SELECT	Read
6	Renderer form / hook pre-fill	Before POST	Write (upstream)

When the same field has defaults defined in more than one place, the values must be kept in sync by hand, and any drift produces silent bugs:

• PATCH leakage: Zod v4 .partial() retains .default() on inner fields, so a PATCH /entity/:id { fooId: 'x' } body parsed against UpdateSchema = CreateSchema.partial() materializes every default value. The service then writes those defaults to the row, overwriting user-set fields. (Zod issues #4799, #5642, #4179.)
• Read/write drift: a rowToEntity masks DB NULL with a hardcoded '🌟'. Months later someone changes the Zod create default to '✨'. New rows get '✨'; older rows still surface as '🌟'. The two ends of the same field disagree.

The rules below close both classes of bug by enforcing one source of truth per field plus read paths that don't fabricate state the DB doesn't carry.

Five Rules

R1. NULL vs NOT NULL must reflect domain semantics

A column is nullable only when NULL carries a domain meaning distinct from any value in the column's domain. Examples:

• assistant.modelId: NULL = "no model selected yet" — a real product state, distinct from any specific model id.
• topic.deletedAt: NULL = "not deleted" — no timestamp value can express this.
• message.parentId: NULL = "root node" — distinct from any non-empty id.

Otherwise the column is NOT NULL. If a column "should" always have a value but is currently nullable, fix the column constraint, not the read path.

R2. Each field has at most one source of truth for its default

Pick exactly one of locations #1–#4 (write-side) per field, plus location #5 only when the field is genuinely T | null and the read should preserve it. Never define the same default in multiple places. The chooser is in Decision Matrix 2.

R3. Read path must not fabricate defaults

rowToEntity may only:

• spread a row;
• run nullsToUndefined(row) once at the SQLite NULL → TypeScript undefined boundary;
• call timestampToISO / timestampToISOOrUndefined for Date.now() ↔ ISO conversion;
• narrow string fields to literal-union types (e.g. clean.type as McpServer['type']).

row.x ?? someValue is forbidden. If the impulse arises, the column is mis-designed: switch it to NOT NULL with a DB DEFAULT or $defaultFn, or accept that the entity field is genuinely T | null and surface NULL to the renderer.

Exception: when the domain type declares T | null (e.g. AssistantSchema.modelId.nullable()), bypass clean and reference row.x directly to preserve the contract. See Row → Entity Mapping.

R4. Write path covers only what the database cannot

service.create() should pass values into db.insert(...).values({...}) only for columns where:

1. The column is NOT NULL, AND
2. The column has neither a DB DEFAULT nor a $defaultFn, AND
3. The DTO doesn't already supply a value.

For everything else, omit the field. Drizzle leaves it out of the SQL; the DB applies its own default (or NULL for nullable columns). Don't restate values the DB already knows.

R5. Update schema must derive from a defaults-free source

UpdateSchema = CreateSchema.partial() is only safe when Create has no .default() calls. Because Zod v4 retains .default() through .partial(), deriving Update from a Create that carries defaults causes PATCH bodies to materialize those defaults, which the service then writes to the row.

When Create has defaults — or whenever in doubt — derive Update directly from the entity:

// ✅ Always safe
export const UpdateXxxSchema = XxxSchema.pick(XXX_MUTABLE_FIELDS).partial()

This dovetails with API Design Guidelines § Rule C.

Decision Matrix 1: Should this column be NULL or NOT NULL?

Pattern	Choose	Example
Optional foreign key	nullable	assistant.modelId, task.assigneeId, message.parentId
Time of an event that may not have occurred	nullable	deletedAt, cancelledAt, lastLoginAt
Tri-state boolean	nullable	verification.passed: true | false | null
Sparse attribute where "absent" ≠ "empty"	nullable	user.middleName, product.discontinuedReason
Unassigned-tagged state	nullable	pr.reviewerId (unassigned vs assigned)
Every row should have a value, with '' / 0 / [] as the "empty" form	NOT NULL + DB DEFAULT	assistant.prompt = '', agent.sortOrder = 0, tag.color
Product policy "every row has X by default"	NOT NULL + DB DEFAULT	assistant.emoji = '🌟'
Counter / aggregate	NOT NULL + DB DEFAULT	views, retryCount
Audit timestamps	NOT NULL + $defaultFn	createdAt, updatedAt
Required foreign key	NOT NULL	topic.userId, message.topicId

Reverse check: if rowToEntity carries row.x ?? someValue for column x, that is reverse evidence that x should be NOT NULL — see R3.

Decision Matrix 2: Where should the default value live?

Location	Best for	Trade-off	SQLite-specific note
#1 DB DEFAULT (text().notNull().default(''))	Type-level "empty" values that by definition won't change ('', 0, false, [])	Single source at the schema; DB enforces it for any caller including raw SQL	Effectively a near-permanent choice in SQLite — every change forces a full-table rebuild and never touches existing rows. See § DB defaults are near-permanent below.
#2 Drizzle $defaultFn (integer().$defaultFn(() => Date.now()))	Dynamic per-row values: UUIDs, Date.now()	Lives in schema file but runs in the JS layer; consistent for all Drizzle-driven inserts	Doesn't apply to raw SQL writers — but those should be rare here
#3 Zod .default()	Avoid on entity / Create / Update — see warnings below	Couples shared schema package to runtime constants; forces z.input / z.output type split; bypasses non-handler callers (seeders, internal-service calls)	n/a
#4 Service dto.x ?? DEFAULT	Tunable product values that may evolve (e.g. DEFAULT_ASSISTANT_SETTINGS)	Lives next to business logic; covers all callers (handler, seeder, internal); changes are pure code edits with no migration	Best fit when the ideal value tracks product iteration

Why Zod .default() is discouraged

1. Caller asymmetry — Zod defaults run at .parse() time. Handler-driven inserts get them; seeder / service-to-service / migration code paths construct DTOs directly and don't, producing inconsistent rows.
2. Type duality — .default() makes a schema's z.input and z.output types diverge: body callers see optional fields, service receivers see required ones. Either every Create* schema spawns paired …Body / …Dto types, or one of the two ends is mistyped.
3. PATCH leakage — see R5. Even when defaults live only on Create, deriving Update from Create re-introduces them; deriving from entity adds rule complexity that's easy to forget.

If a default truly must live in Zod (e.g., a query-string parameter with a baseline value), keep it on the specific schema it applies to (typically ListXxxQuerySchema), never on the entity, Create, or Update.

DB defaults are near-permanent

Putting a value into a DB column DEFAULT for the first time costs nothing — it lands in the next migration's CREATE TABLE. Changing it later is expensive and asymmetric, so the first write is effectively the final one. Three forces compound:

• SQLite has no ALTER COLUMN SET DEFAULT (sqlite.org/lang_altertable). Changing a DEFAULT requires the 12-step table-rebuild dance: create a new table with the new schema, copy data, drop, rename, recreate indexes / triggers / FKs.
• Each change costs a full-table rebuild at runtime. drizzle-kit auto-generates the rebuild SQL (PRAGMA / CREATE __new_xxx / INSERT ... SELECT / DROP / RENAME / re-create indexes), so codegen is not the bottleneck — the SQLite operation is. It copies every row, holds a schema lock for the duration, and consumes ~2× temporary disk for the duplicated table; on multi-GB tables this is no longer free. FTS5 virtual tables and triggers attached to the rebuilt table are also dropped and must be recreated by separate custom-SQL statements.
• DEFAULT changes never touch existing rows. Rows created before the change keep their old default value. If the new constraint can't tolerate the old values (e.g. tightening to NOT NULL while legacy rows hold NULL), the rebuild's INSERT ... SELECT line must be hand-edited with COALESCE(col, 'fallback') — drizzle-kit will not synthesize that for you.

Before placing a value into a DB DEFAULT, ask:

Question	If you can't confidently answer "yes"
Has this value been validated against real product usage?	Move to service ?? until validated
Is this value's meaning stable against provider updates / UX redesigns / A/B tests / regulatory changes?	Move to service ??
Is "rows created before any future change keep the old default" acceptable?	Move to service ??, or budget a backfill migration upfront

The safe bias: only DB-DEFAULT the values that are type-level "empty" ('', 0, false, []) — those almost never change because they're absence markers, not product decisions. Anything that's a product choice ('🌟', default model parameters, sentinel category values) goes in service ?? first; promote to DB only after the value has stabilized through at least one release cycle in production.

A service-side default change is a code edit, one PR, no migration risk. A DB DEFAULT change is a full-table rebuild migration: copy every row, recreate indexes / triggers / FTS, and hand-write COALESCE for any legacy NULL backfill the new constraint can't tolerate. Reviewed differently, gated differently, slow on production-sized tables. Don't trade tomorrow's agility for today's tidiness.

Quick chooser

Default value's character	Pick
Type-level "empty" by definition ('', 0, false, []) — won't change because not a product choice	DB DEFAULT
Dynamic per row (timestamps, UUIDs)	Drizzle $defaultFn
Product-chosen value ('🌟', model parameters, sentinel category) — could conceivably evolve	Service ??
Unsure whether it'll ever change	Service ?? — cheap to change later; promote to DB only after the value has stabilized

Skip Zod regardless.

Standard Layered Design

Reference end-state for an assistant-like entity, demonstrating R1–R5:

// ─── DB schema ────────────────────────────────────────────────
// Stable defaults live here; settings has no DB DEFAULT because it's a
// tunable product value (Service is its source of truth).
export const assistantTable = sqliteTable('assistant', {
  id: uuidPrimaryKey(),                                  // $defaultFn UUID
  name: text().notNull(),                                // required, no default
  prompt: text().notNull().default(''),                  // type-level empty: DB handles
  emoji: text().notNull(),                               // product-chosen ('🌟' may evolve): Service fills
  description: text().notNull().default(''),             // type-level empty: DB handles
  modelId: text().references(() => userModelTable.id),   // legitimately nullable (R1)
  settings: text({ mode: 'json' })
    .$type<AssistantSettings>()
    .notNull(),                                          // NOT NULL, no DB DEFAULT — Service fills
  ...createUpdateDeleteTimestamps                        // $defaultFn for createdAt / updatedAt
})

// ─── Zod schema ───────────────────────────────────────────────
// Pure shape: no .default() calls anywhere.
export const AssistantSchema = z.strictObject({
  id: AssistantIdSchema,
  name: z.string().min(1),
  prompt: z.string(),
  emoji: z.emoji(),
  description: z.string(),
  modelId: UniqueModelIdSchema.nullable(),               // T | null contract preserved
  settings: AssistantSettingsSchema,
  createdAt: z.iso.datetime(),
  updatedAt: z.iso.datetime()
})
export type Assistant = z.infer<typeof AssistantSchema>

const ASSISTANT_MUTABLE_FIELDS = {
  name: true, prompt: true, emoji: true, description: true,
  modelId: true, settings: true
} as const

// Create: all mutable fields, all optional except `name`. No defaults.
export const CreateAssistantSchema = AssistantSchema
  .pick(ASSISTANT_MUTABLE_FIELDS).partial().required({ name: true })
export type CreateAssistantDto = z.infer<typeof CreateAssistantSchema>

// Update: derived from entity, not from Create. R5.
export const UpdateAssistantSchema = AssistantSchema
  .pick(ASSISTANT_MUTABLE_FIELDS).partial()
export type UpdateAssistantDto = z.infer<typeof UpdateAssistantSchema>

// ─── Service ──────────────────────────────────────────────────
async create(dto: CreateAssistantDto): Promise<Assistant> {
  const [row] = await this.db.insert(assistantTable).values({
    ...dto,
    emoji: dto.emoji ?? '🌟',                             // product-chosen default: Service is the source of truth
    settings: dto.settings ?? DEFAULT_ASSISTANT_SETTINGS  // tunable product default: Service is the source of truth
    // prompt / description omitted → DB DEFAULT '' applies
    // modelId omitted (or null) → SQLite stores NULL
  }).returning()
  return rowToAssistant(row)
}

async update(id: string, dto: UpdateAssistantDto): Promise<Assistant> {
  const [row] = await this.db.update(assistantTable)
    .set(dto)                                            // Drizzle skips undefined — PATCH-correct
    .where(eq(assistantTable.id, id)).returning()
  return rowToAssistant(row)
}

// ─── Row → Entity ─────────────────────────────────────────────
// No `??` fallbacks. R3.
function rowToAssistant(row: typeof assistantTable.$inferSelect): Assistant {
  const clean = nullsToUndefined(row)
  return {
    ...clean,
    modelId: row.modelId,                                // preserve T | null contract
    createdAt: timestampToISO(row.createdAt),
    updatedAt: timestampToISO(row.updatedAt)
  }
}

Anti-patterns

Wrong	Why	Correct
Column nullable + rowToEntity does row.x ?? someDefault	Read path masks NULL state; future schema changes drift silently between layers	Make column NOT NULL with DB DEFAULT (R1, R3)
Same default value defined in DB DEFAULT, Zod .default(), and rowToEntity ??	Three places must stay in sync; any change forgets one	Pick one source of truth (R2)
UpdateSchema = CreateSchema.partial() with .default() on Create fields	Zod v4 preserves defaults through .partial(); PATCH bodies materialize them and overwrite row state	Derive Update from entity directly (R5)
.default(DEFAULT_X_SETTINGS) on Zod entity / Create schema	Defaults bleed into every derived schema; non-handler callers bypass it; renderer typings split into z.input / z.output	Move default to service ?? (Decision Matrix 2)
rowToEntity running ?? '🌟' to mask NULL	The product wants every row to have an icon — express it in the column constraint plus the default-fill stage, not the mapper	text().notNull() + service dto.emoji ?? '🌟' (product-chosen value belongs in service — see § DB defaults are near-permanent)
Service create() passes every field, including ones the DB has DEFAULTs for	Restates DB knowledge in app code; drift risk if defaults change in only one place	Omit fields the DB / $defaultFn already handles (R4)
Putting a product-chosen value ('🌟', default temperature, sentinel category) in DB DEFAULT thinking "I can tune it later"	SQLite has no ALTER COLUMN SET DEFAULT; changing it requires a hand-written table-rebuild and doesn't update existing rows. The "tune later" assumption is false	Service ??; promote to DB only after the value has stabilized through a release cycle (see § DB defaults are near-permanent)

Case Studies

A. assistant.prompt / emoji / description / settings — anti-pattern (current state)

Three layers each define defaults:

Field	DB column (assistant.ts)	Zod Create	rowToAssistant (AssistantService.ts)
prompt	text().default('') (nullable)	.default('')	row.prompt ?? ''
emoji	text() (nullable, no default)	.default('🌟')	row.emoji ?? '🌟'
description	text().default('') (nullable)	.default('')	row.description ?? ''
settings	text({mode:'json'}).$type<AssistantSettings>() (nullable)	.default(DEFAULT_ASSISTANT_SETTINGS)	normalizeSettings(row.settings)

Diagnosis: violates R1 (columns "should" always have values but are nullable), R2 (default in three places per field), R3 (?? in rowMapper).

Fix: per the Standard Layered Design above. After the fix prompt / description move to DB DEFAULT (type-level empty); emoji and settings move to service ?? (product-chosen / tunable values that may evolve, per § DB defaults are near-permanent); rowToAssistant no longer fabricates anything.

B. assistant.modelId — correct (current state)

The DB column text().references(...) is nullable; the entity declares UniqueModelIdSchema.nullable(); the row mapper reads row.modelId directly to preserve the T | null contract; the renderer treats NULL as "no model selected" and renders accordingly.

Diagnosis: this is what a legitimately-nullable field looks like — NULL has a domain meaning, no read-path mask, no fabricated default.

C. agent.accessiblePaths — anti-pattern (current state)

DB column (agent.ts): text({ mode: 'json' }).$type<string[]>() — nullable, no DB DEFAULT. RowMapper (AgentService.ts rowToAgent): accessiblePaths: row.accessiblePaths ?? [].

Diagnosis: same shape as Case A. The product wants every agent to have a non-empty workspace path list (AgentService.computeWorkspacePaths even enforces this on create), so the column should be NOT NULL and the rowMapper's ?? [] should disappear.

Related References

• API Design Guidelines § Rule C — DTO derivation rules
• Database Patterns § Column Nullability and Defaults — column-level decision
• DataApi in Main § Row → Entity Mapping — nullsToUndefined, T | null preservation
• Zod issue #4799 — .partial() and .default() interaction
• SQLite ALTER TABLE limitations — why DB DEFAULT changes are painful
• drizzle-team/drizzle-orm#2489 — drizzle-kit's unsupported-SQLite-ALTER comment doesn't name the affected table/column