# KLIGO data model and migration plan

> Scope update (12 September 2026): `LAUNCH_SCOPE.md` takes precedence. Website first; native app later; all four categories. KLIGO bills subscriptions and paid placements only. Historical buyer-deal checkout, commission, escrow, booking-payment and payout proposals below are excluded from launch. See `MATERIALS_HANDOFF.md` for new/surplus material fields.


> Historical architecture baseline, supplemented on 12 September 2026. Read `HANDOFF_READINESS.md` and `CURRENT_ROUTE_INVENTORY.md` first. The current public profile is `/providers/[slug]`; `/marketplace` redirects to `/`; Projects is retired. Legal drafts now exist. Project subtype proposals and older route counts below are not current launch requirements. Vendor references are candidates, not approved integrations.

## Purpose and status

This document turns the shared contracts in `APPLICATION_ARCHITECTURE.md` into a proposed Cloudflare D1/SQLite schema. It is architecture preparation only: no database is bound, no migration is generated and no prototype screen is connected by this change.

The model is intentionally normalized around authorization, marketplace discovery and workflow integrity. JSON is reserved for versioned snapshots, vendor payload fragments and flexible specifications that are not primary filters. D1 is the system of record for relational metadata; R2 stores file bytes.

## Storage conventions

- Use opaque text IDs generated by the application (UUIDv7 or ULID). Never expose sequential row identifiers.
- Store timestamps as UTC ISO-8601 text with millisecond precision. Use `created_at`, `updated_at` and optional `deleted_at` consistently.
- Store money as integer minor units and an ISO 4217 currency code. Initial currency is `ILS`; never use floating-point money.
- Store booleans as `INTEGER NOT NULL CHECK (value IN (0, 1))`.
- Use explicit status columns with `CHECK` constraints for stable platform states. Keep vendor-specific states in event metadata, not the domain status column.
- Use lowercase normalized email for matching while preserving a display form only if needed. Store phone numbers in E.164.
- Give fixture-capable root records a `data_origin` value of `production`, `demo` or `imported`; downstream analytics must join to and exclude non-production roots.
- Enable foreign-key enforcement for application sessions. Every relationship below is an explicit foreign key unless noted as a polymorphic target.
- Use soft deletion only where history or referential integrity requires it. Immutable financial, audit, event and moderation records are never soft-deleted as a substitute for reversal records.
- Add indexes from the query inventory below, then validate them with `EXPLAIN QUERY PLAN`; do not index every foreign key automatically.

## Relationship model

### Identity and provider ownership

```mermaid
erDiagram
    USERS ||--o{ USER_ROLES : receives
    USERS ||--o{ PROVIDER_MEMBERSHIPS : joins
    PROVIDER_ORGANIZATIONS ||--o{ PROVIDER_MEMBERSHIPS : has
    USERS ||--o{ CONSENTS : accepts
    PROVIDER_ORGANIZATIONS ||--o{ ADDRESSES : owns
```

### Marketplace and commercial workflow

```mermaid
erDiagram
    PROVIDER_ORGANIZATIONS ||--o{ LISTINGS : publishes
    LISTINGS ||--o{ LISTING_MEDIA : contains
    USERS ||--o{ REQUESTS : creates
    REQUESTS ||--o{ QUOTES : receives
    QUOTES ||--o| BOOKINGS : becomes
    BOOKINGS ||--o{ PAYMENTS : settles
```

### Communication and trust

```mermaid
erDiagram
    CONVERSATIONS ||--o{ CONVERSATION_PARTICIPANTS : includes
    CONVERSATIONS ||--o{ MESSAGES : contains
    USERS ||--o{ SAVED_ITEMS : saves
    USERS ||--o{ REPORTS : files
    REPORTS ||--o{ REPORT_EVIDENCE : contains
    VERIFICATION_CASES ||--o{ VERIFICATION_DOCUMENTS : contains
```

Polymorphic references such as `target_type + target_id` cannot use a normal SQLite foreign key. They are allowed only for conversations, saved items, reports, verification subjects, moderation actions, promotion targets, audit events and domain events. Each domain service must validate that the typed target exists and that the actor may reference it before writing.

