Data authority & RLS foundation (tenants/memberships/roles/permissions/assignments, auth helpers, deny-by-default membership RLS, schema+migration contract, demo parity)

At a glance

Goals

Consolidate the data-model layers in schema.sql (idempotent) with a documented owner-scoped RLS upgrade path (data-model.md).

Spec document

P6-data-model-layers → · source ↗

Implementation — what to build

Checklist

• ○ Canonical layer: estate_sales, estate_sale_items, estate_sale_item_photos, estate_sale_ai_runs, estate_sale_exports, seller_profiles, discount_phases.
• ○ Marketplace/buyer + seller-intelligence layers (taxonomy/tags, saved_searches, watches, demand_signals, wishlist_matches, appraisal_recommendations, notifications, token_ledger…).
• ○ Tracking-identity (qr_codes / item_identity_bindings), disposition (buyer_offers / disposition_recommendations), ingestion layers.
• ○ inventory_status_events as the single audit trail for status + disposition transitions.
• ○ Idempotent DDL (run whole file) + taxonomy seed; document RLS: MVP permissive → owner_id = auth.uid(), buyer tables by buyer_id.

Gates & testing

• ○ P6 Data-model layers are consolidated (canonical, marketplace, seller-intelligence, tracking-identity, disposition, ingestion) with a documented RLS owner-scoped upgrade path.
  CODE TEST HITL QA

Requirements

• REQ-PLAT-002 accepted Data-model layers must be consolidated with a documented RLS upgrade path.
  Done when: Canonical, marketplace, seller-intelligence, tracking-identity, disposition, and ingestion layers are documented with an owner-scoped RLS path.
  Spec: docs/technical/data-model.md · supabase/schema.sql
• REQ-PLAT-005 accepted The canonical seller data layer must model sales, items (with lifecycle/appraisal/demand fields), photos, AI runs, exports, seller profiles, and discount phases.
  Done when: estate_sales, estate_sale_items, estate_sale_item_photos, estate_sale_ai_runs, estate_sale_exports, seller_profiles, discount_phases exist in schema.sql with the documented fields.
  Spec: docs/technical/data-model.md (Canonical)
• REQ-PLAT-006 accepted The marketplace/buyer data layer must model taxonomy/tags, buyer profiles, saved searches, watches, saves, wanted items, match alerts, followers, notifications, tokens, submissions, discount codes, and appraisals.
  Done when: The listed buyer tables exist with buyer_id scoping ready for Auth.
  Spec: docs/technical/data-model.md (Marketplace/buyer)
• REQ-PLAT-007 accepted The seller-intelligence layer must model alerts, demand signals, wishlist matches, appraisal recommendations, and status events.
  Done when: seller_alerts, item_demand_signals, wishlist_matches, appraisal_recommendations, inventory_status_events exist.
  Spec: docs/technical/data-model.md (Seller intelligence)
• REQ-PLAT-008 accepted Documented upgrade paths must exist for pgvector (semantic search) and PostGIS (geo).
  Done when: Search/geo helpers (troveSearch haversine; FTS/RPC/Meilisearch later) have a documented path to pgvector/PostGIS without contract changes.
  Spec: docs/technical/architecture.md (Data)
• REQ-SEC-003 accepted Public QR/buyer pages must never expose private notes, internal pricing guidance, margin, consignor details, or seller PII.
  Done when: Public QR payloads use opaque IDs and resolve to buyer-safe pages; staff/binding views are auth-gated; internal pricing is hidden from buyers.
  Spec: docs/product/capture-to-sale-tracking.md (QR rules) · docs/product/appraisal-workflows.md
• REQ-SEC-005 accepted RLS must move from MVP-permissive to owner-scoped on a documented path, and buyer tables scope by buyer_id once Auth lands.
  Done when: owner_id = auth.uid() policies are the documented upgrade for seller tables; buyer_id scoping activates with Auth.
  Spec: docs/technical/data-model.md (RLS)

Verify (done when)

Foundation/Authority domain (tenants, tenant_memberships, roles, permissions, sale_team_assignments) + authorization helper contract; deny-by-default, membership/permission-based RLS architecture with insert/update WITH CHECK; the additive upgrade-path stages (owner-only -> team -> sale-scoped); schema.sql current-state bootstrap + ordered-migration contract + generated types + demo-fixture parity; empty-DB + upgrade migration tests + tenant-isolation persona tests on a Supabase branch. Per spec section 58/59 (authority/RLS rows). Section 61 reconciliations tracked. **Wave-1 retro (D39):** the physical layer MUST be conformance-tested against the P5 in-memory reference (@trovesnap/ingestion-contract) — not hand-rolled — covering import-run idempotency, durable-media/temp-URL rejection, expected target item-version on merge/status/media, status-update & attach-media promotion decisions, and physical actor authz (membership/credential-scope/RLS).

Cost triggers

No paid API / infra spend triggered by this item.

Status & editing

queued   Sprint: P&C Wave 2: Core Contracts

Edit status / sprint on the ★ Live Board → — changes are logged live with who / when / why.

Human-in-the-loop

No human tasks linked.

Findings

None recorded yet.

Concerns

None recorded yet.

Risks

None recorded yet.

Tech debt

None recorded yet.

Tools used

No tool calls recorded.

Files & artifacts

No files / artifacts recorded.

Update log

No log entries yet.