red/.planning/phases/14-multi-signer-schema/14-01-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
14-multi-signer-schema	01	execute	1		teressa-copeland-homes/src/lib/db/schema.ts teressa-copeland-homes/drizzle/0010_*.sql	true	MSIGN-08	truths artifacts key_links Existing single-signer documents continue to prepare, send, and sign with zero code changes SignatureFieldData has an optional signerEmail field for field ownership routing signingTokens has a nullable signerEmail column for per-signer token identity documents has a signers JSONB column typed as { email: string; color: string }[] documents has a nullable completionTriggeredAt timestamp column for race-safe completion Drizzle migration 0010 applies cleanly with no data loss path provides contains teressa-copeland-homes/src/lib/db/schema.ts Multi-signer schema additions signerEmail path provides contains teressa-copeland-homes/drizzle/0010_*.sql Drizzle-generated migration SQL signer_email from to via pattern teressa-copeland-homes/src/lib/db/schema.ts teressa-copeland-homes/drizzle/0010_*.sql drizzle-kit generate ALTER TABLE

Add multi-signer schema columns to the database — signerEmail on SignatureFieldData interface, signerEmail column on signingTokens, signers JSONB and completionTriggeredAt columns on documents — via a single additive Drizzle migration. All new columns are nullable; all existing single-signer behavior is unchanged.

Purpose: Establishes the data foundation that Phases 15 (backend) and 16 (UI) build on. Without these columns, field ownership routing, per-signer tokens, and race-safe completion detection cannot be implemented.

Output: Updated schema.ts with new types/columns + generated migration 0010_*.sql applied to the database.

<execution_context> @$HOME/.claude/get-shit-done/workflows/execute-plan.md @$HOME/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/phases/14-multi-signer-schema/14-CONTEXT.md @teressa-copeland-homes/src/lib/db/schema.ts

From teressa-copeland-homes/src/lib/db/schema.ts:

// Current SignatureFieldData — signerEmail will be added here
export interface SignatureFieldData {
  id: string;
  page: number;
  x: number;
  y: number;
  width: number;
  height: number;
  type?: SignatureFieldType;
}

// Current getFieldType pattern — signerEmail helper will follow this pattern
export function getFieldType(field: SignatureFieldData): SignatureFieldType {
  return field.type ?? 'client-signature';
}

// Current signingTokens — signerEmail column will be added
export const signingTokens = pgTable('signing_tokens', {
  jti: text('jti').primaryKey(),
  documentId: text('document_id').notNull()
    .references(() => documents.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  expiresAt: timestamp('expires_at').notNull(),
  usedAt: timestamp('used_at'),
});

// Current documents — signers JSONB and completionTriggeredAt will be added
export const documents = pgTable("documents", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text("name").notNull(),
  // ... existing columns ...
  signedAt: timestamp("signed_at"),
});

// JSONB typed column pattern (already established):
signatureFields: jsonb("signature_fields").$type<SignatureFieldData[]>(),
textFillData: jsonb("text_fill_data").$type<Record<string, string>>(),
emailAddresses: jsonb("email_addresses").$type<string[]>(),

Task 1: Add multi-signer types and columns to schema.ts teressa-copeland-homes/src/lib/db/schema.ts - teressa-copeland-homes/src/lib/db/schema.ts (current schema — must see exact structure before editing) - .planning/phases/14-multi-signer-schema/14-CONTEXT.md (locked decisions D-01 through D-07) Edit `teressa-copeland-homes/src/lib/db/schema.ts` to add the following, in order:

1. Add signerEmail?: string to SignatureFieldData interface (per D-04): After the existing type?: SignatureFieldType; line, add:

  signerEmail?: string; // Optional — absent = legacy single-signer or agent-owned field

2. Add getSignerEmail helper function immediately after the existing isClientVisibleField function:

/**
 * Safe signer email reader — returns the field's signerEmail or a fallback.
 * Legacy single-signer documents have no signerEmail on their fields;
 * this coalesces to the fallback (typically the document's single recipient email).
 * ALWAYS use this instead of reading field.signerEmail directly.
 */
export function getSignerEmail(field: SignatureFieldData, fallbackEmail: string): string {
  return field.signerEmail ?? fallbackEmail;
}

3. Add DocumentSigner interface immediately before the documents table definition (per D-01):

/** Shape of each entry in documents.signers JSONB array. */
export interface DocumentSigner {
  email: string;
  color: string;
}

Note: Per user decision D-01, this is { email: string; color: string } only — NOT the expanded shape from ARCHITECTURE.md that included name/tokenJti/signedAt. Those fields are NOT part of Phase 14.

4. Add two columns to the documents table definition: After the existing signedAt: timestamp("signed_at"), line, add:

  /** Per-signer list with assigned colors. NULL = legacy single-signer document. */
  signers: jsonb("signers").$type<DocumentSigner[]>(),
  /** Atomic completion guard — set once by the last signer's handler. NULL = not yet completed. */
  completionTriggeredAt: timestamp("completion_triggered_at"),