## Table catalog

The column lists below contain the business columns that must be stable across route handlers. Every mutable table also carries `created_at` and `updated_at`; optional deletion timestamps are called out explicitly.

### Identity, authorization and consent

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `users` | `id`, `auth_subject`, `email_normalized`, `phone_e164`, `display_name`, `status`, `locale`, `data_origin`, `email_verified_at`, `phone_verified_at`, `last_seen_at`, `deleted_at` | Unique `auth_subject`; partial unique verified email/phone where policy allows; status in `active, suspended, deletion_pending, deleted`; locale in approved launch locales |
| `user_roles` | `id`, `user_id`, `role`, `scope_type`, `scope_id`, `granted_by`, `granted_at`, `revoked_at` | Role in `customer, provider_member, provider_admin, moderator, admin`; unique active grant by user/role/scope; global roles require null scope, provider roles require provider scope |
| `provider_organizations` | `id`, `legal_name`, `display_name`, `slug`, `registration_number`, `status`, `verification_status`, `data_origin`, `public_phone`, `public_email`, `billing_email`, `deleted_at` | Unique active slug; status in `draft, active, suspended, closed`; verification in `unverified, pending, verified, expired, rejected, revoked`; legal/billing fields never enter public projections by default |
| `provider_memberships` | `id`, `provider_id`, `user_id`, `membership_role`, `status`, `invited_by`, `invited_at`, `accepted_at`, `ended_at` | Unique active membership per provider/user; role in `member, manager, owner, billing`; at least one owner must remain for an active organization |
| `addresses` | `id`, `owner_user_id`, `provider_id`, `label`, `formatted_address`, `place_id`, `latitude_e6`, `longitude_e6`, `precision`, `visibility`, `city`, `region`, `country_code` | Exactly one owner foreign key is non-null; coordinates as scaled integers; country initially `IL`; precision in `exact, approximate, city`; exact addresses never enter public projections |
| `consents` | `id`, `user_id`, `document_type`, `document_version`, `decision`, `accepted_at`, `withdrawn_at`, `ip_hash`, `user_agent_hash` | Unique decision event, not an overwritten flag; terms/privacy acceptance cannot be inferred from marketing consent |
| `audit_events` | `id`, `actor_user_id`, `actor_role`, `action`, `target_type`, `target_id`, `request_id`, `reason_code`, `metadata_json`, `created_at` | Append-only; metadata allowlist must exclude secrets, message bodies and evidence contents |

The authentication provider remains the credential authority. D1 stores the stable external `auth_subject`, roles, organization membership and application lifecycle; it must not store passwords, OTPs, recovery secrets or OAuth tokens unless an approved identity integration explicitly requires encrypted server-side tokens.

