Document Store Plan

This is the implementation handoff for replacing browser-local documents with a shared Supabase document store. It is intentionally specific enough to resume from another development machine without reconstructing the architecture from chat history.

Outcome

The completed system should provide:

• One shared A Grade document workspace for Sebastien, Gina, and Gary.
• Automatic continuity between browsers, devices, and authenticated sessions.
• Database-owned invoice and proposal numbering.
• Safe concurrent editing through optimistic versions.
• Authenticated API access without a runtime service-role credential.
• Searchable document summaries without normalizing every document field.
• Recoverable browser caching without treating local storage as canonical.

Existing Architecture To Preserve

The plan follows the current client-dashboard and kof-repo Supabase architecture:

platform.clients
  → platform.projects
    → platform.project_memberships
    → platform.schema_modules
      → project-specific schema and module data

Important established conventions:

• platform remains private and is never exposed through PostgREST.
• Project discovery is exposed through narrow functions in the api schema.
• Project data lives in a dedicated schema such as kof or bags_by_m.
• Reads use the public Supabase key plus the signed-in user's JWT and remain protected by RLS.
• Writes use narrow SECURITY DEFINER RPCs with explicit membership and role checks.
• Runtime applications do not receive the service-role credential.
• Mutable records carry an optimistic version and reject stale writes.
• Creation RPCs accept client-generated UUID request IDs so retries are idempotent.
• Shared TypeScript request and response contracts are consumed by both API and frontend apps.

Reference snapshots reviewed through Local Code Harness:

bags-by-m does not currently contain tracked database migrations for its product store; its newer project discovery and inventory configuration appear in client-dashboard. The KOF migrations are the stronger baseline for transactional document persistence.

Product Assumptions

This plan assumes:

1. All three approved A Grade users work in the same project and see the same documents.
2. All three may create and edit documents initially.
3. Documents are edited as aggregates; line items are not shared catalog entities.
4. JSON import/export is a backup/admin capability, not the normal workflow.
5. Remote Supabase state becomes canonical before the first production deployment with real data.

If documents must instead remain private per user, the membership and RLS model must be changed before implementing the migration.

Platform Registration

Register the sales app in the existing control plane instead of introducing another organization or workspace abstraction.

platform.clients
  slug: a-grade-contracting
  name: A Grade Contracting

platform.projects
  slug: a-grade-sales
  name: A Grade Sales
  schema_name: agrade
  environment: production

platform.schema_modules
  module_name: documents
  module_version: 1

Provision the three existing auth.users records through platform.project_memberships:

Database membership becomes the real authorization boundary. The browser email allowlist may remain as a fast UX rejection, but API and database authorization must not depend on it.

Proposed Database Shape

platform.project_document_settings

Module-specific project settings follow the existing project_inventory_settings convention.

Keep this table private and grant access only to service_role. Expose the authorized settings through an api.get_my_document_project(project_id) discovery function.

agrade.document_sequences

Document numbers must be allocated in the database because multiple users can create documents at the same time.

Primary key:

(project_id, document_type, document_year)

The create RPC atomically increments the appropriate row and returns a formatted number such as AG-INV-2026-003 or AG-2026-002. The current client-side nextDocumentNumber() helper must stop being authoritative.

agrade.documents

Use a relational envelope around a JSONB document aggregate.

Recommended constraints and indexes:

• Unique (project_id, document_number).
• Check version > 0 and content_schema_version > 0.
• Check total_cents >= 0.
• Check that content is a JSON object.
• Index (project_id, updated_at desc, id) for the dashboard.
• Index (project_id, document_type, document_date desc) for filtering.
• Index (project_id, lower(customer_name)) or add a targeted text-search index when search ships.
• Partial index for active documents where archived_at is null.

Do not normalize line items, parties, add-ons, or acceptance fields into separate tables in the first version. A document is saved and printed as one aggregate, and those nested values are not reused by other records. The relational envelope provides the fields needed for listing, filtering, numbering, authorization, concurrency, and reporting.

RLS And Grants

Enable RLS on both agrade.documents and agrade.document_sequences.

Document reads should allow active members with one of:

owner
developer
client_admin
client_viewer

Writes should allow:

owner
developer
client_admin

Implementation rules:

