Feature: Database Schema

Version: 1.3.1 Last Reviewed: 2026-02-12 Status: Approved

User Story

As a developer, I have a well-designed database schema that supports all MVP features with proper indexes, constraints, and audit trails so the application is performant and data integrity is guaranteed.

Overview

PostgreSQL 16 with Prisma 7.x ORM (Driver Adapters via @prisma/adapter-pg). The schema is designed around the core entities: users, children, and four tracking types (feedings, diapers, sleep, notes). Multi-caregiver access is modeled via a join table. All changes are logged to an append-only audit table.

Entity Relationship Diagram

User ──< Session
User ──< ApiToken
User ──< ChildAccess >── Child
User ──< AuditLog
Child ──< Feeding
Child ──< Diaper
Child ──< Sleep
Child ──< Note
Child ──< ShareLink

Models

User

Core identity. Name + email + password auth.

Column	Type	Constraints	Notes
id	UUID	PK, auto-generated
email	VARCHAR	UNIQUE, NOT NULL	Lowercase, trimmed
name	VARCHAR(100)	NOT NULL	Display name, trimmed. 1-100 chars. Shown in access lists, timeline, multi-caregiver UI.
password	VARCHAR	NOT NULL	bcrypt hash (12 rounds)
timezone	VARCHAR	NOT NULL, default "America/New_York"	IANA timezone string, used for "today" queries
day_start_time	TIME	NOT NULL, default 07:00	PostgreSQL TIME type (no date component). Configurable day boundary for dashboard/timeline "today" calculations.
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_at	TIMESTAMPTZ	NOT NULL, auto

Session

Web/app login sessions. Token-based.

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users, CASCADE
token	VARCHAR	UNIQUE, NOT NULL	crypto.randomBytes(32).hex
expires_at	TIMESTAMPTZ	NOT NULL	30 days from last use (rolling)
last_verified_at	TIMESTAMPTZ	NOT NULL	Updated hourly on authenticated requests
created_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: token (lookup on every authenticated request)

Multiple Sessions: No unique constraint on user_id — a user can have multiple active sessions (e.g., iPhone + iPad). Each login creates a new session. Logout invalidates only that session. Password change invalidates ALL other sessions (see user-profile.md). Expired sessions (>30 days since last_verified_at) are cleaned up lazily — deleted when encountered during auth checks.

ApiToken

Long-lived tokens for programmatic access (Home Assistant, scripts).

Column	Type	Constraints	Notes
id	UUID	PK
user_id	UUID	FK → users, CASCADE
token	VARCHAR	UNIQUE, NOT NULL	SHA-256 hash of the plain token
name	VARCHAR	NOT NULL, default "API Token"	User-provided label
last_used	TIMESTAMPTZ	NULL	Updated on each use
created_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: token (lookup on every authenticated request)

Child

Baby profile. All tracking data hangs off this.

Column	Type	Constraints	Notes
id	UUID	PK
name	VARCHAR	NOT NULL
date_of_birth	DATE	NOT NULL	Date only, no time component
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_at	TIMESTAMPTZ	NOT NULL, auto

ChildAccess

Join table for multi-caregiver access. Determines who can see/edit a child's data.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
user_id	UUID	FK → users, CASCADE
role	VARCHAR	NOT NULL, default "owner"	"owner" or "caregiver"
granted_at	TIMESTAMPTZ	NOT NULL, auto

Unique constraint: (child_id, user_id) - one access record per user per child

ShareLink

Invitation tokens for sharing child access.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
token	VARCHAR	UNIQUE, NOT NULL	crypto.randomBytes(32).hex
created_by	UUID	NOT NULL	User who generated the link
used_at	TIMESTAMPTZ	NULL	When the link was accepted
used_by	UUID	NULL	User who accepted the link
created_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: token (lookup when accepting a share link)

Feeding