### Taxonomy, listings and availability

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `categories` | `id`, `parent_id`, `slug`, `name_he`, `name_ar`, `name_en`, `kind`, `sort_order`, `status` | Unique slug; acyclic parent relationship enforced by service; status in `active, hidden, retired` |
| `listings` | `id`, `provider_id`, `type`, `slug`, `title`, `description`, `category_id`, `status`, `data_origin`, `price_mode`, `price_minor`, `currency`, `service_area_type`, `service_radius_m`, `published_at`, `version`, `deleted_at` | Type in `equipment, service, project, part, material`; unique active slug; nonnegative price; optimistic `version`; only `published` enters public queries |
| `listing_equipment` | `listing_id`, `make`, `model`, `model_year`, `equipment_class`, `condition`, `operator_option`, `delivery_option`, `inspection_due_at` | Exactly one row for an equipment listing; year and inspection rules validated server-side |
| `listing_services` | `listing_id`, `lead_time_days`, `capacity_note` | Exactly one row for a service listing |
| `listing_service_trades` | `listing_id`, `category_id` | Composite primary key; normalized filter values |
| `listing_service_project_types` | `listing_id`, `project_type_code` | Composite primary key; code must exist in managed taxonomy/configuration |
| `listing_projects` | `listing_id`, `opportunity_type`, `scope_summary`, `starts_at`, `ends_at`, `tender_deadline_at` | Exactly one row for a project listing; deadline must precede start where supplied |
| `listing_parts` | `listing_id`, `part_number`, `condition`, `quantity`, `unit` | Quantity positive; compatibility stored in join table |
| `listing_part_compatibility` | `listing_id`, `make`, `model`, `year_from`, `year_to` | Valid year range; normalized text fields for search/filter |
| `listing_materials` | `listing_id`, `grade`, `specification`, `unit`, `minimum_quantity`, `delivery_capable` | Positive minimum quantity; canonical unit from managed code set |
| `listing_media` | `id`, `listing_id`, `upload_id`, `kind`, `sort_order`, `alt_text_he`, `alt_text_ar`, `alt_text_en`, `width`, `height`, `moderation_status`, `published_at` | Unique listing/sort order; only clean, approved derivatives may be public |
| `listing_service_areas` | `id`, `listing_id`, `place_id`, `city`, `region`, `center_latitude_e6`, `center_longitude_e6`, `radius_m` | One of place/radius or later polygon reference; never reuse private jobsite address |
| `availability_blocks` | `id`, `listing_id`, `starts_at`, `ends_at`, `status`, `capacity`, `source`, `note`, `version` | End after start; capacity positive; status in `available, held, booked, maintenance, unavailable`; conflict policy enforced in service |
| `listing_slug_history` | `id`, `listing_id`, `slug`, `replaced_at` | Unique historical slug; provides deterministic redirect behavior |

`listings.type` determines the one-to-one subtype row. The application must create, update and remove core and subtype data in one D1 `batch()` boundary. Database checks cannot fully enforce “exactly one matching subtype,” so repository tests and publish-time validation must enforce it.

### Requests, matching, quotes and bookings

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `requests` | `id`, `customer_id`, `category_id`, `title`, `details`, `address_id`, `required_starts_at`, `required_ends_at`, `status`, `visibility`, `data_origin`, `source_type`, `source_id`, `version`, `submitted_at`, `cancelled_at`, `deleted_at` | Owner is a customer user; end after start; status in `draft, submitted, matching, quoted, booked, cancelled, expired`; exact address redacted until authorized stage |
| `request_items` | `id`, `request_id`, `listing_type`, `listing_id`, `quantity_milli`, `unit`, `specification_json`, `operator_required`, `delivery_required`, `sort_order` | Positive fixed-point quantity; listing reference optional; JSON size/schema bounded by listing type |
| `request_matches` | `id`, `request_id`, `provider_id`, `listing_id`, `score_basis_points`, `reason_codes_json`, `state`, `matched_at`, `viewed_at`, `declined_at` | Unique request/provider/listing tuple; state in `eligible, notified, viewed, declined, quoted, withdrawn`; score is ordering only |
| `quotes` | `id`, `request_id`, `provider_id`, `status`, `current_version`, `valid_until`, `currency`, `version`, `sent_at`, `accepted_at`, `withdrawn_at` | One active lineage per request/provider; status in `draft, sent, accepted, declined, withdrawn, expired`; accepted request has at most one quote |
| `quote_versions` | `quote_id`, `version_number`, `scope`, `subtotal_minor`, `tax_minor`, `fee_minor`, `total_minor`, `terms`, `exclusions`, `availability_promise`, `created_by`, `created_at`, `sent_at` | Composite primary key; amounts nonnegative and total equation checked; sent rows immutable |
| `quote_items` | `id`, `quote_id`, `version_number`, `description`, `quantity_milli`, `unit`, `unit_price_minor`, `tax_minor`, `total_minor`, `sort_order` | Foreign key to quote version; fixed-point arithmetic and server recalculation |
| `bookings` | `id`, `request_id`, `quote_id`, `customer_id`, `provider_id`, `status`, `scheduled_starts_at`, `scheduled_ends_at`, `address_snapshot_json`, `scope_snapshot_json`, `subtotal_minor`, `tax_minor`, `fee_minor`, `total_minor`, `currency`, `cancellation_policy_code`, `cancellation_policy_version`, `version`, `provider_confirmed_at`, `completed_at`, `cancelled_at` | Unique request and quote; snapshot required before confirmation; status in `draft, awaiting_provider, confirmed, in_progress, completed, cancellation_pending, cancelled, disputed` |
| `booking_events` | `id`, `booking_id`, `event_type`, `actor_user_id`, `actor_role`, `prior_status`, `new_status`, `reason_code`, `metadata_json`, `created_at` | Append-only timeline; every status change writes one event in the same batch |

