# ADR-075: Unified RPC Error Response Format and Client-Side Error Contract

## Status

Accepted, 2026-06-27.

## Status History

```yaml
status_history:
  - date: 2026-06-27
    status: Proposed
    changed_by: hkl
    reason: Formalising RPC error handling contract across all PWAs and PostgreSQL functions
    changed_via: adr-kit (360lm)
  - date: 2026-06-27
    status: Accepted
    changed_by: hkl
    reason: Contract stable; all RPCs now follow unified error format
    changed_via: adr-kit (360lm)
```

## Context

360lm uses PostgREST as the API layer (ADR-014) and PostgreSQL functions (RPCs) for all business logic. RPCs are called from PWA client code via fetch:

```javascript
const resp = await fetch('/db/rpc/some_function', { method: 'POST', body: JSON.stringify({...}) });
if (!resp.ok) { /* handle error */ }
```

Currently, error handling is inconsistent:
- Some RPCs raise `RAISE EXCEPTION` with user-readable messages.
- Some RPCs raise errors containing internal PostgreSQL error codes.
- Some RPCs return empty arrays or null for "not found" instead of raising errors (e.g., verify_pin per ADR-006).
- Client code lacks a uniform pattern for surfacing RPC errors to users.

PostgREST automatically maps PostgreSQL exceptions to HTTP responses:
- `RAISE EXCEPTION 'message'` → HTTP 400 with body `{"code":"P0001","message":"message","details":null,"hint":null}`
- Constraint violations → HTTP 409 with database constraint metadata
- Internal PG errors → HTTP 500

Without a formal contract, new RPC implementations vary in error style, client code uses inconsistent error checks, and users see either cryptic error codes or are confused by silent failures (empty arrays).

## Decision

**RPC error responses follow a unified contract:**

1. **RPCs that fail due to business logic** (validation, authorization, not-found, conflict) MUST:
   - Raise `RAISE EXCEPTION USING MESSAGE = '<user-readable message>', DETAIL = '<technical detail>'`
   - This produces HTTP 400 with PostgREST JSON: `{"code":"P0001","message":"<user-readable>","details":"<technical>"}`
   - Example: `RAISE EXCEPTION USING MESSAGE = 'PIN verification failed', DETAIL = 'invalid_pin_attempt'`

2. **RPCs that return TABLE (like verify_pin)** are exempt:
   - Empty array = not found / unauthorized / check failed
   - Non-empty array = success
   - No HTTP error is raised (see ADR-006)

3. **Constraint violations and internal PG errors** (unplanned):
   - Return HTTP 409 (conflict) for unique/FK constraint violations
   - Return HTTP 500 for internal errors
   - Client shows generic "Operation failed. Please try again." message

4. **Client-side fetch pattern (all PWAs):**
   ```javascript
   // Standard error-handling pattern for all non-TABLE RPCs
   const resp = await fetch('/db/rpc/some_function', { method: 'POST', body: JSON.stringify({...}) });
   if (!resp.ok) {
     const error = await resp.json();
     showToast(error.message || `Error: ${resp.status}`);
     console.error('RPC failed:', { status: resp.status, body: error });
     return; // or throw, depending on control flow
   }
   const result = resp.json(); // success case
   ```

5. **TABLE RPCs (verify_pin and similar):**
   ```javascript
   const res = await fetch('/db/rpc/verify_pin', {...});
   const data = await res.json(); // always succeeds (200 OK)
   if (Array.isArray(data) && data.length > 0) {
     // success: user = data[0]
   } else {
     // not found / unauthorized / check failed
   }
   ```

**Decision Maker:** hkl

## Implementation Notes

- **Files:** All RPC definitions in `/var/www/360lm` PostgreSQL migrations; all PWA fetch calls in `*.html` and `*.js` files
- **Key identifiers:** 
  - RPC error pattern: `RAISE EXCEPTION USING MESSAGE = '...', DETAIL = '...'`
  - Client pattern: `if (!resp.ok) { const error = await resp.json(); showToast(error.message); }`
  - TABLE RPC check: `Array.isArray(data) && data.length > 0`
