# Design This file fixes the parts that are easy to get wrong. Choose the language and framework that fit the person's project; nothing here depends on them. ## Data model Use the database in the `database` input. Column types are described, not dictated. | Table | Columns | | --- | --- | | `users` | `id`, `email` (unique, stored lowercase), `password_hash` (only for `password` sign-in), `created_at` | | `sessions` | `id`, `user_id`, `token_hash` (unique), `expires_at`, `created_at` | | `sign_in_links` | `id`, `email`, `token_hash` (unique), `expires_at`, `used_at` (only for `email_link`) | | `workspaces` | `id`, `name`, `slug` (unique), `created_at` | | `memberships` | `workspace_id`, `user_id`, `role`, `created_at`; primary key on both ids | | `invitations` | `id`, `workspace_id`, `email`, `role`, `token_hash` (unique), `invited_by`, `expires_at`, `accepted_at`, `revoked_at` | | `invite_links` | `id`, `workspace_id`, `role`, `token_hash` (unique), `expires_at`, `max_uses`, `uses` (only if `invite_links` is on) | | `audit_entries` | `id`, `workspace_id`, `actor_id`, `action`, `target`, `created_at` (only if `audit_log` is on) | | `notes` | `id`, `workspace_id`, `title`, `body`, `created_by`, `created_at`, `updated_at` | Rules: - Store only a hash of every token. Compare hashes in constant time. - Enforce "exactly one owner" in the database: a unique index on `workspace_id` where `role` is `owner`. - Transfer ownership inside one transaction: demote the old owner, then promote the new one. - Deleting a workspace deletes its memberships, invitations, links, notes and audit entries. ## Permission matrix Test every cell. "Yes" means status 2xx, "no" means status 403. | Action | Owner | Admin | Member | Viewer | | --- | --- | --- | --- | --- | | Read notes | yes | yes | yes | yes | | Create, update, delete notes | yes | yes | yes | no | | Read member list | yes | yes | no | no | | Invite, revoke invitation | yes | yes | no | no | | Change role of admin, member, viewer | yes | yes | no | no | | Remove admin, member, viewer | yes | yes | no | no | | Change or remove the owner | no | no | no | no | | Change workspace settings | yes | yes | no | no | | Read audit log | yes | yes | no | no | | Transfer ownership | yes | no | no | no | | Delete workspace | yes | no | no | no | | Leave workspace | no | yes | yes | yes | A person who is not a member of the workspace gets 404 for every row. ## Request flow Put these checks in one place that every workspace route passes through, in this order: 1. Find the session from the cookie. No session: redirect pages to sign-in, answer API calls with 401. 2. Find the workspace from the slug in the URL. 3. Find the caller's membership in that workspace. No workspace or no membership: 404. 4. Check the action against the matrix. Not allowed: 403. 5. Run the handler with the workspace id taken from step 3, never from the request body. Keep the matrix as data, one table in code, so the tests and the checks read the same source. ## Sessions and cookies - The session cookie is `HttpOnly`, `Secure`, `SameSite=Lax`, with a random token of at least 32 bytes. - State-changing requests need a CSRF token, or a check that the `Origin` header matches the app. - Sign-in answers the same way whether or not the email address has an account. ## Email Send two messages: the sign-in link (for `email_link`) and the invitation. Put sending behind one small interface so tests can capture messages without a network. ## Configuration | Secret | Purpose | | --- | --- | | `DATABASE_URL` | Connection string for the chosen database. | | `SESSION_SECRET` | Signs CSRF tokens. At least 32 random bytes. | | `EMAIL_API_KEY` | Sends sign-in and invitation emails. Optional in development. | | `EMAIL_API_URL` | Where `send_email` posts each message. Optional in development. | | `EMAIL_FROM` | The address every email is sent from. Optional in development. | ## Sending email Send every email through one function, `send_email(to, subject, text)`. - With `EMAIL_API_URL` and `EMAIL_API_KEY` set, it makes one `POST` to `EMAIL_API_URL` with the header `Authorization: Bearer ` and the JSON body `{"from": EMAIL_FROM, "to": ..., "subject": ..., "text": ...}`. Any 2xx answer means sent. The person points the address at their email service, or at a few lines that translate for it. - With neither set, outside production, the message goes to a local outbox that tests read. It is not printed: a message can hold a link that signs someone in. In production the app refuses to start without both. ## Named here - Sign-in attempts are counted in a table, `sign_in_attempts`, with the email address and the time. - A workspace's settings are its name and its slug. - Audit actions are written like `member.invited`. ## What this spec leaves to you Where this spec names something, use that name, so that two builds of it agree. Where it does not, the choice is yours. List what you chose in the README.