Document data layer contracts and RLS policies

epic-travel-expense-registration-foundation-task-012 — Produce developer documentation covering: Supabase table schemas and column semantics for expense_claims and expense_line_items, RLS policy rationale and test queries, receipt Storage bucket structure and signed URL lifetime, threshold configuration schema and cache TTL behaviour, auto-approval edge function request/response contract, and offline sync conflict-resolution strategy. Documentation must be sufficient for upstream epic teams to begin implementation without further clarification.

medium priority low complexity documentation pending documentor Tier 4

Acceptance Criteria

Documentation is written in English, stored as Markdown in `docs/data-layer/` (or equivalent agreed location), and committed to the repository

Table schema section lists every column in expense_claims and expense_line_items with: column name, Postgres type, nullable, default value, and a one-sentence semantic description

RLS policy section documents each policy by name, affected table, operation (SELECT/INSERT/UPDATE/DELETE), and the business rule it enforces; includes a copyable test query (`SET LOCAL role = 'authenticated'; SET LOCAL request.jwt.claims = '...'; SELECT ...`) that verifies the policy in psql

Storage section documents: bucket name, folder structure (e.g. `{org_id}/{claim_id}/{filename}`), MIME type allow-list, maximum file size, and signed URL lifetime in seconds

Threshold config section documents the schema of the org_threshold_config table/JSONB column, cache TTL value (and where it is configured), and the invalidation trigger

Edge function contract section includes: endpoint URL pattern, required headers (Authorization), full request body JSON schema with field descriptions, all possible response body shapes, and HTTP status codes with meanings

Offline sync section describes: local storage mechanism (Hive, SQLite, or equivalent), conflict resolution strategy (last-write-wins vs. server-authoritative), sync trigger conditions (app foreground, connectivity change), and how duplicate prevention works

A 'Quick Start for Upstream Teams' section summarises the minimum a new epic team needs to read to call the data layer correctly

All code examples are syntactically correct Dart or SQL (confirmed by review or build check)

Documentation is reviewed and approved by at least one other engineer before the task is closed

Technical Requirements

frameworks

Markdown

Supabase SQL dialect (for RLS test queries)

Dart (for code examples)

apis

Supabase REST API (documented, not called)

Auto-approval Edge Function (documented, not called)

data models

expense_claims

expense_line_items

org_threshold_config

Receipt (Storage object)

performance requirements

Documentation must be discoverable via the project's README or a top-level CONTRIBUTING.md link

Each section should be independently navigable via Markdown anchor links

security requirements

Documentation must NOT contain real Supabase project URLs, API keys, or production credentials — use placeholders like `<PROJECT_REF>` and `<ANON_KEY>`

RLS test queries must use the local emulator URL, not the production URL

Execution Context

Execution Tier

Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

View Full Execution Plan

Implementation Notes

Write documentation bottom-up: start from the integration test file written in task-011 (it is the ground truth of what the data layer does) and document each behaviour tested there. Use the integration tests as the canonical reference — if a test asserts a behaviour, document it; if a behaviour is not tested, flag it as 'untested / to be confirmed'. Structure the RLS section as a table: Policy Name | Table | Operation | Rule. For the edge function contract, use an OpenAPI-style request/response example (YAML or JSON block in Markdown) — this is the most useful format for upstream teams.

Include a Mermaid sequence diagram for the offline sync flow if the conflict resolution is non-trivial; this saves a thousand words. Keep the 'Quick Start' section to one page maximum — it is the entry point for busy engineers.

Testing Requirements

Documentation quality verification: (1) a second engineer attempts to implement a simple data layer call using only this documentation — any question they need to raise is a documentation gap to fix; (2) SQL test queries in the RLS section are verified to run without syntax errors against the local emulator; (3) Dart code examples compile without errors (can be verified by pasting into a scratch file and running `flutter analyze`). No automated test suite required, but peer review is mandatory before close.

Component

Expense Repository

data medium

Dependencies (1)

Write integration tests covering the full data layer: expense type catalogue fetch and offline fallback, expense claim create/read/update with offline queue sync, receipt upload and signed URL retrieval, threshold configuration load and cache invalidation, and auto-approval edge function invocation. Use flutter_test and a local Supabase emulator or mock server. Assert that all repository methods handle network failure gracefully. epic-travel-expense-registration-foundation-task-011

Epic Risks (3)

high impact medium prob security

Row-level security policies for expense claims must correctly scope data to organisation, role (peer mentor sees own claims only, coordinator sees org-wide queue), and claim status. Incorrect RLS can expose claims cross-organisation or prevent coordinators from accessing the attestation queue.

Mitigation & Contingency

Mitigation: Define RLS policies in code-reviewed migration files. Write integration tests that attempt cross-org reads with different JWT roles and assert access denial. Review with a second engineer before merging migrations.

Contingency: If RLS is misconfigured post-deployment, disable the affected policy temporarily and apply a hotfix migration within the same release window. No claim data is exposed publicly due to Supabase project-level auth requirement.

medium impact medium prob technical

The auto-approval Edge Function is triggered server-side on expense insert. Cold-start latency or Edge Function failures can block the submission response and degrade UX, especially on mobile networks.

Mitigation & Contingency

Mitigation: Implement the auto-approval Edge Function client with a timeout and graceful fallback: if no result is received within 5 seconds, treat the claim as 'pending' and poll for the status update via Supabase Realtime. Keep the Edge Function warm with a periodic ping.

Contingency: If Edge Function reliability is unacceptable, move auto-approval evaluation to a database trigger or Postgres function as an interim measure, accepting that threshold configuration changes require a migration rather than a settings update.

medium impact low prob scope

The expense type catalogue and threshold configuration are cached locally for offline use. If an organisation updates their catalogue exclusion rules or thresholds while a peer mentor is offline, the local cache may allow submissions that violate the new policy.

Mitigation & Contingency

Mitigation: Cache entries include a TTL (24 hours). On connectivity restore, refresh cache before allowing new submissions. Server-side validation in the Edge Function and save functions provides a second enforcement layer.

Contingency: If a stale-cache submission passes client validation but fails server validation, surface a clear error message explaining that the expense type rules have been updated and prompt the user to review their selection with the refreshed catalogue.

Quick Links

All Tasks Execution Plan