- **Find all RPC definitions:** `grep -r "RAISE EXCEPTION" /var/www/360lm --include="*.sql" | head -30`
- **Find all RPC calls:** `grep -r "fetch.*rpc/" /var/www/360lm --include="*.html" --include="*.js" | head -30`

**Gotchas:**
- TABLE RPCs like `verify_pin` do NOT raise HTTP errors on failure — empty array is the signal. Don't add error handling for them.
- PostgREST always includes `"code":"P0001"` in the JSON for application exceptions. Don't rely on the code field; read `message` instead.
- If an RPC calls another RPC or trigger that fails, the outer error propagates. Make sure nested calls also follow this pattern.

## Alternatives Considered

- **Return HTTP 401 on business-logic failures instead of 400.** Rejected: HTTP 401 is reserved for authentication failures; business-logic failures (validation, conflicts) should use 400. Keeps HTTP semantics clear.
- **Return a wrapper JSON object like `{success: bool, error: {code, message}}` instead of PostgREST's native format.** Rejected: PostgREST's error format is automatic; we cannot override it without a custom API layer (rejected in ADR-014). Use PostgREST's native format and adapt client code.
- **Use different HTTP codes for different business-logic error types (400 for validation, 403 for authorization, 409 for conflict).** Rejected: complexity without benefit; 400 for all application exceptions is simpler and covers all cases. Let the `message` field distinguish error types, not the HTTP status.
- **Leave error handling to individual PWAs.** Rejected: leads to inconsistency; one PWA shows user-readable errors, another shows error codes. A unified contract ensures predictable UX.

## Consequences

**Positive:**
- All RPCs follow a single error contract — predictable for new developers.
- Client code uses one fetch pattern everywhere — easier to code review and maintain.
- User-readable error messages are explicit in the RPC, not buried in error code lookups.
- PostgREST's native error format is respected — no middleware needed to reformat.
- Empty arrays from TABLE RPCs are unambiguous (ADR-006 reinforced).

**Negative / Trade-offs:**
- Developers must write explicit `RAISE EXCEPTION USING MESSAGE = '...'` in every RPC that can fail (slightly verbose).
- Client code must call `await resp.json()` to read error messages (one extra step).
- TABLE RPCs (verify_pin) are an exception — dual mental model (array-based check vs. error-based checks).

**Risks and mitigations:**
- New RPC author forgets to use `RAISE EXCEPTION USING MESSAGE =` and raises a raw PG error code instead: Mitigated by code review checklist (ADR-068). All RPC additions reviewed before deploy.
- Client code forgets to check `!resp.ok` and tries to read result on 400 error: Mitigated by this ADR as a reference + unit test templates showing the pattern.
- TABLE RPC error handling mixes with regular RPC error handling: Mitigated by clear comments in TABLE RPC definitions (`-- Returns TABLE: empty array = not found, non-empty = success`).

## Related Decisions

- **ADR-006** (verify_pin returns TABLE array) — TABLE RPCs are exempt from this error contract; they signal failure via empty array, not HTTP errors.
- **ADR-007** (SECURITY DEFINER on privileged trigger functions) — trigger functions may raise exceptions; ensure they follow the `RAISE EXCEPTION USING MESSAGE =` pattern.
- **ADR-014** (PostgREST as API layer) — all RPCs are PostgREST functions; this decision formalises the error contract PostgREST enforces.
- **ADR-068** (VCC pre-build safety checklist) — item H requires checking `if (!resp.ok)` on all fetches; this ADR defines what `resp.ok` means for RPC errors.

## References

- `memory/feedback_verify_pin_response.md` — verify_pin response format (TABLE array, not object)
- `memory/feedback_pg_trigger_security_definer.md` — trigger functions raising exceptions
- PostgREST docs: Error handling and exception mapping (https://postgrest.org/en/stable/api/openapi.html)
- VCC Checklist item H (ADR-068) — fetch error guard pattern