Accepting a quote is one command boundary: re-read request, quote and availability versions; verify ownership and eligibility; mark the winning quote accepted; expire or decline competing sent quotes; create the booking snapshot; reserve availability; append events. Use D1 prepared statements passed to `batch([...])`. The command requires an idempotency key and must return the existing result on safe replay.

### Payments, subscriptions and promotions

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `billing_accounts` | `id`, exactly one of `owner_user_id` or `provider_id`, `provider_name`, `provider_customer_ref`, `billing_name`, `tax_identifier`, `billing_address_id`, `status` | Explicit owner foreign key; provider reference unique within the PSP; legal fields excluded from public/account-summary projections |
| `payment_methods` | `id`, `billing_account_id`, `provider_method_ref`, `type`, `brand`, `last_four`, `expiry_month`, `expiry_year`, `status`, `is_default` | Display-safe metadata only; unique PSP method reference; no PAN, CVV or bank credentials |
| `payments` | `id`, `booking_id`, `subscription_id`, `campaign_id`, `provider_name`, `provider_payment_ref`, `status`, `amount_minor`, `currency`, `idempotency_key`, `authorized_at`, `captured_at`, `failed_at` | Exactly one payable owner; unique provider reference when present; status in `pending, requires_action, authorized, captured, failed, cancelled, partially_refunded, refunded`; no card data |
| `payment_events` | `id`, `payment_id`, `provider_name`, `provider_event_id`, `event_type`, `payload_hash`, `received_at`, `processed_at`, `processing_outcome`, `failure_code` | Unique provider/event ID; store allowlisted parsed metadata separately if needed, not unrestricted sensitive payloads |
| `refunds` | `id`, `payment_id`, `provider_refund_ref`, `amount_minor`, `currency`, `reason_code`, `status`, `requested_by`, `requested_at`, `completed_at` | Positive amount; cumulative successful refunds cannot exceed captured amount |
| `plans` | `id`, `code`, `version`, `billing_cadence`, `price_minor`, `currency`, `entitlements_json`, `active_from`, `active_until` | Unique code/version; immutable once subscribed; bounded entitlement schema |
| `subscriptions` | `id`, `provider_id`, `plan_id`, `provider_name`, `provider_customer_ref`, `provider_subscription_ref`, `status`, `period_starts_at`, `period_ends_at`, `cancel_at`, `ended_at` | One current subscription per provider; webhook-authoritative status |
| `promotion_campaigns` | `id`, `provider_id`, `target_type`, `target_id`, `placement_code`, `status`, `starts_at`, `ends_at`, `budget_minor`, `currency`, `attribution_model_version` | Provider must own target; status in `draft, pending_payment, scheduled, active, paused, completed, cancelled, rejected` |
| `invoices` | `id`, `billing_account_id`, `payment_id`, `provider_invoice_ref`, `invoice_number`, `status`, `subtotal_minor`, `tax_minor`, `total_minor`, `currency`, `issued_at`, `due_at`, `paid_at`, `document_upload_id` | Unique provider invoice reference/number as applicable; document uses authorized file access; status follows approved accounting policy |

No payment tables should be activated until the commercial model and PSP are approved. Their schema isolates provider references so a PSP adapter can change without rewriting booking records.

