An internal kanban for cross-department requests

Overview

The client's cross-department requests used to run on Google Sheets and Apps Script: who asked whom, and how far along it was, held together by one shared sheet and manual reminders. Vulcan turns that process into a kanban system.

Cross-department dependencies get no table of their own. A dependency is an ordinary task whose owning department simply is not the creator’s. Dependency management and task management therefore share one table, one set of queries, and one board.

The wizard for raising a dependency drafts it inside the creator’s own department first, and only on send does it rewrite the owning department and take a ticket number from the receiving side. Abandoned drafts stay on your own board, never polluting the other queue — and without adding a table for them.

Architecture

The front end is SvelteKit in SPA mode, with server-side rendering switched off for the whole app. A board is an interaction-heavy surface with no SEO requirement, so SSR buys nothing here while forcing every interaction to account for two rendering paths.

Data moves only through this app’s own BFF endpoints, 42 of them. The endpoints run on a Worker bound directly to the database, so the browser never holds a database handle. Data access is concentrated in a shared Drizzle query layer; no SQL is written in the app.

Everything runs on the Cloudflare edge: a Worker hosts the app, D1 stores the data, R2 holds attachments, KV keeps per-person settings, and a Durable Object handles state synchronisation. Sign-in is Google OAuth, with sessions shared across two other internal apps.

The hard parts

Multi-user sync: broadcast invalidation only

When one person edits a task, everyone else’s board should follow — but no CRDT was introduced. The Durable Object acts purely as a broadcast relay, and what it pushes is the prefix of the invalidated query key; clients receiving it expire the matching cache and refetch. The single source of truth stays the database.

The broadcast narrows in two stages. Key prefixes carry the department and task identifiers, so prefix matching naturally reaches only the people currently looking at that slice; events for personal views are then addressed by employee identifier. The employee label attached to a connection is overwritten by the server-side gateway, so a client cannot forge it.

What that trade buys is that optimistic concurrency and the ordering logic needed no changes at all, and the Durable Object holds no business data whatsoever. Connections use the hibernation API, with heartbeats answered by the runtime without waking the instance, and a dropped connection falls back to polling.

Wedging a Durable Object into an entry point the framework overwrites

The Cloudflare adapter rewrites the Worker entry file on every build, so a hand-written Durable Object export cannot survive there. The build script therefore moves the compiled output aside afterwards and generates a wrapper entry in its place: ordinary requests are forwarded to the framework, connection upgrades are intercepted, and the Durable Object class is exported alongside.

The wrapper contains wiring and nothing else; the logic stays in source files that type-checking and tests can reach. The script is re-entrant, so running it again will not overwrite the output it already moved aside.

Optimistic concurrency and fractional indexing

Editable rows carry a version number, the update statement puts that version in its condition, and zero affected rows comes back as a conflict. Drag-to-reorder uses fractional indexing, with the server deriving the new sort value from the neighbours on either side of the drop — the client is never allowed to write it.

Together they mean concurrent drags never reshuffle a whole column. When two land on the same position, the insert hits a unique index and retries a bounded number of times; irreversible operations like taking a ticket number sit ahead of the version-guarded write, so losing the race costs at most one gap in the numbering.

Deriving parent status recursively

A parent task’s status is computed from its children rather than stored separately. When a child changes, recomputation walks up from its parent one level at a time and stops as soon as a level comes out unchanged, because everything above it depends only on that unchanged value. A visited set rides along to prevent cycles.

No full-text index, because the content is Japanese

Search runs on partial matching plus scoring in the application layer, not on SQLite’s full-text index. The default tokeniser cannot segment Japanese, and a trigram index would miss two-character words like 予約 and 会議. Search is scoped to the user’s own department, which keeps the scan affordable.

An AI-native way of building

This project went from first commit to production in 17 days, and 223 of its 400 commits carry an AI co-author trailer. The working method treats the AI agent as a design partner: I set the problem and the trade-offs, it produces an implementation, and I review it and decide whether it stays.

What makes that pace sustainable is writing the reasoning down. The repository holds four agent-facing documents totalling over two thousand lines, and what they describe is not the structure of the code but why each decision went the way it did, and which roads were already tried and rejected.

Why the calendar table was deleted, why dependencies get no table of their own, why search avoids a full-text index — each is recorded with its cost at the top of a document or a database migration. The next person to touch this reads the reasoning instead of guessing at it again.

The same documents pin down a few places prone to drift: enums are defined in exactly one file, renaming something in the UI does not touch the query keys in the code, and date fields that mean different things must not be merged. Each of those is guarded by a test of its own.