CASE STUDY 02 / 05

CT Work

Jira-style project management

A project-management tool built into the platform: boards, backlog, sprints, burndown/velocity reports, a JQL-style query language, test management, an AI assistant that only proposes — people decide — and read-only share links for outsiders.

Status
In daily use
Timeline
Since 23 Sep 2026 (the module’s first commit)
Platforms
Web · iOS · public share link
Role
One lead engineer: design, build, operations
01

Context & problem

The studio’s delivery process has many stages, each with documents and a quality gate. It needed one place where tasks, gates, risks and documents link together — and where outsiders (clients, lecturers) can follow progress without an account.

It was built inside the platform rather than adopting an external tool so it could share the existing accounts, permissions, AI gateway and mobile app.

02

Who it serves

  • Project teams

    Plan sprints, move cards, track tests, write weekly reports.

  • Outside observers

    View the board/backlog through a read-only link that expires and can be revoked.

  • Students on capstone projects

    Run a capstone by the course’s grading rules: requirements, gates, defence documents — LabFlow AI is the first project run this way.

03

Solution & key features

  • Boards, backlog, sprints, reports

    Drag cards across columns and within the backlog, sprints with burndown and velocity, per-project custom workflows, a trash bin and an audit log.

  • A JQL-style query language

    For example: assignee = currentUser() AND status != Done ORDER BY priority. Syntax errors come with a position and a “did you mean…” suggestion.

  • Test management

    Test cases, test plans and test cycles linked to work items.

  • An AI assistant that only proposes

    Q&A, weekly reports, sprint planning, retros, daily briefs, defence practice. Every change the AI wants is a proposal — nothing is written until a person presses “Apply”.

  • Sharing and integrations

    Per-section read-only public links, an iCalendar (.ics) feed for calendar apps, GitHub/GitLab integration, API tokens for scripts.

  • Edit lock

    With the lock on, the server refuses one’s own writes in that project — so a plan can be browsed without accidentally moving cards.

04

Architecture

  1. Clients

    • Web /workNext.js
    • iOSCT Work module
    • Share linkRead-only, expiring
    • .icsCalendar apps
    • API tokenScripts
    REST · WebSocket

  2. Entry

    • Work router213 endpoints
    • Edit-lock423 while locked
    Every command asks for permission

  3. Permissions

    • can(role, action)Pure function + matrix, one place only
    Authorised

  4. Domain

    • Issues · SprintsLexoRank-style ordering
    • JQLParse → tree → Prisma
    • Reports · TestsBurndown, velocity
    • AIProposal → human applies
    • GitHub · GitLab
    Events emitted after the write

  5. Data & realtime

    • PostgreSQL52 Work* models
    • Socket.IOOne room per project
    • Audit log
The path of a write: into the router, through the edit lock, one single permission layer, written to the database, and only then broadcast to everyone viewing the project.
05

Hard technical decisions & trade-offs

01Card order with LexoRank-style string keys

Problem
With 1, 2, 3… positions, dropping a card in the middle renumbers the whole column — many writes, and conflicts when two people drag at once.
Decision
Each card holds a string between its two neighbours: a drop writes exactly one row. Appending increments a 6-character head (~2 billion appends at constant length). The alphabet is digits and lowercase only so PostgreSQL collation order matches JavaScript comparison.
Trade-off
Repeated middle inserts lengthen keys; past 48 characters (the column holds 64) the column has to be rebalanced.

02JQL compiled to Prisma queries, never concatenated SQL

Problem
Letting users type free-form queries invites SQL injection if strings are concatenated.
Decision
Two layers: the parser is a pure function (string → syntax tree, errors with positions), the compiler turns the tree into Prisma conditions using the project’s lookup tables. Every value goes through Prisma.
Trade-off
A grammar to maintain in-house — it has 25 dedicated test cases, and every syntax bug found in real use becomes a new case.

03One single place decides permissions

Problem
Two earlier lessons from this very repo: “hiding a button is not blocking the API” and “two places checking one permission become two rules”.
Decision
Every route AND every AI-assistant tool asks the same permission layer. The core is a pure function that never touches the database, tested by table; the outer layer loads data then calls the core.
Trade-off
New actions must be added to the matrix — a little slower, but impossible to forget.

04Edit lock enforced on the server, not just hidden buttons

Problem
People browsing a pre-built plan would accidentally drag cards or edit fields, with no way back.
Decision
While locked, the server returns 423 for that person’s writes — drag and drop, bulk edits, the iOS app and the AI “Apply” button all pass through it. A whitelist keeps comments, daily logs, AI questions and filters working.
Trade-off
The whitelist must be updated when new write routes appear.

05AI proposes, a person applies

Problem
The AI assistant has tools that change the plan; letting it write directly hands decisions to the model.
Decision
Each change the AI wants is stored as a proposal in the message; separate endpoints apply or dismiss each proposal.
Trade-off
One more click for the user.
06

Real incidents & lessons

Accidental edits while only browsing

25/09/2026

What happened
A user browsing a pre-built plan accidentally dragged cards and edited fields.
Root cause
The board allowed writes at all times, with no view-only mode for the project owner.
Fix & safeguard
Added a per-project edit lock, enforced on the server (code 423), with an Unlock button in the UI.

`sprint IN openSprints()` failed to parse

What happened
A familiar Jira-style filter reported a syntax error while seeding the LabFlow project.
Root cause
The parser only accepted a parenthesised list after IN, not a bare function.
Fix & safeguard
Fixed the grammar to match Jira and added test cases for all three spellings.
07

Measured numbers

No user, revenue or client numbers here — only what can be counted from source code and the database. Each row says how it was counted so you can check it. Anything that cannot be counted is not shown.

Module size

Counted by command on the codebase on 04/10/2026

Dedicated data modelsgrep -c "^model Work" prisma/schema.prisma
52
API endpointsrouter.get/post/…( lines in work.routes.ts
213
UI pagespage.tsx under frontend/src/app/work
20
Backend lines of codewc -l services/work/*.ts + work.routes.ts
12,930
Test files*.test.ts in services/work and routes/work*
18
Test casestest(…)/it(…) lines in those 18 files
174
Module commitsgit rev-list --count HEAD -- (work folders)
32
08

Technology

Front end
Next.js · React · TypeScript · TipTap
Back end
Express · Prisma · PostgreSQL · Socket.IO
Integrations
GitHub · GitLab · iCalendar (.ics) · API token
Testing
node:test (tsx --test) · Kiểm thử trên cơ sở dữ liệu thật