5. Add signerEmail column to the signingTokens table definition (per D-02): After the existing documentId line and before createdAt, add:

  /** Signer this token belongs to. NULL = legacy single-signer token. */
  signerEmail: text('signer_email'),

Do NOT:

• Add any new values to auditEventTypeEnum — those are Phase 15 scope (D-06 says Phase 14 is schema-only, no sign handler changes; new audit events belong with the handlers that fire them)
• Add Partially Signed to documentStatusEnum (per D-05)
• Change any existing function signatures or column definitions
• Touch any file other than schema.ts cd /Users/ccopeland/temp/red/teressa-copeland-homes && npx tsc --noEmit 2>&1 | head -20 <acceptance_criteria>
  • grep "signerEmail?: string" src/lib/db/schema.ts returns a match inside SignatureFieldData
  • grep "export function getSignerEmail" src/lib/db/schema.ts returns a match
  • grep "export interface DocumentSigner" src/lib/db/schema.ts returns a match
  • grep 'signers: jsonb("signers")' src/lib/db/schema.ts returns a match
  • grep 'completionTriggeredAt: timestamp("completion_triggered_at")' src/lib/db/schema.ts returns a match
  • grep "signerEmail: text('signer_email')" src/lib/db/schema.ts returns a match inside signingTokens
  • grep "Partially" src/lib/db/schema.ts returns NO match (D-05)
  • npx tsc --noEmit exits 0 (no type errors) </acceptance_criteria> schema.ts has all 4 additions (interface field, helper function, 2 document columns, 1 signingTokens column) with zero changes to existing definitions; TypeScript compiles cleanly

Task 2: Generate Drizzle migration and apply it teressa-copeland-homes/drizzle/0010_*.sql - teressa-copeland-homes/src/lib/db/schema.ts (just-modified schema — confirm changes are present) - teressa-copeland-homes/drizzle.config.ts (migration output dir and DB config) - teressa-copeland-homes/drizzle/0009_luxuriant_catseye.sql (latest migration — confirm 0010 is next) **1. Generate the migration (per D-07):** ```bash cd teressa-copeland-homes && npx drizzle-kit generate ``` This will produce `drizzle/0010_*.sql` containing ALTER TABLE statements for the 3 new columns (signer_email on signing_tokens, signers and completion_triggered_at on documents). The SignatureFieldData interface change does NOT produce SQL — it is a TypeScript-only change (the JSONB column is schema-less at the DB level).

2. Inspect the generated SQL to verify it contains exactly:

• ALTER TABLE "signing_tokens" ADD COLUMN "signer_email" text;
• ALTER TABLE "documents" ADD COLUMN "signers" jsonb;
• ALTER TABLE "documents" ADD COLUMN "completion_triggered_at" timestamp;
• NO DROP COLUMN statements
• NO ALTER TYPE statements
• NO backfill UPDATE statements

3. Apply the migration:

cd teressa-copeland-homes && npx drizzle-kit migrate

4. Verify migration applied by checking drizzle-kit output shows success.

Do NOT:

• Hand-edit the generated SQL file — Drizzle generates it; we commit it as-is
• Run any backfill queries
• Add ALTER TYPE statements for audit events (Phase 15 scope) cd /Users/ccopeland/temp/red/teressa-copeland-homes && ls drizzle/0010_.sql && grep -l "signer_email" drizzle/0010_.sql && grep -l "completion_triggered_at" drizzle/0010_*.sql <acceptance_criteria>
  • A file matching drizzle/0010_*.sql exists
  • grep "signer_email" drizzle/0010_*.sql returns a match
  • grep "signers" drizzle/0010_*.sql returns a match for the JSONB column
  • grep "completion_triggered_at" drizzle/0010_*.sql returns a match
  • grep -i "DROP" drizzle/0010_*.sql returns NO match (additive only)
  • grep "audit_event_type" drizzle/0010_*.sql returns NO match (no enum changes in Phase 14)
  • drizzle/meta/_journal.json contains an entry for migration 0010 </acceptance_criteria> Migration 0010 generated with 3 additive ALTER TABLE statements (no drops, no type changes, no backfills), applied successfully to the database, and committed alongside schema.ts

After both tasks complete: 1. `npx tsc --noEmit` exits 0 — TypeScript compiles with new types 2. `drizzle/0010_*.sql` exists and contains only additive ALTER TABLE statements 3. `grep -c "signerEmail" teressa-copeland-homes/src/lib/db/schema.ts` returns 3+ (interface field, helper function param, signingTokens column) 4. No files outside schema.ts and drizzle/ were modified

<success_criteria>

• Drizzle migration 0010 applies cleanly (no errors)
• schema.ts has SignatureFieldData.signerEmail?, DocumentSigner interface, documents.signers JSONB, documents.completionTriggeredAt TIMESTAMP, signingTokens.signerEmail TEXT
• Existing single-signer code paths unaffected (all additions are nullable/optional)
• No audit event enum changes, no status enum changes, no sign handler changes
• TypeScript compiles cleanly </success_criteria>

After completion, create `.planning/phases/14-multi-signer-schema/14-01-SUMMARY.md`