Breast or bottle feeding events.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
timestamp	TIMESTAMPTZ	NOT NULL	When the feeding occurred
type	VARCHAR	NOT NULL	"breast" or "bottle"
started_side	VARCHAR	NULL	"left" or "right" (breast only - which side feeding began on)
both_sides	BOOLEAN	NOT NULL, default false	True when fed from both sides (breast only)
duration_minutes	INT	NULL	Optional, manual entry. Max 180.
amount_ml	FLOAT	NULL	Stored in mL, displayed as oz (1 oz ≈ 29.5735 mL). Max 500. Bottle only.
created_by	UUID	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_by	UUID	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: (child_id, timestamp) - chronological queries per child Constraints: Application-level: started_side required when type=breast, amount_ml only when type=bottle. Max: amount_ml ≤ 500, duration_minutes ≤ 180.

Diaper

Diaper change events.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
timestamp	TIMESTAMPTZ	NOT NULL
type	VARCHAR	NOT NULL	"pee", "poop", or "both"
created_by	UUID	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_by	UUID	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: (child_id, timestamp)

Sleep

Sleep session events with start and end times.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
start_time	TIMESTAMPTZ	NOT NULL
end_time	TIMESTAMPTZ	NULL	NULL = active sleep timer running. Must be after start_time when set.
duration_minutes	INT	NULL	Calculated when end_time is set: Math.round((end - start) / 60000)
created_by	UUID	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_by	UUID	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: (child_id, start_time) Constraints: Application-level: end_time > start_time

Note

Free-form catch-all for events not yet supported as dedicated features.

Column	Type	Constraints	Notes
id	UUID	PK
child_id	UUID	FK → children, CASCADE
timestamp	TIMESTAMPTZ	NOT NULL
category	VARCHAR	NOT NULL	"pumping", "health", "medication", "other"
text	TEXT	NOT NULL	Free-form content
created_by	UUID	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, auto
updated_by	UUID	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL, auto

Indexes: (child_id, timestamp), (child_id, category)

AuditLog

Append-only log of every data change for debugging and accountability.

Column	Type	Constraints	Notes
id	UUID	PK
entity_type	VARCHAR	NOT NULL	"feeding", "diaper", "sleep", "note", "child", "user", "session", "api_token", "share_link", "child_access"
entity_id	UUID	NOT NULL	ID of the changed entity
action	VARCHAR	NOT NULL	"create", "update", "delete"
changes	JSONB	NOT NULL	Before/after snapshot or full entity on create/delete
user_id	UUID	FK → users, CASCADE	Who made the change
timestamp	TIMESTAMPTZ	NOT NULL, auto

Indexes: (entity_type, entity_id), user_id

changes JSONB Structure:

• CREATE: Full snapshot of the new entity (all fields except id, created_at, updated_at)
• UPDATE: { "before": { ...changed fields only }, "after": { ...changed fields only } } — only fields that actually changed, not the full entity
• DELETE: Full snapshot of the deleted entity (for recovery reference)
• Sensitive fields NEVER stored: Passwords, tokens, hashes are redacted. For password changes: { "before": { "password": "[redacted]" }, "after": { "password": "[redacted]" } }
• Auth events: Login/logout/register are logged as session create/delete with changes: { "user_id": "uuid" } (no token values)

Design Decisions

