​

ADR-0022: Daily fieldforce briefings as stored snapshots

​

Status

​

Tags

​

Decision

• team_ids is denormalized onto each ff_briefing_at_risk row at generation time (see §4.2 of the Phase 4 spec). Supervisor read filtering joins fresh supervisor.team_ids against the snapshotted row team_ids. Membership at generation time is the correct domain semantic for “what was your team’s at-risk picture this morning.”
• The cron is idempotent per (org_id, briefing_date) via a UNIQUE constraint, generation enters a lease-then-LLM flow per ADR-0023. Retries are safe and concurrent ticks across replicas resolve cleanly.

​

Why

1. Predictable cost. Live computation would call the LLM on every read — once per supervisor opening the panel, multiplied by tab refreshes and notification clicks. Per-org LLM spend would scale with user behaviour instead of with org count. Stored snapshot caps cost at one call per org per day, observable and budgetable via AccessGate.
2. History is a feature. Managers want yesterday’s briefing to compare against today, not just “the current at-risk list.” A stored snapshot makes history a SELECT, not a re-computation against historical states that no longer exist (a task closed today wouldn’t appear in a recomputed view of yesterday’s briefing).
3. Idempotent cron. A daily snapshot table with a UNIQUE natural key lets the cron be safely re-run, multi-replica deployed, and crash-resumed without infrastructure beyond Postgres. Live computation has no equivalent durable anchor — every read is a new operation.
4. Snapshot semantics align with denormalization. Supervisor team filtering requires a notion of “what teams owned this task at the briefing’s moment.” A stored snapshot makes that question well-defined; live computation forces a join against current member rows, which silently changes what supervisors see as memberships drift through the day.

• Live computation on read. Every panel open triggers gather + LLM. Rejected — cost scales with users, no history, no clean snapshot semantics for team filtering.
• Cached live computation (e.g., 1-hour TTL). A middle ground. Still loses history, and the TTL boundary creates “the briefing just changed under me” UX issues. Rejected.
• Materialised view, refreshed nightly. Postgres-native snapshot mechanism. Rejected because the briefing payload is partly LLM-generated text, not pure SQL — the refresh job would still need to call an LLM, at which point the materialised view is just an obscure wrapper around the same cron pipeline.

​

How it works

• ff_briefings — one row per (org_id, briefing_date) with UNIQUE(org_id, briefing_date). Holds summary_md, metrics columns, generation_mode, locale.
• ff_briefing_at_risk — child rows for at-risk items with denormalised team_ids and assignee_user_ids arrays. ON DELETE CASCADE from the parent.

​

Known limitations

• A briefing generated at 08:00 reflects state at 08:00; events later in the day are not retroactively folded in. Users wanting current state navigate to the task list, not the briefing widget. The widget header includes generated_at so the snapshot moment is visible.
• A supervisor demoted after generation still appears in the snapshot’s team-resolution context (the snapshotted team_ids reflects assignee teams, not supervisor membership). Their view is filtered at read time by their current supervisor.team_ids, which is correct — see Phase 4 spec §6.2.
• Manual regeneration (a “regenerate today” button) is out of scope. Each daily snapshot is final once generated; corrections require waiting for tomorrow or editing the row out-of-band. Phase 5 may revisit if demand surfaces.

​

Rules for agents

• The briefing read path MUST serve the stored row. Do NOT trigger LLM calls from a read handler.
• New columns added to ff_briefings MUST be filled at generation time, never lazily computed on read.
• Any “what did this look like in the past?” query against briefings MUST read the historical row, never recompute against current task state.
• Denormalised array columns (team_ids, assignee_user_ids) are frozen at generation time and MUST NOT be refreshed on read.

​

Bad pattern (do not generate)

// Live computation in read handler — defeats the snapshot
func GetLatestBriefing(ctx context.Context, orgID string) (*Briefing, error) {
    tasks := repo.OpenTasks(orgID)
    shortlist := heuristics.Score(tasks)
    summary := llm.Call(ctx, buildPrompt(shortlist)) // LLM on every read
    return &Briefing{Summary: summary, AtRisk: shortlist}, nil
}

​

Good pattern

func GetLatestBriefing(ctx context.Context, orgID string) (*Briefing, error) {
    return briefingsRepo.GetLatest(ctx, orgID) // SELECT, no recomputation
}