### Messaging, saved items and notifications

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `conversations` | `id`, `context_type`, `context_id`, `status`, `created_by`, `last_message_at`, `closed_at` | Unique active context/participant-set policy enforced by service; status in `open, closed, archived` |
| `conversation_participants` | `conversation_id`, `user_id`, `participant_role`, `joined_at`, `last_read_message_id`, `last_read_at`, `left_at` | Composite primary key; read cursor only moves forward |
| `messages` | `id`, `conversation_id`, `sender_id`, `type`, `body`, `reply_to_id`, `created_at`, `edited_at`, `deleted_at` | Sender must be active participant; type in `text, attachment, system`; sanitized body length bounded |
| `message_attachments` | `message_id`, `upload_id`, `sort_order` | Composite primary key; upload must be complete, clean and owned by participant |
| `saved_items` | `user_id`, `target_type`, `target_id`, `created_at` | Composite primary key makes save/unsave idempotent; target types restricted to public-saveable domains |
| `notification_preferences` | `user_id`, `channel`, `topic`, `enabled`, `quiet_hours_json`, `updated_at` | Composite primary key; legal/transactional topics cannot be silently treated as marketing consent |
| `notifications` | `id`, `user_id`, `topic`, `channel`, `template_code`, `template_version`, `data_json`, `status`, `dedupe_key`, `scheduled_at`, `sent_at`, `failed_at` | Unique dedupe key where supplied; payload schema allowlisted; no sensitive details in SMS/email previews |

### Uploads, verification, reports and moderation

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `uploads` | `id`, `owner_user_id`, `purpose`, `object_key`, `original_filename`, `declared_mime`, `detected_mime`, `size_bytes`, `checksum_sha256`, `status`, `scan_status`, `visibility`, `created_at`, `completed_at`, `expires_at`, `deleted_at` | Unique object key; server chooses key; statuses `initiated, uploaded, processing, ready, rejected, expired, deleted`; private by default |
| `upload_derivatives` | `id`, `upload_id`, `kind`, `object_key`, `mime_type`, `width`, `height`, `size_bytes`, `created_at` | Unique upload/kind; publish only generated derivatives approved for public use |
| `verification_cases` | `id`, `subject_type`, `subject_id`, `level`, `status`, `submitted_at`, `assigned_reviewer_id`, `reviewed_at`, `expires_at`, `decision_reason_code`, `version` | One active case per subject/level; status in `draft, submitted, in_review, needs_information, approved, rejected, expired, revoked` |
| `verification_documents` | `id`, `case_id`, `upload_id`, `document_type`, `country_code`, `document_expires_at`, `review_result`, `reviewed_at` | Upload purpose must be verification and remain private; raw object key never returned to clients |
| `reports` | `id`, `reporter_user_id`, `target_type`, `target_id`, `booking_id`, `reason_code`, `narrative`, `severity`, `status`, `assigned_moderator_id`, `submitted_at`, `resolved_at`, `version` | Rate-limited; status in `submitted, triaged, investigating, actioned, dismissed, resolved, appealed`; reporter identity private by default |
| `report_evidence` | `id`, `report_id`, `upload_id`, `submitted_by`, `visibility`, `created_at` | Evidence upload purpose required; visibility in `staff_only, reporter_and_staff, case_parties, legal_hold` |
| `moderation_actions` | `id`, `report_id`, `actor_user_id`, `target_type`, `target_id`, `action`, `policy_code`, `policy_version`, `reason`, `prior_state_json`, `new_state_json`, `created_at` | Append-only; staff authorization and assignment checked; destructive outcomes represented by new actions |
| `report_appeals` | `id`, `report_id`, `appellant_user_id`, `reason`, `status`, `assigned_reviewer_id`, `submitted_at`, `decided_at` | Original decision remains immutable; reviewer separation policy configurable |

### Reliability and integration tables

| Table | Key columns | Required constraints and notes |
| --- | --- | --- |
| `idempotency_keys` | `actor_key`, `operation`, `key`, `request_hash`, `response_status`, `response_json`, `resource_type`, `resource_id`, `expires_at`, `created_at` | Composite primary key; same key with a different request hash is a conflict; response excludes secrets |
| `domain_events` | `id`, `event_name`, `aggregate_type`, `aggregate_id`, `aggregate_version`, `actor_type`, `actor_id`, `correlation_id`, `causation_id`, `payload_json`, `occurred_at` | Append-only; actor type supports user, system and verified webhook; unique aggregate/version/event identity where applicable; bounded versioned payload |
| `outbox_messages` | `id`, `domain_event_id`, `destination`, `status`, `attempt_count`, `available_at`, `locked_at`, `delivered_at`, `last_error_code` | Unique event/destination; retry with bounded backoff; never place secrets in payload/error |
| `webhook_receipts` | `id`, `provider_name`, `provider_event_id`, `signature_verified`, `payload_hash`, `received_at`, `processed_at`, `outcome`, `error_code` | Unique provider/event ID before domain handling; retain only the minimum vendor data required for reconciliation |

