Conditional persistence, optimistic updates, and why we only call the AI once per week

By Kiprono Ngetich

We wanted live collaboration. We wanted AI-generated status extraction. And we wanted server costs that didn't explode when 50 people opened the same sheet.

The standard approach store everything, recalculate on every view was a non-starter.

So we built something different: a POST-once persistence model where the REST API is a write-once initialization store and WebSockets handle everything else.

Here's how it works, why we chose Cohere Command-A for extraction, and what broke along the way.

1. The core problem

Status sheets die on write.

Traditional project management tools (Jira, Asana, Monday.com) require disciplined data entry from contributors who are primarily focused on delivery. The result is a system that is accurate only when it is actively maintained — creating a secondary task burden on the people least likely to have capacity for it.

Our goal was to flip this: make the status sheet a byproduct of normal work, not an additional job.

2. The WeekData abstraction

The central data structure in our system is the WeekData object, which encapsulates all intelligence associated with a discrete working week.

A WeekData instance carries:

weekNumber (ISO week identifier)

quarter (fiscal quarter)

tasks (ordered list of WeekTask objects)

rawMessage (the unprocessed source text for auditability)

summaryText (AI-extracted executive summary)

The key design choice: WeekData is constructed from a single raw message. This means the platform can reconstruct full week intelligence from one stored message — making the raw WebSocket stream the authoritative source of record.

3. The POST-once model

This is the most important architectural decision.

Before invoking the AI model, the system performs a GET lookup against the persistence API. Generation — and the subsequent POST to persist results — occurs only when the lookup returns no existing data for the current week.

The implications:

Server storage grows at a rate of at most one record per week, regardless of how many users open the status sheet

The AI model is invoked at most once per week, regardless of page refresh frequency

All subsequent changes are propagated exclusively via WebSocket, with no REST API writes

The conditional persistence model sounds obvious in retrospect, but it wasn't obvious at the start. Our first design stored every user's view state separately. For a team of 50 people looking at the same week's status sheet, we were storing 50 identical copies of the same data.

The current design cut storage costs by two orders of magnitude.

Lesson: Cache at the domain level, not the view level.

text
# Generation trigger logic (simplified)
async def get_or_generate_status_sheet(week_id):
    existing = await db.find(f"week_{week_id}")
    if existing:
        return existing

    raw_messages = await fetch_workspace_messages(week_id)
    ai_output = await cohere_generate(raw_messages)
    status_rows = parse_markdown_table(ai_output)

    if not status_rows:
        status_rows = create_fallback_rows(raw_messages)

    await db.post(f"week_{week_id}", status_rows)
    return status_rows

4. AI pipeline: prompt engineering for reliability

We use Cohere's Command-A model (command-a-03-2025), selected for its strong instruction-following characteristics and structured output reliability.

The generation prompt is structured in two parts:

System prompt: Defines the output schema (a markdown table with exactly nine columns) and quality constraints (10-20 word activity descriptions, specific comment formats). The model is explicitly prohibited from adding explanatory prose outside the table structure.

User prompt: Constructed from the WeekData's task list, serialized into structured text that surfaces each task's title, brief, deadline, responsible party, and full detail text.

The model's markdown output is parsed line-by-line with pipe-delimited split and header detection. Rows failing the 9-column minimum are silently dropped.

If the parsed output yields zero rows — due to model refusal, formatting deviation, or network failure — the system falls back to constructing rows directly from the WeekTask list without AI enrichment. This graceful degradation ensures status sheets are always available.

5. WebSocket protocol: five message types

The collaboration protocol is built on three principles:

Operation-based messaging (transmitting what changed, not the full state)

Human-readable formats (pipe-delimited strings that can be logged without a parser)

Optimistic local application (changes apply locally before server confirmation)

The five message types:

Message Type    Format
CELL_EDIT   CELL_EDIT|rowIndex|column|oldValue|newValue|username
BATCH_UPDATE    BATCH_UPDATE|rowIndex|column|value|username
ROW_DELETE  ROW_DELETE|rowIndex|username
ROW_ADD ROW_ADD|{rowJson}|username
USER_JOINED USER_JOINED|username
USER_LEFT   USER_LEFT|username

CELL_EDIT carries both old and new values to enable future conflict detection and undo functionality. ROW_ADD carries a JSON-serialized StatusRow to ensure type fidelity when transmitting multi-field objects.

User presence is maintained client-side as an ephemeral set — presence is a live indicator, not a historical record. Reconnecting clients re-announce themselves, ensuring the presence set self-heals after disconnections.

6. What broke (honest failures)

Three things broke in production:

Model refusal: Occasionally, the model would output "I cannot complete this request" instead of a table. Our fallback to _createFallbackRows() handled this, but we added retry logic with a modified prompt (removing date references) which reduced refusal rates by ~80%.

Formatting drift: The model sometimes added extra columns or merged cells. We made the parser tolerant — it drops extra columns and treats missing columns as empty strings rather than failing the entire row.

Reconnection storms: When the WebSocket server restarted, all clients reconnected simultaneously and re-requested the full status sheet. We added jittered backoff (3s + random 0-2s) to spread the load.

7. What we'd do differently

The biggest missing piece is the two-way feedback loop.

Right now, human edits to the status sheet don't flow back to the AI's understanding of project state. If a project manager corrects a deadline, the AI doesn't learn that it was wrong.

We're building toward this. The isManuallyEdited flag on each StatusRow is the foundation — it tells us which fields represent human judgment vs. AI extraction. The next step is feeding corrected rows back into the prompt context for future weeks.

Other improvements on the roadmap:

Operational transforms or CRDTs for concurrent cell edits

Per-cell audit trails (username + timestamp)

Export to PDF and XLSX

Closing
The system isn't perfect. The AI misses context sometimes. The WebSocket protocol doesn't yet handle concurrent cell edits with operational transforms.

But the core pattern — conditional persistence, operation-based messaging, graceful AI degradation — has proven reliable across months of internal use.

If you're building a collaborative, AI-assisted document tool, steal the POST-once pattern. It's the difference between a demo and a deployment.

Kiprono Ngetich builds software at the intersection of AI and collaboration.