Players row edit: form action UPDATE + audit log in same transaction #5

New issue

Open

opened 2026-04-25 21:59:50 +00:00 by forgejo_admin · 1 comment

forgejo_admin commented

2026-04-25 21:59:50 +00:00

Owner

Type

Feature

Lineage

Depends on players list view ticket. Completes the WRITE half of the core story.

Repo

forgejo_admin/westside-admin

User Story

story-westside-admin-admin-row-crud. The WRITE half. Once this lands, M3 milestone (Marcus successfully edits a row without Lucas's involvement) is achievable.

Context

Click a row in the players list → editable form for that player. SvelteKit form action handles POST, validates input via Drizzle column metadata (NOT NULL, length, enum values), runs UPDATE within a transaction that also writes a contract_audit_log row capturing the diff. Either both succeed or both fail.

This is the "legitimized write surface" replacing banned ad-hoc psql UPDATEs. The audit log is non-negotiable — every mutation must be traceable to a Keycloak user via actor (= locals.user.email) + timestamp + table + before/after state.

See arch-dataflow-westside-admin Flow 2.

File Targets

Create:

src/routes/players/[id]/+page.server.ts — load() fetches single player; actions.default validates + UPDATEs + writes audit row in transaction
src/routes/players/[id]/+page.svelte — form with type-correct inputs per column; uses SvelteKit enhance; shows ActionResult feedback
src/lib/components/inputs/EnumSelect.svelte — <select> for enum columns
src/lib/components/inputs/JsonbEditor.svelte — <textarea> with JSON validation on blur
src/lib/components/inputs/DatePicker.svelte — <input type="date"> with formatting
src/lib/server/audit.ts — helper: writeAudit({ table, rowId, actor, oldState, newState }, tx) — writes to contract_audit_log

Modify:

src/routes/players/+page.svelte — make rows clickable, link to /players/:id

Do NOT create:

INSERT route (out of scope per story)
DELETE route (out of scope per story)
Bulk edit (out of scope)

Acceptance Criteria

GET /players/:id shows form with current values populated
Each column has the right input type: text → input, integer → input type=number, date → date picker, enum → select, jsonb → textarea, boolean → checkbox
Form validates client-side and server-side using Drizzle column metadata
On submit: UPDATE + audit log INSERT happen in the SAME transaction (both or neither)
On success: redirect to /players/:id with a flash message "Saved"
On Postgres error (e.g. constraint violation): error renders inline next to the offending field, no partial save
Audit row contains: actor (locals.user.email), table='players', row_id, old_state (jsonb of all columns before), new_state (jsonb of all columns after), timestamp
Sensitive columns (contract_token) are NOT editable AND NOT shown
Tenant scoping enforced — editing a player with tenant_id != 1 is a 404 (not a 403, to avoid revealing existence)

Test Expectations

Integration: edit a player's monthly_fee, assert DB row updated AND contract_audit_log row written, both committed
Integration: simulate transaction failure (e.g. invalid enum value) → no UPDATE, no audit row
Integration: tenant_id mismatch returns 404
Manual: Lucas edits a real player's notes field, sees the change, sees the audit row in psql

Constraints

Single transaction is non-negotiable — use Drizzle's db.transaction()
Audit actor field is the user's email (most human-readable); consider also storing sub (Keycloak UUID) for stability
Form must work without JavaScript (progressive enhancement — use:enhance upgrades but doesn't require)
monthly_fee is DOLLARS not cents per session_2026_04_12_contract_emails

Checklist

PR opened
Transaction atomicity verified by deliberate failure injection in test
Visual check screenshot in PR description

project-westside-admin
arch-dataflow-westside-admin Flow 2
feedback_never_write_prod_db (this ticket replaces it)

### Type Feature ### Lineage Depends on players list view ticket. Completes the WRITE half of the core story. ### Repo `forgejo_admin/westside-admin` ### User Story `story-westside-admin-admin-row-crud`. The WRITE half. Once this lands, M3 milestone (Marcus successfully edits a row without Lucas's involvement) is achievable. ### Context Click a row in the players list → editable form for that player. SvelteKit form action handles POST, validates input via Drizzle column metadata (NOT NULL, length, enum values), runs UPDATE within a transaction that also writes a `contract_audit_log` row capturing the diff. Either both succeed or both fail. This is the "legitimized write surface" replacing banned ad-hoc psql UPDATEs. The audit log is non-negotiable — every mutation must be traceable to a Keycloak user via `actor` (= `locals.user.email`) + timestamp + table + before/after state. See `arch-dataflow-westside-admin` Flow 2. ### File Targets Create: - `src/routes/players/[id]/+page.server.ts` — `load()` fetches single player; `actions.default` validates + UPDATEs + writes audit row in transaction - `src/routes/players/[id]/+page.svelte` — form with type-correct inputs per column; uses SvelteKit `enhance`; shows ActionResult feedback - `src/lib/components/inputs/EnumSelect.svelte` — `<select>` for enum columns - `src/lib/components/inputs/JsonbEditor.svelte` — `<textarea>` with JSON validation on blur - `src/lib/components/inputs/DatePicker.svelte` — `<input type="date">` with formatting - `src/lib/server/audit.ts` — helper: `writeAudit({ table, rowId, actor, oldState, newState }, tx)` — writes to `contract_audit_log` Modify: - `src/routes/players/+page.svelte` — make rows clickable, link to `/players/:id` Do NOT create: - INSERT route (out of scope per story) - DELETE route (out of scope per story) - Bulk edit (out of scope) ### Acceptance Criteria - [ ] GET `/players/:id` shows form with current values populated - [ ] Each column has the right input type: text → input, integer → input type=number, date → date picker, enum → select, jsonb → textarea, boolean → checkbox - [ ] Form validates client-side and server-side using Drizzle column metadata - [ ] On submit: UPDATE + audit log INSERT happen in the SAME transaction (both or neither) - [ ] On success: redirect to `/players/:id` with a flash message "Saved" - [ ] On Postgres error (e.g. constraint violation): error renders inline next to the offending field, no partial save - [ ] Audit row contains: actor (locals.user.email), table='players', row_id, old_state (jsonb of all columns before), new_state (jsonb of all columns after), timestamp - [ ] Sensitive columns (contract_token) are NOT editable AND NOT shown - [ ] Tenant scoping enforced — editing a player with tenant_id != 1 is a 404 (not a 403, to avoid revealing existence) ### Test Expectations - Integration: edit a player's `monthly_fee`, assert DB row updated AND contract_audit_log row written, both committed - Integration: simulate transaction failure (e.g. invalid enum value) → no UPDATE, no audit row - Integration: tenant_id mismatch returns 404 - Manual: Lucas edits a real player's notes field, sees the change, sees the audit row in psql ### Constraints - Single transaction is non-negotiable — use Drizzle's `db.transaction()` - Audit `actor` field is the user's email (most human-readable); consider also storing `sub` (Keycloak UUID) for stability - Form must work without JavaScript (progressive enhancement — `use:enhance` upgrades but doesn't require) - monthly_fee is DOLLARS not cents per `session_2026_04_12_contract_emails` ### Checklist - [ ] PR opened - [ ] Transaction atomicity verified by deliberate failure injection in test - [ ] Visual check screenshot in PR description ### Related - `project-westside-admin` - `arch-dataflow-westside-admin` Flow 2 - `feedback_never_write_prod_db` (this ticket replaces it)

forgejo_admin commented

2026-04-25 22:08:26 +00:00

Author

Owner

Scope Review: READY

Review note: review-1093-2026-04-25

Spec is solid. Transaction atomicity is rigorously specified (UPDATE + audit INSERT in same Drizzle tx with deliberate-failure test). Traceability complete (story-westside-admin-admin-row-crud verified, arch:page-server pattern documented in arch-dataflow-westside-admin Flow 2). All 6 Create file targets use conventional SvelteKit paths.

Pre-flight checks for Ava at todo->next_up:

[SCOPE] Confirm contract_audit_log table exists in westside Postgres. If missing, a migration ticket must land before this one.
Confirm dependency ordering: #6 (sveltekit-server) -> #1 (scopeddb-helper) -> #2 (hooks-server / locals.user) -> #4 (players list) -> #1093 (this). The Modify target (src/routes/players/+page.svelte) is created by #4.

Optional body polish (non-blocking):

[BODY] Add a one-liner that db.transaction(async (tx) => ...) requires using tx for both statements (not the outer db) — common Drizzle footgun.
[BODY] Specify "Saved" flash mechanism (query param / cookie / load() return).

Decomposition: NOT needed. 9 AC and ~7 files but single transaction unit + 3 tightly-coupled inputs — splitting would fragment the atomicity story. If the dev agent overruns 5 minutes, follow-up ticket for input components as reusable primitives.

Cleared for backlog -> todo.

## Scope Review: READY Review note: `review-1093-2026-04-25` Spec is solid. Transaction atomicity is rigorously specified (UPDATE + audit INSERT in same Drizzle tx with deliberate-failure test). Traceability complete (story-westside-admin-admin-row-crud verified, arch:page-server pattern documented in arch-dataflow-westside-admin Flow 2). All 6 Create file targets use conventional SvelteKit paths. **Pre-flight checks for Ava at todo->next_up:** - `[SCOPE]` Confirm `contract_audit_log` table exists in westside Postgres. If missing, a migration ticket must land before this one. - Confirm dependency ordering: #6 (sveltekit-server) -> #1 (scopeddb-helper) -> #2 (hooks-server / locals.user) -> #4 (players list) -> **#1093 (this)**. The Modify target (`src/routes/players/+page.svelte`) is created by #4. **Optional body polish (non-blocking):** - `[BODY]` Add a one-liner that `db.transaction(async (tx) => ...)` requires using `tx` for both statements (not the outer `db`) — common Drizzle footgun. - `[BODY]` Specify "Saved" flash mechanism (query param / cookie / load() return). Decomposition: NOT needed. 9 AC and ~7 files but single transaction unit + 3 tightly-coupled inputs — splitting would fragment the atomicity story. If the dev agent overruns 5 minutes, follow-up ticket for input components as reusable primitives. Cleared for `backlog -> todo`.