## Query-driven index plan

Primary keys and declared unique constraints create their own indexes. Add the following only when the corresponding repository query is implemented.

| Query | Index | Notes |
| --- | --- | --- |
| Public marketplace by type/category and recency | `idx_listings_public_type_category_published` on `(type, category_id, published_at DESC)` where `status = 'published' AND deleted_at IS NULL` | Supports category pages; separate type-only index only if plans show it is needed |
| Provider workspace listing status | `idx_listings_provider_status_updated` on `(provider_id, status, updated_at DESC)` | Uses provider as leftmost ownership predicate |
| Location candidate filtering | `idx_listing_service_areas_region_city` on `(region, city, listing_id)` | D1 narrows candidates; application computes radius until geo needs justify a service |
| Customer request list | `idx_requests_customer_status_updated` on `(customer_id, status, updated_at DESC)` | Own-record list and status tabs |
| Provider match inbox | `idx_request_matches_provider_state_matched` on `(provider_id, state, matched_at DESC)` | Match ID still checked against organization membership |
| Request quote comparison | `idx_quotes_request_status_sent` on `(request_id, status, sent_at DESC)` | Sent/active quotes only in customer projection |
| Booking lists | `idx_bookings_customer_status_schedule` on `(customer_id, status, scheduled_starts_at)` and provider equivalent | Two actor-specific query patterns justify two indexes |
| Conversation list | `idx_conversation_participants_user_joined` on `(user_id, left_at, conversation_id)` plus `idx_conversations_last_message` on `(last_message_at DESC)` | Verify actual join plan; denormalized inbox read model only if needed later |
| Message polling | `idx_messages_conversation_created` on `(conversation_id, created_at, id)` | Stable cursor uses timestamp plus opaque ID |
| Moderation queue | `idx_reports_queue_severity_submitted` on `(status, severity DESC, submitted_at)` where `status IN ('submitted','triaged','investigating','appealed')` | Partial operational queue index |
| Verification expiry | `idx_verification_cases_status_expiry` on `(status, expires_at)` where `status = 'approved'` | Scheduled expiry scan |
| Upload cleanup | `idx_uploads_status_expiry` on `(status, expires_at)` where `status IN ('initiated','uploaded','rejected','expired')` | Orphan/quarantine cleanup |
| Outbox delivery | `idx_outbox_status_available` on `(status, available_at)` where `status IN ('pending','retry')` | Worker delivery scan |

After adding an index, run representative `EXPLAIN QUERY PLAN` statements and `PRAGMA optimize`. Remove redundant indexes whose leftmost columns are already covered and whose query plan shows no benefit.

## Validation and invariant rules

### Request boundary

- Parse every mutation with a shared Zod contract before a domain service runs. Reject unknown security-sensitive fields.
- Normalize whitespace, emails, phone numbers, locale codes, slugs and controlled codes server-side.
- Cap text, JSON-array and attachment counts. Never rely on UI `maxlength` or file accept attributes.
- Reject client-supplied owner IDs, status, prices derived from trusted records, moderation outcomes and PSP state.
- Require `expectedVersion` for mutable aggregate updates. Return `409 VERSION_CONFLICT` with the current safe projection.
- Require idempotency keys for request submission, quote sending/acceptance, booking transitions, payment/refund creation, message sending when retried, uploads completion and moderation decisions.

### Money and quantity

- Calculate quote, booking, payment and refund totals on the server using integers.
- Use fixed-point `quantity_milli` for up to three quantity decimals; define rounding per unit and tax policy before live billing.
- Require one currency throughout a quote/booking/payment lineage.
- Check `subtotal + tax + fee = total`; line totals must reconcile to the version total under an approved rounding rule.
- Never accept a refund total above captured minus already successful refunds.

### Time and location