1. UUIDs everywhere: Prevents ID enumeration attacks. Slightly larger than integers but acceptable for this scale.
2. TIMESTAMPTZ for all times: Everything stored in UTC. Client timezone handled at query time via AT TIME ZONE.
3. Audit fields on tracking entities: created_by, created_at, updated_by, updated_at on feedings/diapers/sleep/notes. Separate AuditLog table for change history.
4. No soft deletes: Hard deletes with AuditLog capturing the deleted entity. Keeps queries simple.
5. Cascade deletes on child: Deleting a child removes all tracking data. AuditLog entries remain (user FK, not child FK).
6. ChildAccess join table: Decouples user-child relationship. Supports multiple caregivers per child and multiple children per user.
7. Share links are single-use: Once accepted, the link is marked used. Generate a new one for another caregiver.
8. API token hashing: Store SHA-256 hash, not plain token. Plain token shown only once on creation.
9. duration_minutes on Sleep is denormalized: Could be calculated from start/end, but storing it avoids repeated computation in dashboard/timeline queries. NULL when sleep timer is active (end_time is NULL).
10. Configurable day boundary: User.day_start_time (default "07:00") defines when "today" starts for dashboard/timeline. A 10PM-6AM sleep stays as one record attributed to the day whose boundary contains the start_time. Never split sleep records at boundaries.
11. Feeding amounts in mL: Stored as Float in mL for precision. Max 500 mL (~17 oz). iOS converts to oz for display (1 oz ≈ 29.5735 mL). Avoids rounding artifacts when converting back.
12. Feeding side tracking: started_side (left/right) tracks which side the feeding began on. both_sides (boolean) indicates if both sides were used. This enables reliable side alternation suggestions: always suggest the opposite of started_side, regardless of whether both sides were used.
13. Nullable Sleep.end_time: Supports two-step timer flow (start sleep → stop sleep). Active sleep sessions have end_time=NULL and duration_minutes=NULL.
14. User.name required: Display name for multi-caregiver UX. Shows "Johnny" instead of "parent@example.com" in access lists, timeline entries, and audit displays. Required at registration, 1-100 chars, trimmed.

Migration Strategy

• Initial migration creates all tables in one migration file
• Future schema changes get their own migration files
• prisma migrate dev for local development
• prisma migrate deploy in production (via deploy.sh)

Acceptance Criteria

• All tables created via Prisma migration
• All indexes present (verified via \d+ tablename in psql)
• Foreign key constraints enforce referential integrity
• Unique constraints prevent duplicate ChildAccess records
• Cascade delete on child removes all related tracking data
• AuditLog entries survive child deletion
• Schema matches this spec exactly

Test Cases

1. Migration runs cleanly: prisma migrate dev on empty DB succeeds
2. Cascade delete: Create child with feedings/diapers/sleep/notes, delete child, verify all removed
3. Unique constraint: Attempt duplicate ChildAccess (same child+user), verify error
4. Index verification: Query plan for WHERE child_id = X AND timestamp > Y uses index scan
5. Timezone handling: Insert TIMESTAMPTZ, query with different timezone, verify correct conversion

Prisma Implementation Notes

• VARCHAR(100) fields: User.name and Child.name must use @db.VarChar(100) in Prisma schema. Default String maps to TEXT (unlimited), which won't enforce the 100-char limit at DB level.
• UNIQUE columns already create indexes: PostgreSQL creates an implicit index for every UNIQUE constraint. The token columns on Session, ApiToken, and ShareLink have UNIQUE constraints, so explicit @@index([token]) is NOT needed (would be redundant). The "Indexes" notes in the model definitions above refer to the implicit indexes from UNIQUE, not additional explicit indexes.
• TIME type: day_start_time uses @db.Time(0) in Prisma for PostgreSQL TIME without timezone.

Connection Pool

• Prisma's default pool size is num_physical_cpus * 2 + 1 (typically 5-9 on an LXC container).
• When all connections are in use, new queries queue internally with a 10-second timeout (configurable via ?connection_limit=N&pool_timeout=N in DATABASE_URL).
• On timeout, Prisma throws P2024 error — the API returns 500, no crash.
• For MVP (2-3 concurrent users, single API server), the default pool is more than sufficient.
• Post-MVP: if scaling, configure explicitly in DATABASE_URL: ?connection_limit=10&pool_timeout=15.

Boundaries

• Schema supports MVP features only. Post-MVP features (growth tracking, custom categories) will require migrations.
• No database-level enum types - using VARCHAR with application-level validation for flexibility.
• No triggers or stored procedures - all logic in application layer.