Data Model Spec

# Data Model: [project name]

Database: [PostgreSQL 16 / SQLite]. [SQLite is a real production choice for
single-server apps; pick Postgres when you need concurrent writers or you're
deploying somewhere that hands you one anyway.]

## The things (plain language)

[One short paragraph per entity. What it represents in the real world, and
roughly how many you expect: 100 rows and 10 million rows want different
decisions.]

- **User**: a person with a login. Expect [hundreds].
- **Project**: a container a user owns. A user has many projects;
  a project belongs to exactly one user. Expect [tens per user].
- **Task**: one item of work inside a project. Expect [hundreds per project].

## Relationships, stated in sentences

[One line each. If you can't write the sentence, you don't know the
relationship yet, and neither does the agent.]

- A User has many Projects. A Project belongs to one User.
- A Project has many Tasks. Deleting a Project deletes its Tasks (cascade),
  because an orphaned task means nothing.
- A Task may be assigned to one User, or nobody. Deleting that User leaves
  the task unassigned (set null), it does not delete work.

## Fields per table

### users

| field         | type      | rules                                        |
|---------------|-----------|----------------------------------------------|
| id            | uuid      | primary key, generated by the database       |
| email         | text      | required, unique, lowercased before storing  |
| password_hash | text      | required. The word "hash" is load-bearing.   |
| display_name  | text      | required, 1-80 chars                         |
| created_at    | timestamp | required, set by the database, UTC           |

### projects

| field      | type      | rules                                   |
|------------|-----------|-----------------------------------------|
| id         | uuid      | primary key                             |
| user_id    | uuid      | required, references users(id)          |
| name       | text      | required, unique per user, not globally |
| archived   | boolean   | required, default false                 |
| created_at | timestamp | required, UTC                           |

### tasks

| field       | type      | rules                                             |
|-------------|-----------|---------------------------------------------------|
| id          | uuid      | primary key                                       |
| project_id  | uuid      | required, references projects(id), cascade delete |
| assignee_id | uuid      | optional, references users(id), set null          |
| title       | text      | required, 1-200 chars                             |
| status      | text      | required, one of: todo, doing, done. Enforced.    |
| due_on      | date      | optional. A date, not a timestamp: "Friday" has   |
|             |           | no time zone.                                     |
| created_at  | timestamp | required, UTC                                     |

## SQL sketch

```sql
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  display_name  TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  archived   BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, name)
);

CREATE TABLE tasks (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
  title       TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 200),
  status      TEXT NOT NULL DEFAULT 'todo'
              CHECK (status IN ('todo', 'doing', 'done')),
  due_on      DATE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_tasks_project ON tasks (project_id);
CREATE INDEX idx_projects_user ON projects (user_id);
```

## Rules the schema can't express

[Invariants the agent must enforce in application code. Number them so
tests can reference them.]

1. A task's assignee must belong to... [state your rule: same account?
   any user? This is a business decision, decide it here.]
2. Archived projects are read-only: no new tasks, no status changes.
3. [Add yours. Every unstated rule ships as a bug.]

## Sensitive fields

- `users.email`: personal data. Never log it, never put it in a URL.
- `users.password_hash`: never leaves the database. Not in API responses,
  not in logs, not "temporarily for debugging."
- Deliberately NOT stored: [e.g. full names, addresses, payment details.
  Data you don't hold is data you can't leak.]

## Instructions to the agent

- Build exactly these tables via a migration file, not by typing SQL into
  a console. The migration is the record.
- If a feature needs a field or table not listed here, propose the schema
  change and wait for approval before writing it. The model changes by
  edit to this document, not by drift.