- Require end timestamps after start timestamps and normalize user-entered local time using an explicit IANA timezone; default display timezone may be `Asia/Jerusalem`, but stored time is UTC.
- Tender deadlines and quote validity must be future-dated when sent.
- Re-check booking availability at quote acceptance and provider confirmation; draft checks are advisory only.
- Treat geocoding results as suggestions until the user confirms the place. Store place ID, normalized label and coordinates separately.
- Expose city/region or deliberately rounded coordinates publicly. Exact jobsite coordinates/address are limited to the customer, authorized provider after the approved stage, and case-scoped staff.

### Files

- Server generates R2 object keys and upload purpose; clients cannot choose bucket paths or final visibility.
- Enforce per-purpose file count, byte size, detected MIME, image dimensions and allowed extensions; compute SHA-256.
- Keep uploads private and quarantined until completion and scanning/processing succeed.
- Public listing pages reference approved derivative IDs, never raw R2 object keys.
- Verification and report evidence access is short-lived, logged and role/case scoped. Downloads use safe filenames and `Content-Disposition`.

### State transitions

Only commands named in `API_AND_AUTHORIZATION_CONTRACT.md` may move these aggregates. A direct generic status patch is forbidden.

| Aggregate | Allowed primary transitions |
| --- | --- |
| Listing | `draft -> in_review -> published -> paused -> archived`; moderation may move eligible states to `suspended`, with a recorded restoration action |
| Request | `draft -> submitted -> matching -> quoted -> booked`; eligible open states may move to `cancelled` or `expired` |
| Quote | `draft -> sent -> accepted`; sent may move to `declined, withdrawn, expired`; acceptance is exclusive per request |
| Booking | `draft -> awaiting_provider -> confirmed -> in_progress -> completed`; policy may route active states through `cancellation_pending -> cancelled` or `disputed` |
| Upload | `initiated -> uploaded -> processing -> ready`; failure branches to `rejected, expired, deleted` |
| Verification | `draft -> submitted -> in_review -> approved/rejected`; review may request information; approval may later become `expired` or `revoked` |
| Report | `submitted -> triaged -> investigating -> actioned/dismissed -> resolved`; eligible decisions may create a linked appeal |

Every transition must authenticate, authorize, validate the current version/state, execute all related writes as one command boundary, append domain/audit records and return a role-redacted result.

## Migration sequence

Use Drizzle schema in `db/schema.ts` and generated, reviewed SQL in `drizzle/`. Numbering below is conceptual; actual filenames come from the project's migration generator. Applied migration files and matching metadata are immutable.

| Migration | Scope | Dependency and exit check |
| --- | --- | --- |
| `0001_identity_and_audit` | Users, roles, organizations, memberships, addresses, consents, audit events | Bootstrap procedure creates the first admin without a public elevation path; cross-organization authorization tests pass |
| `0002_taxonomy_and_listings` | Categories, listing core/subtypes, compatibility, areas, slug history | Seed taxonomy through a separate controlled seed command; one provider draft can be queried without static arrays |
| `0003_uploads_and_listing_media` | Upload metadata, derivatives and listing media | Bind R2 only when this slice starts; unauthorized/raw-object access fails |
| `0004_availability` | Availability blocks and query indexes | Overlap and version-conflict tests pass for one listing |
| `0005_requests_and_matching` | Requests, items, matches | Submission batch and deterministic eligibility fixtures pass |
| `0006_quotes_and_bookings` | Quotes, immutable versions/items, bookings/events | Concurrent acceptance test produces one booking and one winning quote |
| `0007_messaging_and_saved` | Conversations, participants, messages, attachments, saved items | Cross-participant reads fail; polling cursor has a verified query plan |
| `0008_trust_and_moderation` | Verification cases/documents, reports/evidence/actions/appeals | Evidence ACL and append-only staff action tests pass |
| `0009_commercial` | Billing accounts/method metadata, plans, subscriptions, payments/events, refunds, campaigns and invoices | Create schema only after commercial approval; PSP replay fixtures are idempotent |
| `0010_notifications_and_delivery` | Preferences, notifications, domain events, outbox, webhook receipts, idempotency keys | Retry/deduplication and privacy-safe payload checks pass |

