Data model and invariants

EverDuet's hardest data problem is not storing prompts. It is preserving consent and history when people invite, connect, answer, reveal, disconnect, and later form a different relationship.

Model the relationship, not “a partner field”

The canonical model is a relationship with membership history:

Relationship
  ├── RelationshipMember(user A, joinedAt, leftAt?)
  ├── RelationshipMember(user B, joinedAt, leftAt?)
  ├── PartnerInvitation(status, intended email/user)
  ├── DailyPromptAssignment(day, prompt)
  └── PromptResponse(user, prompt, relationship scope)

A partnerUserId column on a profile looks simpler, but it cannot naturally represent pending consent, ended relationships, membership history, or answers from different relationships.

The database defends concurrency

Application checks improve error messages, but only the database sees every concurrent writer.

EverDuet enforces these rules in PostgreSQL:

• one active membership per user;
• at most two active members per active relationship;
• no member may join an ended relationship;
• an ended relationship cannot be reactivated;
• accepting an invitation claims that invitation once;
• response scope is derived from relationship ownership.

The invitation acceptance transaction first conditionally claims a pending invitation. A database trigger locks the parent relationship row before counting members. Two simultaneous accepts therefore serialize on one durable authority.

-- Conceptual shape: the real migration includes full validation and errors.
SELECT id FROM "Relationship" WHERE id = relationship_id FOR UPDATE;

SELECT COUNT(*) FROM "RelationshipMember"
WHERE "relationshipId" = relationship_id
  AND "leftAt" IS NULL;

Why a trigger here is reasonable

Triggers can hide behavior, so they should not become a default domain layer. Here they protect an invariant that must hold across every writer and every race. The application still performs the same check for understandable errors; the trigger is the final guardrail.

Preserve response history by scope

One user may answer the same prompt while solo, with one relationship, and later with another. The response key therefore includes scope.

Situation	responseScopeId
No active relationship	solo
Relationship 42	relationship:42
Later relationship 91	relationship:91

This prevents a new relationship from inheriting or overwriting private history from an old one.

Assign daily prompts idempotently

A couple receives one stable assignment for each UTC day:

model DailyPromptAssignment {
  relationshipId Int
  promptId       Int
  assignedFor    DateTime

  @@unique([relationshipId, assignedFor])
}

The first assignment wins. Retries use an upsert with an empty update, so publishing or reading again cannot silently switch the conversation.

Tradeoffs

• UTC day: predictable and operationally simple, but not each couple's local midnight.
• Assignment on read: no fan-out job for every couple, but a never-active couple receives no assignment row.
• No backfill for schedule gaps: honest about missing content, but a late prompt does not repair that day's slot.
• Archived prompt remains assigned: preserves history; current reads hide it instead of substituting a different conversation.

Lifecycle fields beat destructive updates

Prompts progress through DRAFT, SCHEDULED, PUBLISHED, and ARCHIVED. Timestamps such as publishedAt and archivedAt preserve when transitions happened. Quality review snapshots the reviewed content hash, so editing approved text makes the approval stale rather than silently reusing it.

const approved = review.reviewedContentHash === hashPromptContent(prompt)

if (!approved) {
  throw new Error('Prompt content changed after quality review')
}

Practical heuristics

Use a unique key for identity

If retries should converge on one record, encode that identity in the database.

Use history for meaning

If old state affects privacy or auditability, timestamp it instead of overwriting it.

Use transactions for atomic stories

Invitation claim and membership creation either happen together or not at all.

Use triggers sparingly

Reserve them for cross-writer invariants that application code cannot make race-free.

Read the accepted relationship ADR in docs/adr-001-relationship-foundation.md, then continue to authentication and security.