• Use platform.has_schema_role('agrade', allowed_roles) for the first project-specific policy.
• Also validate the supplied project_id, active client/project state, and documents module inside every write RPC.
• Grant authenticated users SELECT under RLS.
• Revoke direct authenticated INSERT, UPDATE, and DELETE after the RPC paths are deployed.
• Grant RPC execution only to the roles that need it; do not grant anonymous execution.
• Set search_path = '' on every security-definer function and fully qualify referenced objects.

Database RPCs

agrade.create_document(...)

Responsibilities:

• Require auth.uid().
• Validate active project membership and write role.
• Validate that the project has the documents module and schema_name = 'agrade'.
• Accept a UUIDv4 request_id and use it as the document ID.
• Serialize retries with an advisory transaction lock.
• Return the existing matching document when an identical request is retried.
• Allocate the next invoice/proposal number transactionally.
• Derive server-owned fields including actor IDs, status, timestamps, and initial version.
• Validate document type, date, customer, total, and JSON content limits.

agrade.update_document(...)

Responsibilities:

• Require document_id and expected_version.
• Lock or conditionally update only the matching active document.
• Validate the document envelope and JSON content.
• Preserve immutable ID, type, and number unless a future explicit renumber operation is designed.
• Set updated_by = auth.uid().
• Increment version and update updated_at through the shared platform trigger.
• Raise SQLSTATE 40001 when the expected version is stale so the API can return HTTP 409.

agrade.archive_document(...)

Responsibilities:

• Require expected_version.
• Soft-delete by setting status, archived_at, and archived_by.
• Remain idempotent for a repeated successful request.
• Reject stale versions rather than silently archiving a newer edit.

A later restore RPC can clear the archive fields. Avoid hard deletion for business documents.

Shared Contracts

Expand packages/shared so the API and sales client consume the same compile-time and runtime contracts.

Recommended exports:

DocumentStatus
DocumentSummary
StoredDocument
CreateDocumentRequest
CreateDocumentResponse
UpdateDocumentRequest
UpdateDocumentResponse
DocumentListResponse
DocumentProjectAccess
DocumentConflictError

Add runtime schemas, preferably Zod, for external request bodies and persisted JSON content. The API validates before calling Supabase, while the RPC independently enforces authorization, concurrency, size limits, and critical invariants.

The persisted contract should distinguish:

• Database-owned envelope fields: ID, type, number, status, dates, actors, version, and summaries.
• Editable JSON content: parties, line items, add-ons, terms, notes, schedule, and presentation settings.

Before persistence ships, replace non-UUID document IDs with UUIDv4 values and decide whether the editable price contract should move fully to integer cents. At minimum, the API must derive total_cents deterministically and reject non-finite or negative amounts.

Bun API

Add an independently deployable Bun API workspace at apps/api, targeting api.agradecontracting.com. Hono is a natural fit and matches the existing API style.

Suggested routes:

Runtime request flow:

sales.agradecontracting.com
  → Authorization: Bearer <Supabase access token>
  → api.agradecontracting.com
  → Supabase PostgREST using the public key and the same user JWT
  → membership-aware RLS or a narrow write RPC

The API should:

• Validate the bearer token against Supabase Auth.
• Forward the real user JWT rather than replacing it with a service-role identity.
• Resolve the project through api.get_my_document_project().
• Set accept-profile/content-profile to the required custom schema.
• Map PostgreSQL authorization, validation, conflict, and missing-row errors into stable HTTP errors.
• Apply request timeouts and narrow CORS to the local and production sales origins.
• Never receive or expose the Supabase service-role key during normal runtime.

Client Data Flow

Initial load

1. Restore the Supabase Auth session.
2. Fetch the user's accessible projects.
3. Select the A Grade documents project.
4. Fetch remote document summaries.
5. Cache the successful response locally by project and authenticated user.
6. Render remote state as canonical.

Creation

1. Generate a UUIDv4 request ID.
2. Call the create endpoint when a template is selected.
3. Let the database reserve the official document number.
4. Hydrate the selected template with the returned ID, number, version, and timestamps.
5. Open the editor only after the remote draft exists, with a retry-safe pending state.

Saving

1. Validate the editable document locally.
2. Debounce edits and serialize saves so one browser cannot race itself.
3. Send the last known version as expectedVersion.
4. Replace local envelope metadata with the successful response.
5. Update the recovery cache only after a successful remote save.
6. Display clear states: Saving, Saved, Offline—changes retained, or Updated elsewhere.