This order establishes identity and ownership before user data, file metadata before file references, and workflow parents before their events. A later implementation may split a conceptual migration into smaller bounded files; it must not combine unrelated domains into a single large migration.

## Safe migration procedure

1. Change `db/schema.ts` for one bounded capability and generate a new Drizzle migration.
2. Inspect every generated statement for D1 compatibility, complete statements, foreign keys and unintended destructive changes.
3. Keep schema migrations schema-only. Run taxonomy seed data, demo fixtures and large backfills through separate versioned commands.
4. Prefer expand-and-contract: add nullable columns/tables first, deploy code that writes both shapes if needed, backfill separately, switch reads, then remove old structure in a later approved migration.
5. For an existing table, use constant defaults on added ordinary columns. A new `NOT NULL` column needs a non-null constant default; an added foreign-key column must initially be nullable.
6. Prepare each SQL statement separately in application code. Use D1 `batch([...])` for multi-statement command boundaries; do not concatenate statements into one `prepare()` call.
7. Test on an empty database and a representative prior-schema snapshot. Verify foreign-key failures, unique constraints and the important query plans.
8. Commit the generated SQL and its matching Drizzle metadata together. Never edit an already-applied file or snapshot.
9. Treat a partially applied publication as real state. If the applied/unapplied boundary is uncertain, stop and inspect it before creating a correction.
10. Keep a pre-release export/restore procedure and rehearse forward recovery. SQLite schema rollback is not a substitute for restoring user records changed by application code.

## Demo data and production separation

- Static arrays remain prototype fixtures until each repository slice is connected. They are not migration seed data.
- Keep deterministic development fixtures in a separate seed module or command, never in schema migrations.
- Mark seeded records with an explicit data-origin field or dedicated tenant/environment boundary. Public sample records must show a sample label.
- Production analytics, ranking, provider performance, billing and moderation metrics must exclude demo records by construction.
- Do not silently copy prototype identities, messages, bookings, reports, payments or verification evidence into production.
- Taxonomy/configuration seed data may be promoted only through a reviewed, idempotent command with stable codes and translations.

## Retention and deletion baseline

Exact periods require legal approval; the system still needs lifecycle classes before collection begins.

| Data class | Baseline behavior pending policy |
| --- | --- |
| Account/profile | On deletion request, disable access immediately; schedule erasure or irreversible pseudonymization while preserving legally required transaction references |
| Public listings/media | Unpublish immediately; delete unused originals/derivatives after the recovery window unless attached to an active case or legal hold |
| Draft requests/quotes | Expire abandoned drafts on a documented schedule; delete unattached uploads earlier |
| Bookings/payments/refunds | Retain the minimum legally required financial and dispute record; separate it from mutable profile data |
| Messages | Define retention and user-visible deletion semantics before launch; legal holds override routine deletion with audited access |
| Verification documents | Shortest practical retention; expire access and delete raw documents when verification/legal purpose ends |
| Reports/evidence/moderation | Case-based retention with explicit legal hold, access logging and restricted staff roles |
| Audit/domain/webhook events | Retain structured minimum necessary for security, reconciliation and incident response; never use them as a shadow copy of sensitive content |

Deletion jobs must be idempotent, record an audit outcome, respect legal holds and clean both the D1 metadata and corresponding R2 objects. An R2 deletion failure leaves a retryable tombstone rather than falsely marking the object erased.

## Implementation readiness gates

Before the first D1-backed screen is connected:

- approve identity, role and provider ownership decisions;
- enable the logical `DB` binding and establish environment-specific database handling;
- implement the first migration plus repository helpers, current actor and authorization policies;
- define the seed/demo boundary and admin bootstrap path;
- verify migrations from empty and prior schema states;
- prove ownership isolation and version conflicts with repository/integration tests;
- document backup/export, recovery and migration incident ownership.

Before the first upload is accepted, bind R2, approve purpose-specific limits and retention, and provide scanning/processing behavior. Before live money or verification evidence, complete the separate legal, vendor, operational and credential gates in `INTEGRATION_CHECKLIST.md`.