Database schema design

Design the database changes for this feature.

Feature:
{{feature}}

Current database and stack:
{{current schema}}

Include:
1. The entities and how they relate, in a few lines.
2. Each new or changed table: columns with types, nullability, defaults, keys and constraints. Explain any choice that is not obvious.
3. Indexes for the queries the feature needs, naming each query.
4. How existing data and existing tables are affected, and a safe migration order (for example add, backfill, then enforce).
5. Edge cases the design must handle, such as deletions, duplicates, concurrency and time zones, and how the schema handles each.
6. Open questions, each with the assumption you made.

Use only the tables and columns I described as existing; mark everything else as new. Do not write application code.

How to use it

Describe the feature as a user would, then list the existing tables and columns it touches and the database you use. Check the migration order against your deploy process before running it.

Example

feature

Workspace owners can invite people by email to join their workspace as admin, member or viewer. Invites expire after 7 days and can be revoked. A person can belong to several workspaces. When someone accepts, they get the role from the invite.

current schema

PostgreSQL 16. users(id uuid pk, email text unique, created_at). workspaces(id uuid pk, name, owner_id references users). Right now only the owner can access a workspace; there is no membership table.