Do not silently use last-write-wins. On HTTP 409, preserve the local unsaved content and ask the user to reload or compare it with the newer remote document.

Supabase Realtime is not required for the first release. Version conflicts provide the necessary safety without adding subscription lifecycle complexity.

Legacy Browser Migration

The current browser store contains string IDs and seeded example records, so migration must be explicit and idempotent.

Recommended behavior:

1. Fetch remote documents first.
2. Detect the existing user-scoped local-storage collection.
3. Convert every local record to the current shared schema and generate a UUIDv4 request ID.
4. Submit through the normal create API rather than writing directly to Supabase.
5. Treat an existing project/document-number conflict as already migrated and refresh remote state.
6. Mark the local migration complete only after every record succeeds or is positively deduplicated.
7. Retain a timestamped recovery copy locally until the user has verified the remote documents.

Avoid allowing all three users to upload identical seed documents. Prefer seeding the two shared demo documents once through the migration or a guarded administrative seed, then have every client load the same remote records.

Supabase Deployment Requirements

Expose the new application schema through PostgREST, but do not expose platform:

PGRST_DB_SCHEMAS=public,storage,graphql_public,api,kof,bags_by_m,agrade
PGRST_DB_EXTRA_SEARCH_PATH=public,extensions

Preserve any existing schema list rather than replacing it blindly. After changing the environment, recreate the relevant services:

docker compose up -d --force-recreate rest studio

Apply the canonical A Grade migration from this repository through the self-hosted Studio SQL editor or the host's PostgreSQL migration workflow. The application must never apply production migrations automatically at startup.

Implementation Sequence

Phase 1 — Contracts and migration

• Add document runtime schemas and API DTOs to packages/shared.
• Convert persisted document IDs to UUIDv4.
• Decide and document the integer-cents boundary.
• Add the canonical A Grade Supabase migration under apps/api/supabase/migrations.
• Register the A Grade client, project, module, settings, and three memberships.
• Add sequences, documents, triggers, RLS, grants, and RPCs.
• Add authenticated SQL verification queries for every role.

Phase 2 — API

• Scaffold the Bun/Hono apps/api workspace.
• Add Supabase bearer-token authentication.
• Add project discovery and document routes.
• Forward the user JWT to PostgREST.
• Validate requests using packages/shared schemas.
• Map stale versions to HTTP 409.
• Add unit tests for authorization, validation, idempotency, and conflict behavior.

Phase 3 — Sales client

• Add the authenticated API client.
• Replace local document initialization with remote project/document loading.
• Create numbered drafts through the API.
• Add serialized debounced saving with visible state.
• Implement conflict and offline-recovery behavior.
• Replace delete with archive semantics.
• Implement one-time browser-data migration.
• Move JSON import/export under an advanced backup surface.

Phase 4 — Deployment and verification

• Expose the api and agrade PostgREST schemas without exposing platform.
• Apply the migration before deploying API code that calls its RPCs.
• Deploy apps/api to api.agradecontracting.com.
• Deploy apps/sales to sales.agradecontracting.com.
• Verify CORS, Auth redirects, and password recovery on both localhost and production.
• Verify all three users see the same document collection.
• Verify a client_viewer can read but cannot write.
• Simulate two concurrent editors and confirm the stale save returns 409.
• Create simultaneous invoices and confirm document numbers remain unique.
• Verify no runtime deployment contains a Supabase service-role credential.

Release Gate

The remote document store is ready for the first production sales deployment when:

• Memberships, RLS, and RPC grants pass authenticated database checks.
• Creating the same request twice returns one document.
• Concurrent creation cannot duplicate a document number.
• A stale save cannot overwrite a newer version.
• Reloading or signing in from another browser shows the latest saved documents.
• Local migration is idempotent and retains a recovery copy.
• The API and sales production builds and repository-wide typecheck pass.
• Browser-facing environments contain only the public Supabase key.

Deliberately Deferred

These capabilities should not block the first remote-persistence release:

• Supabase Realtime subscriptions.
• Fine-grained field-level collaborative editing.
• Normalized line-item tables.
• Customer/contact master records.
• Immutable issued-document revision history or generated PDF storage.
• Payment tracking beyond a basic lifecycle status.
• Hard deletion of business documents.

Immutable issued snapshots are a strong follow-up once the issue/send workflow is designed, but autosaving every keystroke into a revision table would add cost and noise without improving the first release.