Nursing Kanban Backend

Companion to nursing-kanban-board.md. Describes the live backend powering the Ward Kanban surface.

Schema (migration: 20260514_ward_kanban.sql)

Table / View	Purpose	Writes from
nursing_shifts	Sprint instance (8-hour เวร: morning/noon/night), one row per ward × date × shift_type. Carries handover_from/to FKs + sprint metrics filled in at close.	Shift planner
improvement_cards	Nurse-filed feature/bug/policy/equipment/training/workflow requests. Self-contained — no FK out to focus_list/orders.	“+ New Card” → category=Improvement
kanban_card_status	Overlay table. Stores per-card Kanban status/priority/assignee/shift WITHOUT touching the source row. Drag-drop writes go here only.	transition_kanban_card() RPC, called from drag-drop and the inline quick-edit popover
kanban_card_comments	Comments + @mentions per card. Plain timestamps; no edit history past edited_at.	Card detail drawer
ward_kanban_cards (view)	Unified read-model. Unions focus_list + improvement_cards joined against the status overlay. Designed to grow: add UNION ALL blocks for orders / acks / worklist when those wire in.	Read-only — frontend never writes here

Source-of-truth model

   focus_list ───┐
   orders ──────┤        ┌─ kanban_card_status (overlay) ─┐
   ack_requests ┼───────►│  status / priority / assignee /│
   worklist ────┤        │  shift_id / labels / due_at    │
   improvement ─┘        └────────────────────────────────┘
                            │
                            ▼
                    ward_kanban_cards (view)
                            │
                            ▼
                  WardKanbanTarget (frontend)

Rule: Source rows are read-only from the Kanban surface. All drag-drop, priority changes, assignee changes, label edits land in kanban_card_status. The source remains the canonical clinical record (nursing care plan, medication order, etc.).

For Improvement cards, the source IS improvement_cards so writes can land directly there.

Migrations

# 1. Apply the main migration
psql "$SUPABASE_DB_URL" -f web/supabase/migrations/20260514_ward_kanban.sql

# 2. (Optional) Seed demo data
psql "$SUPABASE_DB_URL" -f web/supabase/migrations/20260514_ward_kanban_seed.sql

# Or paste into the Supabase SQL editor — same result.

Both are idempotent (CREATE TABLE IF NOT EXISTS / ON CONFLICT DO NOTHING).

Per-region

Same migration works in every Supabase project. Locale-specific text on the cards comes from the frontend (labelTh / label); the schema is locale-agnostic.

RLS

Demo-friendly. Every authenticated / anon role has full CRUD. Tighten when auth lands:

-- Example: only the assignee + charge nurse can mutate
DROP POLICY IF EXISTS kanban_card_status_assignee_only ON kanban_card_status;
CREATE POLICY kanban_card_status_assignee_only ON kanban_card_status FOR UPDATE
  USING (assignee_id = auth.uid()::text OR auth.jwt()->>'role' IN ('charge_nurse','ward_manager'));

Realtime

Migration enables supabase_realtime publication on all four tables. Frontend subscribes via:

supabase.channel(`ward-kanban:${wardId}`)
  .on('postgres_changes', { event: '*', schema: 'public', table: 'kanban_card_status', filter: `ward=eq.${wardId}` }, payload => {...})
  .on('postgres_changes', { event: '*', schema: 'public', table: 'improvement_cards', filter: `ward=eq.${wardId}` }, payload => {...})
  .subscribe();

RPC: transition_kanban_card

Atomic drag-drop. Upserts the overlay + appends to status_history:

SELECT * FROM transition_kanban_card(
  'focus',                                  -- p_source
  'aaaa-bbbb-cccc-dddd',                    -- p_source_ref
  'ward-4',                                 -- p_ward
  'doing',                                  -- p_status
  'staff-1',                                -- p_user_id
  'สมศรี วงศ์สว่าง'                          -- p_user_name
);

Frontend service wraps this:

const { data, error } = await supabase.rpc('transition_kanban_card', {
  p_source: 'focus', p_source_ref: id, p_ward: ward,
  p_status: 'doing', p_user_id: userId, p_user_name: userName,
});

Status history trail

Every status / priority / assignee change appends to kanban_card_status.status_history (JSONB array):

[
  { "at": "2026-05-14T07:32:11Z", "by": "staff-1", "byName": "สมศรี วงศ์สว่าง",
    "from": { "status": "todo", "priority": "routine", "assignee": null },
    "to":   { "status": "doing", "priority": "stat",   "assignee": "วิลาวัณย์ แสงทอง" }
  }
]

Surfaced in the card detail drawer (Loop 11).

What’s intentionally NOT in this migration

• Source-row mutations. We never touch focus_list / orders / acknowledgement_requests.
• Policy gate enforcement. Status transitions are unconditional at the DB layer. Policy gates can be added later via a trigger that consults policy_gates, or enforced from the frontend service via the existing policy-gate UI.
• Notifications. kanban_card_comments has mentions[] but no fan-out. Wire to the central notification system in a separate migration when ready.
• Per-ward column overrides. Columns are still defined in the frontend (COLUMNS const). A ward_kanban_templates table can ship later if wards need different column sets.

Files

Path	Purpose
web/supabase/migrations/20260514_ward_kanban.sql	Schema
web/supabase/migrations/20260514_ward_kanban_seed.sql	Demo seed
docs/architecture/nursing-kanban-board.md	Frontend / phase plan
docs/architecture/nursing-kanban-supabase.md	This file
web/src/services/wardKanban.service.ts	Service layer (Loop 3)
web/src/hooks/useWardKanban.ts	React hook (Loop 6)