# Board data: planning and organising

How the features in the guide's planning and organising pages are stored and enforced. Read it before you change one
of their tables, triggers or RPCs. Each table's migrations are under `db/migrations/`. The user side is in
[`docs/guide/`](https://andon-docs.pages.dev/index.md#planning-and-organising).

## Saved views

`views` (`20261103160834_saved_views.sql`): `owner_id` null means shared; `filter` holds the filter bar's query as
structured keys. `packages/shared/src/views.ts` is the one place the keys are defined. It parses and formats the
query, matches cards, groups and sorts, and the public API's `q` parameter uses it too. Changes to shared views are
audited as `view.create|update|delete`; the permission is `view.shared`.

Without a view open, the filter bar and layout are kept in `board_prefs`, as are swimlanes (`swimlanes`, `subLanes`,
`hideEmptyLanes`, `laneOrder`, `projectLaneOrder`, `collapsedLanes`). Lanes are worked out in
`apps/board/src/lib/swimlanes.ts`.

## Cycles and estimates

`cycles` (`20261107202319_cycles_estimates.sql`), on the root team of a tree; `cards.cycle_id`, `cards.estimate`.

| Column | Notes |
| --- | --- |
| `team_id` | the top team of a tree; sub-teams share their root's cycles |
| `number`, `name` | numbered per team; the name defaults to "Cycle *n*" and can change after it closes |
| `starts_on`, `ends_on` | UTC days, at most 12 weeks; a team's cycles never overlap |
| `state` | `planned`, `active` (at most one per team), `done` |
| `carried_over`, `summary` | written at close: the cards carried over and the cycle's numbers |

- The hourly `cycles_tick` (run by `andon.run_due_ticks` on the server) keeps two cycles ahead
  for teams with a cadence, closes each cycle the day after it ends and starts the next. One team's failure is logged
  as a Postgres warning and skipped.
- Closing writes a `card.carry_over` audit row per carried card. When there is no next cycle and something is left to
  carry, one of the same length is made after it, or around today, past any cycle in its way.
- The database lets members rename a cycle, and its maker, owners and admins delete an open one. The board offers
  neither.
- Estimates: points as the number, t-shirt sizes as 1 to 5, hours as the hours (`packages/shared/src/cycles.ts`).
  Autopilot's placement sees `estimate` in each card's facts (`placement_input`).

## Milestones

`milestones`: `team_id`, `project_id`, `name` (unique per project, case-insensitive), `description` (the board's
**Note**), `target_on`, `state` (`open`, `done`, `canceled`), and `external_provider`/`external_id` for imported ones;
`cards.milestone_id`. Audited as `milestone.create|update|delete`. Imports find a
Linear milestone by its Linear id, and adopt a board milestone of the same name made by hand.

The timeline layout (`apps/board/src/components/board/TimelineLayout.tsx`) doesn't draw cycles yet; the code
comment there marks where bands would go.

## Custom fields

`custom_fields` (`20261107204016_custom_fields.sql`): `team_id`, `name`, `key`, `kind`, `options`, `required`,
`position`, `show_on_face`. Values live in `cards.fields`, checked by the `cards_fields` trigger on every write:

- A value for a field the card's team doesn't have, or that doesn't fit the kind, is refused (SQLSTATE 22023).
- A value the card already had that no longer fits is dropped rather than refused. Adding, changing or deleting a field
  re-checks the cards that hold its key; moving a team re-checks its cards and its sub-teams'.
- A required value can change but not be cleared (23502). Cards are still made without one: imports, quick adds and
  agents don't know a team's fields.
- The owner rule applies to fields like any card change.

At launch the agent reads values with `card_fields(p_card)` (names, kinds, display text), for the `{{fields}}` and
`{{fields.<key>}}` prompt tags. Imported fields: `apps/server/src/functions/shared/import-fields.ts` and
`tracker-import-job.ts`.

## Sub-tasks and relations

`20261108171243_subtasks_relations.sql` and `20261108200934_card_deps_into_relations.sql`.

- `cards.parent_card_id`: same org, same team tree, no loop, at most 3 levels. `private.cards_workstream_guard`
  enforces it, serialised per org; the board asks `nestRefusal` (`packages/shared/src/subtasks.ts`) first.
- `cards.auto_advance` marks a workstream step; hand-added, template and dropped sub-tasks have it off.
  `cards.done_with_children` is the parent's switch. A workstream's card closes through `private.close_done_parent`.
- `card_relations(card_id, other_id, kind, source)`, kind `blocks`, `duplicate_of` or `relates_to`. A `blocks` row reads
  "`card_id` blocks `other_id`"; `source` is `linear` or `jira` for imported ones.
- `card_deps` is a view over the `blocks` rows with its old columns (`card_id` waits for `depends_on_id`), so the
  dispatch hold, workstream auto-advance, Autopilot planning, PR groups, `card_board_v` and `attention_v` read it
  unchanged. Inserting into it writes a `blocks` row. New code reads and writes `card_relations`.
- `teams.hold_blocked` is **Blocked cards wait** (default true).
- Autopilot leaves parents and sub-tasks out of its groups (`autopilot_card_ok`, `placement_enqueue`) and starts an
  unpaused workstream on its own (`autopilot_queue`).
- A `report` with `outcome: parked` may name `waiting_on` keys; they become `blocks` rows once the card is in the
  Backlog.

## Templates and bulk edit

`20261108141843_card_templates_bulk_edit.sql`.

- `card_templates`: org-wide (`team_id` null) or a team's, with `title_pattern`, `notes`, `recipe_id`, `labels`,
  `priority`, `estimate`, `fields` and `subtasks`. `card_apply_template` fills a card as the caller, so the owner rule
  applies. `cards.templates` remembers which templates filled a card, so a status default applies once.
- `cards_bulk` makes every Set and Archive one call: each card changes in its own savepoint under the same RLS, owner
  rule and checks as a one-card edit, and a refused card is skipped. It returns each changed card's old values, and
  `p_action: 'undo'` sends them back. One audit row per batch: `card.bulk_edit` or `card.bulk_undo`.

## Comments

`20261108144947_card_comments.sql`.

```
card_comments(id, org_id, card_id, author_id, parent_id, body, mentions uuid[], edited_at, deleted_at, deleted_by,
              machine_id, run_id,        -- a run's agent left it (agent_comment)
              former_author_id,          -- an imported comment's author who isn't a member
              external_provider, external_ref, external_url, external_author)    -- brought from Linear
card_comment_reactions(org_id, comment_id, card_id, user_id, emoji)    -- primary key (comment_id, user_id, emoji)
notifications(id, org_id, user_id, kind 'mention', card_id, comment_id, actor_id, read_at)
card_thread(card, limit)                 -- the run's {{comments}}: latest 20, each cut at 4000 characters
delete_card_comment(comment)
agent_comment(run, text, card?, reply_to?)    -- the MCP tool `comment`
```

- Whoever sees the card reads its comments and reactions (RLS through `visible_card_ids`). Only the author edits;
  nobody hard-deletes. Deleting an author, machine or run only clears its id on the comment.
- `private.comment_mentions` keeps only members who can see the card, never the author. Mention handles come from
  `mentionHandle` in `packages/shared/src/comments.ts`, shared with the notes editor.
- `agent_comment` checks that the run is the calling machine's. The database clears `machine_id` and `run_id` on a
  person's comment, so nobody can pose as a machine.
- Linear comments are written only by `import_issue_comments`, once, when the card is made.
- Realtime topics: `org:<orgId>:comments:<cardId>` and `org:<orgId>:notifications:<userId>`. A mention push goes only
  to that person's devices (`payload.user_id` in `push-core.ts`).

## Activity, the archive and history

`20261109010731_activity_archive_history.sql`.

- `card_history` keeps one row per change to a tracked card field: before, after, who and `via`. Notes keep their first
  280 characters. Only the database writes it.

  | `via` | When |
  | --- | --- |
  | `board` | a person signed in (the board, the desktop app) |
  | `cli` | a machine's `andon` CLI through `/api/v1` |
  | `api` | an API token |
  | `import` | a tracker import |
  | `agent` | a machine acting through the data API (a run, the daemon) |
  | `system` | Andon's own ticks and service writes |

- `archived_cards` searches archived cards by any key they have had, or by words in the title and notes.
- `teams.auto_archive_days` is stored per team but set for the whole workspace; `auto_archive_tick` (the
  server's `auto-archive` tick) runs hourly. Teams added by a machine or a tracker import start on Never.
- `notifications_mark_read` marks Activity read.

## The Dashboard

The Dashboard page (`apps/board/src/pages/Dashboard.tsx`) is the lamp in `components/lamp/`. Its count is the sidebar's
(`useAttention`, `needsHuman`). The wake lock, pixel shift and night dim live in `lib/hygiene.ts`; the night dim is
kept in `localStorage`. Car mode's pure logic (phrases, the decision queue, briefings) is `lib/car-mode.ts`, and each
spoken decision is logged with `log_voice_action`.
