# Build a fast issue tracker (replacing Linear for one company)

You are building an issue tracker for one company with two to six teams and up to 50 people. It replaces Linear for teams that use issues, statuses, priorities, labels, projects, cycles, the GitHub PR link, and Slack notifications. Build it end to end. Speed and keyboard handling matter more than features. Every list should open in under 100ms from cache and every mutation should apply on screen before the server responds.

## Stack

- One Node 22 process running Next.js (App Router) with TypeScript and Tailwind. No serverless functions and no second process.
- SQLite through `better-sqlite3` with Drizzle, WAL mode, file at `./data/app.db`. Migrations are checked into the repo and run on startup. Keep the schema portable so Postgres works later, but don't build for it now.
- Files on local disk under `./data/files/<table>/<id>/`, served by an authenticated route with short-lived signed URLs the app mints itself.
- `croner` inside the app process for cycle rollover, the Slack outbox, the email digest, and cleanup. A `job_locks` table stops two instances from running the same job.
- Sessions the app issues itself as signed httpOnly cookies. First run creates an admin from `ADMIN_EMAIL` and `ADMIN_PASSWORD`, or prompts in the browser if they're unset. Teammates join through invite links. Google sign-in is an optional add-on, never a requirement.
- Realtime: server-sent events at `/api/events`, fed by an in-memory event bus.
- Email: `nodemailer` with `SMTP_URL`. Without it, every message is written to `./data/outbox/*.eml` and printed to the log.
- UTC in the database as ISO 8601 strings, local time in the UI, `date-fns-tz` for every conversion. Never do timezone math by hand.
- Client state: TanStack Query with optimistic mutations. Markdown: `react-markdown` with `remark-gfm` and `rehype-sanitize`. Palette and pickers: `cmdk`. Keyboard: one `useHotkeys` layer with scopes (global, list, issue, modal).
- Outside services, each with a laptop fallback: a GitHub webhook (a PR can't link itself without one; the Simulate webhook button in Settings posts a signed fixture instead), a Slack incoming webhook URL per team (optional), and an SMTP relay once you go public (the outbox covers dev).

Full profile: https://buildyourown.software/deploy.md

## Running it

- Laptop: `pnpm install && pnpm dev`. No env vars. The app creates `./data` on first start and the first browser visit creates the admin.
- Public: a VPS with 1 CPU and 1 GB RAM, one A record, and `docker compose up -d`. The compose file has `app` and `caddy` (plus `backup`). Caddy terminates TLS with a certificate it gets on its own. `./data` is a named volume.
- Backups: `pnpm backup` runs `sqlite3 .backup` into `./data/backups/<date>.db` and prunes to the last 14. The `backup` service runs it nightly. Restoring is copying one file back and restarting.
- Updates: `git pull && docker compose up -d --build`. Migrations run on boot.
- Secrets: `APP_SECRET` signs sessions and file URLs and encrypts the Slack webhook URL. If unset, generate one on first run and store it in `./data/app-secret`. Same for `GITHUB_WEBHOOK_SECRET`.

## Data model

Every table has `id` (text, uuid), `created_at`, `updated_at` (text, ISO 8601 UTC like `2026-09-10T14:03:00.000Z`). SQLite types only: text, integer, real. Booleans are integer 0 or 1. Dates without a time are `YYYY-MM-DD` text. JSON columns are text holding JSON. What changes from a hosted-database draft: no `timestamptz` (ISO strings sort correctly as text, so range queries still work), no `jsonb` (parse `slack_outbox.events` in code), no `tsvector` (an FTS5 virtual table instead), and no generated column across tables (set `identifier` at insert). There is one workspace per deployment, so there is no `workspace_id`.

- `users`: `email` (unique), `name`, `avatar_url`, `role` (`admin` | `member`), `password_hash` (nullable when Google-only), `timezone` (IANA), `slack_user_id` (nullable), `digest_enabled` (integer, default 0), `active` (integer).
- `sessions`: `token_hash`, `user_id`, `expires_at`. `invites`: `email`, `token_hash`, `role`, `invited_by`, `expires_at`, `accepted_at` (nullable).
- `teams`: `name`, `key` (unique, 2 to 5 uppercase letters, e.g. `ENG`), `issue_counter` (integer, default 0), `timezone` (IANA, default `America/New_York`), `cycles_enabled`, `cycle_length_weeks` (default 2), `cycle_start_weekday` (0 = Sunday to 6 = Saturday, default 1), `cycle_cooldown_weeks` (default 0), `slack_webhook_url` (nullable, encrypted with `APP_SECRET`), `slack_channel_name` (nullable), `private` (integer).
- `team_members`: `team_id`, `user_id`. Unique on the pair.
- `workflow_states`: `team_id`, `name`, `type` (`backlog` | `unstarted` | `started` | `completed` | `canceled`), `color`, `position` (integer). Seed each team with Backlog (backlog), Todo (unstarted), In Progress (started), Done (completed), Canceled (canceled). Every team must have at least one state of each type.
- `issues`: `team_id`, `number` (integer), `identifier` (text, `ENG-123`, unique, set at insert), `title`, `description` (markdown, nullable), `state_id`, `priority` (integer: 0 none, 1 urgent, 2 high, 3 medium, 4 low), `assignee_id` (nullable), `creator_id`, `project_id` (nullable), `cycle_id` (nullable), `parent_id` (nullable, self FK), `due_date` (date text, nullable), `estimate` (integer, nullable), `sort_order` (real, for manual ordering on boards), `started_at`, `completed_at`, `canceled_at` (all nullable ISO text). Unique on (`team_id`, `number`).
- `issues_fts`: an FTS5 virtual table over `identifier`, `title`, `description`, kept in sync by triggers on insert, update, and delete of `issues`.
- `labels`: `name`, `color`, `team_id` (nullable; null means workspace-wide). Unique on (`team_id`, `name`).
- `issue_labels`: `issue_id`, `label_id`. Unique on the pair.
- `projects`: `name`, `slug` (unique), `description` (markdown), `lead_id` (nullable), `status` (`planned` | `in_progress` | `paused` | `completed` | `canceled`), `target_date` (date text, nullable), `color`.
- `project_teams`: `project_id`, `team_id`.
- `cycles`: `team_id`, `number` (integer), `name` (nullable), `starts_at` (ISO UTC text), `ends_at` (ISO UTC text), `completed_at` (nullable), `total_scope` (integer, nullable), `completed_scope` (integer, nullable). Unique on (`team_id`, `number`). At most one cycle per team where `starts_at <= now < ends_at`, compared as ISO strings.
- `comments`: `issue_id`, `author_id`, `body` (markdown), `parent_id` (nullable, one level of threading), `edited_at` (nullable).
- `issue_history`: `issue_id`, `actor_id` (nullable for system), `field` (`state` | `assignee` | `priority` | `labels` | `project` | `cycle` | `title` | `due_date` | `parent`), `from_value` (text), `to_value` (text), `source` (`user` | `github` | `cycle_rollover` | `import`).
- `pull_requests`: `issue_id`, `repo` (`owner/name`), `number` (integer), `title`, `url`, `branch`, `author_login`, `state` (`open` | `merged` | `closed`), `draft` (integer), `merged_at` (nullable). Unique on (`repo`, `number`, `issue_id`).
- `github_deliveries`: `delivery_id` (unique), `received_at`. Rows older than 24 hours are deleted by the cleanup job.
- `subscriptions`: `issue_id`, `user_id`. Unique on the pair.
- `notifications`: `user_id`, `issue_id`, `kind` (`assigned` | `mentioned` | `commented` | `state_changed` | `pr_merged`), `actor_id`, `read_at` (nullable), `snoozed_until` (nullable).
- `attachments`: `issue_id` (nullable), `comment_id` (nullable), `uploader_id`, `path` (relative to `./data/files`), `filename`, `size_bytes`, `content_type`.
- `slack_outbox`: `issue_id`, `events` (text, JSON array), `send_after`, `sent_at` (nullable), `attempts` (integer), `error` (nullable).
- `job_locks`: `name` (primary key), `locked_by`, `locked_at`, `expires_at`.

### Identifier assignment

Issue creation runs in one `better-sqlite3` transaction, which is synchronous and holds SQLite's single write lock:

```ts
const create = db.transaction((teamId, fields) => {
  const { issue_counter } = db.prepare(
    "UPDATE teams SET issue_counter = issue_counter + 1 WHERE id = ? RETURNING issue_counter"
  ).get(teamId);
  return db.prepare("INSERT INTO issues (team_id, number, identifier, ...) VALUES (?, ?, ?, ...) RETURNING *")
    .get(teamId, issue_counter, `${key}-${issue_counter}`, ...);
});
```

Never read the counter, compute in application code, then write. Twenty concurrent creates must produce ENG-124 through ENG-143, never a duplicate and never a gap. Moving an issue to another team assigns a new number from that team and records the old identifier in `issue_history` so old links can redirect.

## Screens

Left sidebar: Inbox, My Issues, then each team the user belongs to with Issues, Active cycle, Backlog, Projects, Cycles underneath. Bottom: Settings. Top bar: search input that opens the command palette, and a "New issue" button that does what `c` does.

1. **First run and sign in (`/setup`, `/login`, `/invite/[token]`).** With no users in the database every route redirects to `/setup`, which creates the admin from `ADMIN_EMAIL` and `ADMIN_PASSWORD` when set and otherwise shows a form for name, email, and password. The admin is prompted to create the first team. `/login` is email and password. An invite link lets a teammate set a name and password; links expire after 7 days. If `ALLOWED_EMAIL_DOMAINS` is set, invites to other domains are rejected. Google sign-in appears only when `GOOGLE_CLIENT_ID` and `GOOGLE_CLIENT_SECRET` are set, and only for an email that already has an account or a pending invite.
2. **Inbox (`/inbox`).** Unread notifications grouped by issue, newest first. Each row shows the actor, the kind, and the issue title. Enter opens the issue in a split pane with the inbox still on the left. `e` archives (marks read), `h` snoozes until tomorrow 9am in the user's timezone. Unread count in the sidebar.
3. **My Issues (`/my-issues`).** Issues assigned to me, grouped by state, excluding completed and canceled by default with a toggle to show them. Also tabs for Created by me and Subscribed.
4. **Team issues (`/[teamKey]/issues`).** All non-backlog issues in the team as a list grouped by state. Each row: priority icon, identifier, title, labels, project, cycle, assignee avatar, updated time. Toggle to a board with one column per state, drag and drop between columns and within a column to set `sort_order`. Display options: group by state, assignee, priority, project, or label; sort by priority, updated, created, due date; filter by any field. Persist display options per view per user in `localStorage`.
5. **Backlog (`/[teamKey]/backlog`).** Same list, only backlog-type states.
6. **Active cycle (`/[teamKey]/active`).** Issues in the current cycle with a header showing cycle number, date range in the team's timezone, days left, and a progress bar of completed vs total issues.
7. **Cycles (`/[teamKey]/cycles`).** Past, current, and upcoming cycles with scope and completed counts. Clicking one shows its issues.
8. **Projects (`/[teamKey]/projects` and `/projects/[slug]`).** List of projects with lead, status, target date, progress. Project page shows description, issues grouped by team and state, and an activity feed.
9. **Issue (`/issue/[identifier]`, e.g. `/issue/ENG-123`).** Also reachable as `/[teamKey]/issues/[identifier]`; redirect to the canonical URL. Left: title (editable inline), description (markdown, click to edit, cmd+enter to save), sub-issues list, linked pull requests with state badges, attachments, then the activity feed of history rows and comments interleaved by time. Right sidebar: state, priority, assignee, labels, project, cycle, due date, estimate, parent, subscribers, created and updated times. Every field is a picker that opens with its shortcut.
10. **Settings (`/settings`).** Members (invite by email: the app creates the link, emails it, and shows it to copy; deactivate; set admin). Teams (create, rename, change key with a warning that identifiers change, edit workflow states, cycle settings, timezone, Slack webhook URL). Labels. GitHub (the webhook URL and secret to paste into GitHub, the last 50 deliveries with status, and a Simulate webhook button). Email (whether mail is going to SMTP or the outbox). Import from Linear CSV. Export CSV per team.

## Keyboard and command palette

One global hotkey layer with scopes. When a modal or picker is open, list shortcuts are inactive. Two-key sequences like `g i` wait up to 800ms for the second key.

Global:

- `cmd+k` opens the command palette
- `c` opens the new issue modal
- `/` focuses search
- `?` shows the shortcut sheet
- `g i` inbox, `g m` my issues, `g b` backlog of the current team, `g a` active cycle, `g p` projects
- `esc` closes whatever is open, in order: picker, then modal, then side pane

List and board:

- `j` / `k` and arrow keys move the cursor. The cursor is a visible row highlight and survives refetches.
- `x` toggles selection on the cursor row. `shift+j` / `shift+k` extend the selection. `cmd+a` selects all visible rows.
- `enter` or `o` opens the issue. `space` peeks it in a side pane without leaving the list.
- `s` state picker, `a` assignee picker, `i` assigns to me, `p` priority picker, `l` labels picker, `shift+p` project picker, `shift+c` cycle picker, `d` due date picker
- `cmd+shift+,` copies the identifier. `cmd+shift+.` copies the URL. `cmd+c` copies the identifier and title as a markdown link.
- `backspace` deletes selected issues after a confirm dialog. `cmd+z` undoes the last mutation.
- On the board, `h` / `l` move the cursor between columns and `cmd+shift+arrow` moves the selected issue to the next or previous state.

Issue page:

- Same field shortcuts as the list, applied to the open issue
- `e` edits the description, `r` focuses the comment box, `cmd+enter` submits either
- `shift+s` toggles subscription
- `[` / `]` go to the previous and next issue in the list you came from
- `cmd+shift+.` copies the git branch name (see the GitHub section)

Command palette (`cmd+k`):

- A `cmdk` list with sections: Issues, Actions, Navigation, People, Projects, Cycles.
- Typing an identifier like `eng-12` or `ENG-12` jumps to it on enter.
- Typing anything else searches issues by title through `issues_fts` with prefix matching on the last word and shows the top 10.
- Actions include every list shortcut, navigation, "create issue", "toggle theme", and "copy branch name". They apply to the current selection or the open issue. Show the shortcut key next to each action.
- If the palette is opened with a selection, put "Change state", "Assign", and "Set priority" at the top.

Pickers (state, assignee, priority, labels, project, cycle):

- Open instantly with the filter input focused. Arrow keys or typed prefix to move, enter to apply, esc to cancel.
- Show the current value checked. The labels picker is multi-select with space to toggle.
- Numbers 0 to 4 in the priority picker set priority directly. The assignee picker lists team members first, then everyone else.
- Applying to a multi-selection shows "3 issues" in the picker header.

## Optimistic updates and realtime

- Every mutation applies to the client cache first, then sends the request with a client-generated `mutation_id`. On failure, roll back and show a toast with the error and a Retry button.
- Issue creation inserts a placeholder with a temporary id and identifier `ENG-?`; replace it when the server returns. The create modal closes immediately.
- Bulk mutations send one request with an array of issue ids and return the updated rows.
- Realtime: `GET /api/events` is an SSE stream. After every commit that touches `issues`, `comments`, `issue_history`, or `notifications`, the server publishes `{ id, table, op, rows, actor_id, mutation_id }` on an in-memory bus, and the SSE route forwards it to every open connection that can see the team. The server keeps the last 500 events in memory; a client reconnects with `Last-Event-ID` and gets the gap replayed, or refetches if the gap is too old. On an event whose `mutation_id` matches a pending optimistic update, ignore it. Otherwise patch the cache in place.
- `cmd+z` undoes the last mutation in the current session by applying the inverse (state back, assignee back, and so on). Keep a stack of the last 20.
- Lists render from cache instantly on navigation; refetch in the background. Paginate at 200 issues per list with virtualization (`@tanstack/react-virtual`).

## Markdown, mentions, and references

- Descriptions and comments are markdown. Render with GFM (tables, task lists, strikethrough) and syntax-highlighted code blocks. Sanitize output.
- `@name` in the editor opens a people picker and inserts `@[Dan Wolchonok](user:uuid)`. Rendering shows the name; the mentioned user gets subscribed and a `mentioned` notification.
- Bare identifiers like `ENG-123` in any markdown auto-link to the issue and show its title on hover. Do not link inside code blocks or code spans.
- Pasting an image posts it to `/api/upload`, which writes it to `./data/files/attachments/<id>/<filename>` and inserts `![filename](attachment:<id>)`. The renderer swaps `attachment:<id>` for a fresh signed URL from `/api/files/:id`. Drag and drop files onto the description or comment box does the same. Max 25MB per file.
- Comments support one level of replies. Editing a comment stamps `edited_at` and shows "(edited)".
- Task list checkboxes in the description are toggleable in the rendered view and save on click.

## Subscriptions, notifications, and email

- Auto-subscribe: the creator, anyone assigned, anyone who comments, anyone mentioned.
- Create a notification for each subscriber other than the actor on: assignment (to the assignee), comment, mention, state change to completed or canceled, PR merged.
- Coalesce: if a user has an unread notification of the same kind on the same issue, update it instead of adding a second one.
- Email through `nodemailer`: invite links, and a daily digest of unread notifications for users with `digest_enabled` (the `email-digest` job, 08:00 in each user's timezone). With `SMTP_URL` unset, write each message to `./data/outbox/<timestamp>-<to>.eml` and log the invite link, so a laptop needs no mail account.

## Jobs and the lock table

`croner` schedules these inside the app process: `cycles` hourly, `slack-outbox` every 30 seconds, `email-digest` hourly (sends to users whose local time is 08:00), `cleanup` daily (delivery ids older than 24 hours, expired sessions and invites, orphaned files). Before a job runs it claims its `job_locks` row in one statement: insert, or update where `expires_at < now`, setting `locked_by` to the process id plus a random suffix and `expires_at` to now plus the job's timeout. If the statement changes no row, skip this tick. Release the lock in `finally`. Every job commits per team (or per outbox row) in its own transaction and checks state before acting, so a process killed mid-job leaves at most one partial unit and the next tick finishes it without repeating work. Run every job once at startup too, so a laptop closed over the weekend catches up.

## Cycles (job `cycles`, hourly)

For each team with `cycles_enabled`:

1. Compute "now" in the team's timezone with `date-fns-tz`.
2. If there is no active or upcoming cycle, create cycle 1 starting at 00:00 on the next `cycle_start_weekday` in the team's timezone, ending `cycle_length_weeks` later at 00:00. Store both as UTC ISO strings. Always keep one upcoming cycle after the active one.
3. If the active cycle's `ends_at <= now`: set `completed_at`, compute `total_scope` (issues in the cycle) and `completed_scope` (issues in a completed state), then move every issue whose state type is not `completed` or `canceled` into the next cycle. Write an `issue_history` row per moved issue with `source = cycle_rollover`. If `cycle_cooldown_weeks > 0`, the next cycle starts after the cooldown. Do all of this for one team in one transaction.
4. Handle DST: a two-week cycle starting Monday 00:00 in `America/Los_Angeles` that crosses a DST change still ends on a Monday at 00:00 local, so its UTC length is 13 days 23 hours or 14 days 1 hour. Compute `ends_at` by adding weeks in local time, then converting to UTC.

Moving an issue to a completed state while it's in a cycle keeps it in that cycle. Moving an issue to a cycle that has already completed is not allowed.

## GitHub integration (`POST /api/webhooks/github`)

This is the one outside service the tracker can't do without: a PR can't link itself. Use a plain repository or organization webhook (GitHub Settings > Webhooks), not a GitHub App. Point it at `https://<your-domain>/api/webhooks/github`, content type JSON, events `pull_request` and `push`, secret `GITHUB_WEBHOOK_SECRET`. The app only receives; it never calls GitHub. On a laptop with no public URL, the Simulate webhook button in Settings picks a payload from `fixtures/github/`, signs it, and posts it to the same route, so the whole flow is testable offline.

1. Verify `X-Hub-Signature-256` with HMAC SHA-256 over the raw body using `GITHUB_WEBHOOK_SECRET`. Reject with 401 on mismatch. Respond 200 to events you don't handle.
2. Deduplicate on `X-GitHub-Delivery` through the `github_deliveries` table. Skip repeats.
3. Extract identifiers from, in order: the branch name (`head.ref`), the PR title, the PR body. The regex is `/\b([A-Z]{2,5})-(\d+)\b/gi`, applied to the branch after replacing `/` and `_` with spaces. `dan/eng-123-fix-login` matches `ENG-123`. Only keep matches where a team with that key exists. Also detect magic words: `close`, `closes`, `closed`, `fix`, `fixes`, `fixed`, `resolve`, `resolves`, `resolved` followed by an identifier in the title or body; those identifiers get the auto-close behavior. Identifiers found only in the branch name or without a magic word are linked but only auto-move on merge if `AUTO_CLOSE_ON_MERGE_WITHOUT_MAGIC_WORD` is true (default true).
4. On `pull_request` `opened`, `reopened`, `edited`, `synchronize`, `ready_for_review`, `converted_to_draft`: upsert a `pull_requests` row per matched issue on (`repo`, `number`, `issue_id`) with title, url, branch, author, draft, and `state = open`. If the issue's state type is `backlog` or `unstarted` and the PR is not a draft, move it to the team's first `started` state with `source = github`.
5. On `pull_request` `closed` with `merged = true`: set `state = merged`, `merged_at`, and move each linked issue to the team's first `completed` state with `source = github`. Skip issues already completed or canceled. Notify subscribers with `pr_merged`.
6. On `pull_request` `closed` with `merged = false`: set `state = closed`. Do not change the issue.
7. On `push` to a branch (not a PR): if the branch name matches an identifier and the issue is in a backlog or unstarted state, move it to started. This is what makes the issue move when someone starts work before opening the PR.
8. Show linked PRs on the issue page with a GitHub icon, repo and number, title, and a badge for open, draft, merged, or closed.

Also expose a "Copy git branch name" action (`cmd+shift+.` on the issue, and in the palette) that produces `<username>/<identifier-lowercase>-<slugified-title-max-40-chars>`, e.g. `dan/eng-123-fix-login-redirect`.

## Slack notifications

Optional. Each team can store a Slack incoming webhook URL (just a URL, no Slack app to install). Post a Block Kit message when an issue in that team is created, changes state, is assigned, or receives a comment. Teams without a URL enqueue nothing.

- Format: a header line with the identifier and title as a link, then a context line like "Dan moved to In Progress" or "Priya commented: first 200 characters". Include assignee and priority in a fields block.
- Coalesce: buffer events per issue for 30 seconds in `slack_outbox` and send one message describing all changes. The `slack-outbox` job drains rows where `send_after <= now` and `sent_at` is null.
- Retry up to 3 times on non-2xx with backoff. Never block the user's mutation on Slack.
- Users can turn off Slack posting for comment events per team in settings.

## Search

- `GET /api/search?q=` returns up to 20 issues from `issues_fts` ordered by `bm25()`, with `*` appended to the last term for prefix matching, plus exact identifier match at the top. Under 50ms on 20,000 issues.
- The palette and the `/` search box both use it.

## Import and export

- Import a Linear CSV export per team. Map columns: `ID` → identifier (keep the number, set `issue_counter` to the max), `Title`, `Description`, `Status` → workflow state by name (create missing ones with a guessed type), `Priority` (Urgent, High, Medium, Low, No priority), `Assignee` → user by name or email, `Labels` (comma-separated), `Project`, `Cycle Number`, `Created`, `Completed`. Write one `issue_history` row per import with `source = import`. Show a preview of the first 20 rows with the mapping before committing.
- Export a team's issues as CSV with the same columns, plus a second CSV of comments with `identifier`, `author`, `created_at`, `body`.

## API

All routes are under `/api`, JSON in and out, authenticated by session cookie except the webhook, login, setup, and invite routes. Validate every body with zod. Return 400 with field errors, 403 for private teams the user isn't in, 404 for missing rows.

- `POST /setup` (only while there are no users), `POST /auth/login`, `POST /auth/logout`, `GET /invite/:token`, `POST /invite/:token` (sets name and password, creates the session).
- `POST /invites` (admin) creates an invite, emails the link, and returns it. `DELETE /invites/:id`.
- `POST /issues` creates one issue. Body: `team_id`, `title`, optional `description`, `state_id`, `priority`, `assignee_id`, `label_ids`, `project_id`, `cycle_id`, `parent_id`, `due_date`, `mutation_id`. Returns the row with `identifier`.
- `PATCH /issues` updates many. Body: `ids: string[]`, `mutation_id`, plus any subset of the fields above. Returns the updated rows. Writes one `issue_history` row per changed field per issue.
- `DELETE /issues` with `ids`. Soft delete is not needed; hard delete and cascade comments, labels, subscriptions, history, and files on disk.
- `GET /issues?team=ENG&view=active|backlog|all&cursor=` returns up to 200 issues with labels, assignee, project, and cycle embedded. Sort by `sort_order` then `created_at`.
- `POST /issues/:identifier/comments`, `PATCH /comments/:id`, `DELETE /comments/:id`.
- `POST /issues/:identifier/move` with `team_id` reassigns the issue to another team, allocates a new number, and records the old identifier. `GET /issue/OLD-1` redirects to the new one.
- `GET /search?q=` as described above.
- `POST /upload` accepts multipart, writes the file under `./data/files/attachments/<id>/`, and returns `{ id, filename, size_bytes, content_type, url }` where `url` is signed. `GET /files/:id?exp=&sig=` checks the session, the expiry (15 minutes), and the HMAC over `id` and `exp`, then streams the file.
- `GET /notifications`, `POST /notifications/read` with `ids`, `POST /notifications/snooze` with `ids` and `until`.
- `GET /teams`, `POST /teams`, `PATCH /teams/:id`, plus `/teams/:id/states` for workflow state CRUD. Deleting a state requires a replacement state id and moves every issue there.
- `GET /projects`, `POST /projects`, `PATCH /projects/:id`.
- `GET /teams/:id/cycles`, `POST /teams/:id/cycles` (create the next upcoming cycle by hand).
- `POST /import/linear` with a CSV file and a team id. `GET /export/:teamKey.csv`.
- `POST /webhooks/github`, `POST /webhooks/github/simulate` (admin, body `{ fixture }`), `GET /events` (SSE), `POST /jobs/:name/run` (admin, runs a job now through the same lock).

Every mutation route returns the full updated rows so the client cache can be patched without a refetch.

## Edge cases to handle

- Changing a team's `key` rewrites `identifier` for every issue in the team. Keep an `identifier_aliases` table (`old_identifier`, `issue_id`) so old links redirect, and rewrite bare references in descriptions and comments in a background job.
- Deleting a user is not allowed. Deactivate instead. Deactivated users stay as assignee and author on old rows but don't show in pickers. Deactivating deletes their sessions.
- A sub-issue can't be its own ancestor. Reject cycles in `parent_id` with 400.
- Completing a parent does not complete its children. The parent shows "3 of 5 sub-issues done".
- Priority sort order is urgent, high, medium, low, then none. Store none as 0 and sort with `NULLS LAST` semantics by mapping 0 to 5 in the query.
- Due dates are stored as plain dates with no time component. "Overdue" means `due_date < today` in the viewing user's timezone, taken from the browser and stored on the user row on sign-in.
- The GitHub webhook can arrive before the issue exists (someone names a branch for an issue they haven't created). Store the PR in a `pending_pr_links` table and link it when an issue with that identifier is created.
- Two users editing the same description: last write wins, but show a banner "Dan updated this description while you were editing" if `updated_at` changed under you, with a diff.
- Deleting a cycle moves its issues to no cycle. Deleting the active cycle is not allowed.
- The Slack webhook URL is a secret. Encrypt it at rest with `APP_SECRET` and show only the channel name in the UI.
- Two app containers pointed at the same `./data` volume must not double-run jobs; the `job_locks` row is what prevents it. Don't rely on "there's only one process".

## Performance budget

- Issue list first paint from cache under 100ms. Navigation between lists never shows a spinner if the list was loaded before.
- `GET /issues` for a 200-row page under 150ms on a database with 20,000 issues. Add indexes on (`team_id`, `state_id`), (`assignee_id`), (`cycle_id`), (`project_id`), and (`team_id`, `sort_order`).
- Every keyboard action gives visual feedback within one frame. Pickers open synchronously from already-loaded data.
- Realtime patches must not cause the list to re-sort under the cursor. Apply sort changes on the next navigation or when the user presses `r` to refresh.
- Bundle: the issue list route ships under 250KB of JS gzipped. Lazy-load the markdown editor, the syntax highlighter, and the import screen.

## Non-goals

Do not build: roadmaps, initiatives, SLAs, customer requests, Asks, Zendesk or Intercom sync, AI triage, thread summaries, analytics dashboards, mobile apps, SSO or SAML, multi-workspace, billing, public issue pages. No hosted services beyond a GitHub webhook, a Slack incoming webhook URL, and an SMTP relay. If you're tempted, leave a TODO comment.

## Acceptance criteria

1. The admin invites `priya@example.com`, the invite email lands in the outbox with a link, and following it creates a member who can sign in. With `ALLOWED_EMAIL_DOMAINS=example.com`, an invite to `x@other.com` is rejected with 400. The admin creates team ENG and the first issue is ENG-1.
2. Twenty concurrent `POST /api/issues` requests for the same team produce identifiers ENG-2 through ENG-21 with no duplicates and no gaps.
3. Creating an issue with `c` shows it in the list before the server responds. If the server returns 500, the row disappears and a toast with Retry appears.
4. Changing an issue's state in one browser session appears in a second session within two seconds without a refresh, delivered over `/api/events`.
5. Selecting three issues with `x` and pressing `a` then choosing a user assigns all three, writes three `issue_history` rows, and creates three `assigned` notifications.
6. `cmd+k`, typing `eng-7`, enter opens `/issue/ENG-7`. Typing `login redirect` lists issues whose title matches, ranked by relevance.
7. A description containing a GFM table, a fenced code block with `ENG-3` inside it, and a bare `ENG-3` outside it renders the table, leaves the code block text unlinked, and links the bare reference.
8. A `pull_request` `opened` webhook with `head.ref = "dan/eng-15-fix-login"` and a valid signature links the PR to ENG-15 and moves it from Todo to In Progress. The same delivery id sent twice is processed once. An invalid signature returns 401. The Simulate webhook button produces the same result from the `pr-opened` fixture.
9. A `pull_request` `closed` webhook with `merged = true` for that PR moves ENG-15 to Done, sets `completed_at`, and enqueues a Slack message. A `closed` with `merged = false` leaves the state alone.
10. A PR whose body says `Fixes ENG-15 and DES-4` links both issues, and an identifier for a nonexistent team key is ignored.
11. With `cycle_start_weekday = 1`, `cycle_length_weeks = 2`, and timezone `America/Los_Angeles`, the `cycles` job creates cycle 1 starting Monday 00:00 Pacific. When the clock passes `ends_at`, the cycle completes, `total_scope` and `completed_scope` are correct, and every unfinished issue moves to cycle 2. Across the March DST change, cycle 2 still ends at Monday 00:00 Pacific.
12. Three state changes on one issue within 30 seconds produce exactly one Slack POST describing all three. A Slack 500 is retried and does not fail the mutation.
13. Importing a 500-row Linear CSV creates 500 issues with matching identifiers, states, priorities, labels, and assignees, and sets `issue_counter` so the next new issue is one higher than the max imported number.
14. `/api/search?q=login` returns in under 50ms with 20,000 seeded issues, and `?q=ENG-12` returns ENG-12 first.
15. A fresh clone runs with `pnpm install && pnpm dev` and no env vars, and the first browser visit creates the admin.
16. `docker compose up -d` on a clean Ubuntu VPS with one A record serves the app over HTTPS with no other setup.
17. Killing the process mid-job and restarting it doesn't double-run or lose the job.
18. `pnpm backup` then deleting `./data/app.db` and restoring from the backup brings every record back.
19. Every outbound email in dev shows up in `./data/outbox`.

## Deliverables

- The app, migrations, and a seed script that creates three teams (ENG, DES, OPS), 12 users, 400 issues spread across states, 4 projects, 3 cycles per team (one completed, one active, one upcoming), 300 comments, and 20 linked pull requests.
- `lib/identifiers.ts` (regex extraction and magic words), `lib/cycles.ts` (date math), `lib/slack.ts` (message formatting), and `lib/jobs.ts` (the lock claim and release), each pure where possible and unit-tested.
- Sample GitHub webhook payloads in `fixtures/github/` for opened, synchronize, closed-merged, closed-unmerged, and push, with a script that signs them. The Simulate webhook button uses the same files.
- `docker-compose.yml` with `app`, `caddy`, and `backup`; a `Caddyfile` that reads the domain from `DOMAIN`; `scripts/backup.sh` behind `pnpm backup`.
- README covering the laptop run, the VPS run, backup and restore, the three outside services (GitHub webhook setup and the simulate button, Slack incoming webhooks, `SMTP_URL`), the optional Google sign-in, and how to import from Linear.

Build the data model and the identifier transaction first, with the concurrency test. Then first-run setup and sessions. Then the issue list and issue page with the keyboard layer. Then optimistic updates and SSE. Then the GitHub webhook with the simulate button, then the job lock and cycles, then Slack, then email and import, then compose, Caddy, and backup. Run the app in the browser after each step and check the shortcuts by hand.