# Database Plan

Model facts the product must preserve, not every value visible on a screen. A relational database is a strong default for structured ownership, permissions, and transactional state; verify the actual access patterns.

[Kit README](README.md) · [Project Overview](Project%20Overview.md) · [PRD](Full%20PRD%20Template.md) · [Master Build Prompt](Master%20Build%20Prompt.md) · [Completion Checklist](Project%20Completion%20Checklist.md)

## Data Inventory

| Entity | Purpose | Important Fields | Relationships | Lifecycle |
| --- | --- | --- | --- | --- |
| User | identity/ownership | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |
| Device | core domain state | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |
| Session | core domain state | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |
| LocalRecord | core domain state | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |
| SyncOperation | operational or supporting state | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |
| NotificationPreference | operational or supporting state | [PRIMARY KEY + REQUIRED FIELDS] | [RELATIONSHIPS] | [RETENTION/DELETION] |

## Field Specification Template

~~~text
Entity.field: [NAME]
Type/format: [TYPE]
Required/default: [RULE]
Source of truth: [SYSTEM OR USER]
Validation: [RANGE, LENGTH, ENUM, FORMAT]
Sensitivity: [PUBLIC / INTERNAL / PERSONAL / SENSITIVE]
Index/query reason: [ACCESS PATTERN]
Retention/deletion: [RULE]
Migration note: [BACKFILL OR COMPATIBILITY]
~~~

## Relationship and Ownership Rules

- Define who or what owns every record.
- Put organization, user, device, project, or local-workspace scope directly in the model where authorization depends on it.
- Enforce required uniqueness and references in the durable store as well as the application.
- Decide how archived and deleted records behave in queries, exports, backups, and restores.
- Keep immutable audit or transaction facts separate from editable display information.

## Access Patterns

| Query | Frequency | Maximum Result | Filter/Sort | Index or Local Strategy |
| --- | --- | --- | --- | --- |
| [PRIMARY LIST] | [RATE] | [LIMIT] | [FIELDS] | [INDEX] |
| [DETAIL LOOKUP] | [RATE] | 1 | [ID + OWNER] | [UNIQUE/COMPOSITE] |
| [HISTORY/SEARCH] | [RATE] | [PAGE] | [FIELDS] | [INDEX/SEARCH ENGINE] |

## Migration and Recovery

1. Version every schema or local save format.
2. Back up before destructive migrations.
3. Write expand-and-contract changes when old and new application versions may overlap.
4. Test migration against realistic volume and malformed legacy records.
5. Verify restore time and data integrity, not only backup creation.

## Acceptance Criteria

- [ ] Every P0 flow has defined reads and writes.
- [ ] Ownership and permission scope are queryable and enforced.
- [ ] Sensitive fields have purpose, retention, and deletion rules.
- [ ] Indexes correspond to documented access patterns.
- [ ] Sample data is labeled and contains no real secrets or personal information.
- [ ] Migration, backup, restore, and deletion have tests.
- [ ] No unused database is added to a static or local-only project.