Technical spec · all specs
supabase/schema.sql · Migration surface: supabase/migrations/* · Taxonomy seed: supabase/seed_treasure_taxonomy.sql · Demo parity: src/lib/*DemoData.tsLoad-bearing database invariant: Supabase is the canonical system of record. External sources enter through P5. Canonical state changes occur only through authorized domain functions. Public and buyer-facing surfaces receive explicitly projected, buyer-safe data. RLS, grants, constraints, and transaction boundaries enforce these rules rather than relying on application convention.
Define and implement the durable relational model, authority boundaries, security policies, state-transition controls, provenance, and upgrade path for TroveSnap. P6 turns the product architecture into enforceable database behavior.
Capture → Candidate → Review → Canonical Item → Identity Binding → Publish
→ Buyer Interest → Floor Tracking → Reconciliation → Sold / Not Sold
→ Post-Sale Recovery → Terminal Disposition
It must support sellers using TroveSnap-native workflows; EstateSail-connected workflows (source: estatesail); Square/PROSALE/Shopify/HiBid/AuctionNinja or other POS/export tools; CSV/spreadsheet reconciliation; QR labels and reusable tag packs; no labels at all; manual checkout; buyer-facing marketplace features; and post-sale disposition/recovery — without giving external systems uncontrolled authority over canonical inventory.
TroveSnap owns canonical item identity, lifecycle, provenance, demand intelligence, and recovery orchestration — even when another platform handles payment, checkout, bidding, or marketplace publication.
TroveSnap is not initially a replacement for payment processing, receipts, refunds, taxes, cash drawers, disputes, or mature estate-sale POS. It is the item operating system around those tools. Its durable advantage is the connection between capture, source evidence, item identity, seller review, labels, public discovery, buyer interest, status reconciliation, transaction history, unsold recovery, and long-term pricing intelligence.
Postgres stores tenant/team authority; canonical sales/inventory; source ingestion & promotion; item identity & external aliases; status history; publication state; buyer-owned data; seller intelligence; disposition; provenance; audit events; privacy controls. Cloudflare Workers and application services may act as trusted gateways but do not become separate systems of record.
P5 defines the ingestion/promotion contract; P6 physically implements + enforces it through tables, constraints, foreign keys, roles, grants, RLS, functions, triggers, transaction boundaries, idempotency indexes. P5 defines what must be true; P6 makes it impossible — or at minimum explicitly unauthorized — for application paths to violate it.
When NEXT_PUBLIC_TROVESNAP_DEMO=1, the application uses deterministic fixtures from src/lib/*DemoData.ts and writes are no-ops. Demo mode must not require broadly permissive database policies. Production/dev databases must not become anonymously writable to support demos.
The long-term model is not thousands of owner_id = auth.uid() policies. Every seller-owned row carries tenant_id, and authorization resolves through stable membership + permission functions. The initial tenant may contain only its owner, but the policy model already supports owner, admin, cataloger, pricer, label runner, checkout, client-report viewer, delegated reviewer. Adding team roles must be additive — not a rewrite of every policy.
Relational columns for workflow-driving fields, status, publication, prices, foreign keys, identity, tenant boundaries, privacy, query filters, public projections, reporting. JSONB for immutable source payloads, normalized source payloads, provider metadata, versioned AI results, flexible event metadata, connector config, noncanonical evidence. Do not hide canonical operational state inside arbitrary JSONB.
Not a pure event-sourced system: current canonical projections for efficient reads + append-only events for history/audit/reconciliation/analytics. E.g. estate_sale_items.sale_status holds current state; inventory_status_events holds authoritative transition history. Current projections change through domain functions that also append the corresponding event.
A single status cannot safely represent draft/approved/published/active/pending/sold/picked-up/not-sold/donated/archived. The model separates review_status, publication_status, sale_status, fulfillment_status, disposition_status. The UI may derive one operational board stage; the database must not force unrelated state axes into one ambiguous enum.
Anonymous/buyer clients do not receive broad SELECT on seller tables. Public data is served through security-invoker views, narrow RPCs, Cloudflare gateway endpoints, or approved public read models. Public projections are explicit allowlists. Private columns are never selected, not removed after selection.
Public QR payloads contain opaque TroveSnap-controlled identifiers. They must not expose item UUIDs where avoidable, tenant IDs, internal pricing guidance, consignor info, seller notes, margin, source credentials, or exact private addresses. Public scans resolve through a safe resolver function/gateway. Staff binding and checkout require authentication + permissions.
One canonical item may have aliases from EstateSail, Square, Shopify, PROSALE, HiBid, AuctionNinja, seller CSVs, label packs, other integrations. For EstateSail the source namespace is source: estatesail. The canonical item remains TroveSnap-owned.
supabase/schema.sql remains idempotent; runnable against an empty database; the complete current-state schema; useful for local bootstrap, review, documentation.
Production upgrades require ordered migrations under supabase/migrations/. A change is not complete until it includes a forward migration; a schema.sql update; an upgrade test; an empty-database test; a generated-type update where applicable; a demo-fixture update where applicable. CREATE TABLE IF NOT EXISTS alone is not an adequate production migration strategy (it doesn't safely evolve columns/constraints/grants/RLS/indexes/functions/data).
Each migration: transactional where PostgreSQL permits; repeat-safe via the migration ledger; tenant-safe; compatible with active data or accompanied by a backfill; explicit about locks/large-table changes; tested against the prior released schema; reflected in schema docs.
Generated TypeScript types must be updated after schema changes. Application code must not hand-maintain duplicate definitions when generated types are available. Domain I/O contracts may wrap generated row types but should not silently diverge.
Demo fixtures must remain contract-compatible. CI verifies required fields; enum/code values; nullability; public-view shapes; status values; privacy values; candidate/promotion states. Fixtures need not include every column but must not teach the UI a shape production can't return.
The initial app may keep most RLS-protected tables in public for PostgREST compatibility. Table presence in public does not imply public access.
Sensitive server-only objects live in a non-exposed schema such as private: OAuth token material; encryption helpers; authorization helper implementation; internal replay payload references; internal service config; privileged transition implementations; security-definer helpers not intended as public RPCs.
Narrow RPC entry points may live in an exposed schema but must enforce auth/authz internally; use explicit parameter types; set a safe search_path; schema-qualify all references; avoid unsafe dynamic SQL; grant execution only to intended roles; return restricted result shapes.
For sensitive base tables: REVOKE ALL FROM anon; REVOKE ALL FROM authenticated; then grant only the minimum required operations or use RPC-only writes.
Eight authority domains (may initially coexist in one exposed schema, but ownership/access boundaries are explicit):
| Domain | Responsibility |
|---|---|
| Foundation and Authority | tenants, users, membership, roles, permissions, sale assignments |
| Canonical Seller Operations | sales, zones, items, photos, prices, publication, status |
| Ingestion and Provenance | connections, runs, records, candidates, review, promotion |
| Tracking Identity | QR codes, aliases, bindings, labels, scans, checkout identity |
| Marketplace and Buyer | public listings, taxonomy, saves, watches, profiles, alerts |
| Seller Intelligence | demand, wishlist matches, alerts, analytics, recommendations |
| Disposition and Recovery | offers, unsold routing, exports, donation/disposal outcomes |
| Audit and Read Models | events, safe views, aggregates, outbound attribution |
tenants: { id, name, slug, status, plan, settings, created_by, created_at, updated_at }
tenant_status: [active, suspended, closed]
All seller-operated data belongs to a tenant.
tenant_memberships: { id, tenant_id, user_id, role_id, status, invited_by, joined_at, suspended_at, created_at, updated_at }
membership_status: [invited, active, suspended, revoked]
A unique constraint prevents more than one active duplicate membership for the same tenant + user.
tenant_role: [owner, admin, cataloger, pricer, label_runner, checkout, client_report_viewer]
Role definitions should be data-driven or represented through stable permission mappings.
permission:
- tenant.settings.manage
- tenant.members.manage
- sale.create
- sale.manage
- sale.view_private
- item.create_candidate
- item.review_candidate
- item.promote_candidate
- item.view_private
- item.edit
- item.bulk_edit
- pricing.view_internal
- pricing.edit
- publication.manage
- labels.manage
- identity.bind
- identity.release
- checkout.update_status
- offers.manage
- disposition.manage
- ingestion.manage
- exports.manage
- reports.view
- reports.view_financial
Role-to-permission mappings must be versioned and testable.
sale_team_assignments: { id, tenant_id, estate_sale_id, membership_id, role_override, permissions_override, starts_at, ends_at, created_by }
Supports temporary crews; checkout-only staff; catalogers assigned to one house; client-report viewers assigned to one client sale.
Every seller-owned operational row carries tenant_id. Rows belonging to a sale generally also carry estate_sale_id (even when derivable) for faster RLS, simpler filtering, explicit audit, composite FK enforcement, and cross-tenant protection.
Parent tables expose unique pairs like (tenant_id, id); child tables use composite FKs (tenant_id, estate_sale_id) → estate_sales(tenant_id, id). This prevents a child row from carrying Tenant A while referencing a Tenant B sale.
Policies call a small, stable set of helpers rather than duplicating membership logic:
private.is_tenant_member(p_tenant_id uuid)
private.has_tenant_permission(p_tenant_id uuid, p_permission text)
private.has_sale_permission(p_sale_id uuid, p_permission text)
private.is_buyer_owner(p_buyer_id uuid)
private.can_review_candidate(p_candidate_id uuid)
private.can_access_private_item(p_item_id uuid)
Functions are stable where valid; carefully indexed; ownership controlled; search_path explicitly set; referenced tables schema-qualified; recursive RLS avoided; actor IDs un-spoofable; auth.uid() resolved server-side; tenant context never trusted solely from client input.
estate_sales: { id, tenant_id, owner_membership_id, name, description, sale_type, visibility, starts_at, ends_at, timezone, address_private, city, region, postal_code, latitude, longitude, address_reveal_policy, hero_asset_id, brand_settings, promotion_tier, offer_settings, publication_status, created_by, created_at, updated_by, updated_at, version, archived_at }
sale_type: [estate_sale, garage_sale, moving_sale, auction_preview, private_sale, other]
sale_visibility: [private, unlisted, public]
Independent from sale visibility:
address_reveal_policy: [hidden, city_only, approximate, date_gated, full]
A public sale may still hide the exact address until a configured date. Public views compute the allowed location shape; they never return the private address column unconditionally.
external_listing_urls: { id, tenant_id, estate_sale_id, channel, url, status, added_by, created_at, updated_at }
external_channel: [estatesales_net, facebook, hibid, auction_ninja, craigslist, custom]
Amplification references — they do not imply authenticated synchronization.
sale_zones: { id, tenant_id, estate_sale_id, parent_zone_id, zone_type, name, sort_order, pickup_instructions_private, public_label, created_by, created_at, updated_at }
zone_type: [room, table, shelving, garage, outdoor, storage, pickup_area, offsite, other]
Zones connect to capture, candidates, canonical items, label batches, checkout, pickup, client reports, room-level analytics, and imported POS/EstateSail data. Private pickup instructions must not appear in public listing views.
estate_sale_items: { id, tenant_id, estate_sale_id, zone_id, title, description, category_id, quantity, review_status, publication_status, sale_status, fulfillment_status, disposition_status, disposition_type, asking_price, current_price, currency, internal_price_guidance_low, internal_price_guidance_high, internal_pricing_notes, sold_price, sold_at, appraisal_status, recommended_disposition, recommended_disposition_reason, public_notes, private_notes, search_document, search_vector, created_from_candidate_id, created_by, created_at, updated_by, updated_at, version, archived_at }
Buyer views may expose asking price, current public price, approved discount, sold state where appropriate. They must not expose internal appraisal range, internal pricing guidance, margin, seller reserve, consignor split, pricing rationale, or private negotiation notes. Internal pricing fields require pricing.view_internal.
A source-derived item requires promoteInventoryCandidate(). A seller-native manual workflow may present a one-step save but internally still creates, reviews, and promotes a manual candidate.
Canonical mutable records include version: bigint. Update RPCs require the expected version; conflicting edits fail visibly rather than silently overwrite another reviewer's work.
review_status: [approved, needs_changes, suspended]
publication_status: [unpublished, scheduled, published, hidden, archived]
sale_status: [available, active_sale, pending, sold, not_sold, withdrawn]
fulfillment_status: [not_applicable, awaiting_pickup, picked_up, shipped, delivery_scheduled, completed, cancelled]
disposition_status: [none, review_required, recommended, selected, in_progress, completed, archived]
disposition_type: [auction, matched_offer, consignment, relist, clearance, donation, return_to_client, retain, haul_away, responsible_disposal, archive]
Most canonical items begin approved (review occurs before promotion). Responsible disposal is a recognized terminal route.
A view/deterministic function may derive operational_stage: [review, ready_to_publish, published, active_sale, pending, sold, pickup, not_sold, disposition_review, disposition_in_progress, complete] for workflow display. It must not replace the underlying dimensions.
inventory_status_events is append-only.
inventory_status_events: { id, tenant_id, estate_sale_id, item_id, event_type, state_dimension, from_value, to_value, effective_at, occurred_at, source_type, source_record_id, source_import_run_id, external_reference, actor_type, actor_id, correlation_id, causation_id, idempotency_key, metadata }
inventory_event_type:
- candidate_promoted
- seller_approved
- publication_scheduled
- published
- sale_activated
- pending
- checkout_scanned
- status_imported
- sold
- pickup_scheduled
- picked_up
- not_sold
- withdrawn
- disposition_review_started
- disposition_selected
- auction_exported
- offer_accepted
- consigned
- clearance_started
- donation_manifest_generated
- donated
- returned_to_client
- haul_away_completed
- responsible_disposal_completed
- archived
Not every event changes state — candidate_enriched, appraisal_suggested, qr_scanned, watch_requested may feed analytics without mutating canonical state.
append_inventory_status_event(...): (1) authenticate actor; (2) verify permission; (3) lock the item; (4) verify expected version; (5) verify allowed transition; (6) insert the event; (7) update the current projection; (8) record actor + source provenance; (9) increment item version; (10) commit atomically. Application roles must not directly update state columns outside this function.
Allowed transitions via versioned definitions / deterministic code, e.g. available → active_sale → pending → sold → awaiting_pickup → picked_up; active_sale → not_sold → disposition review. Imported status changes that don't map cleanly enter a reconciliation conflict rather than forcing an invalid transition.
estate_sale_item_photos: { id, tenant_id, estate_sale_id, item_id, storage_object_id, role, sort_order, is_primary, appraisal_recommended, better_photo_needed, photo_quality_score, detected_tags, width, height, mime_type, content_hash, visibility, source_asset_id, created_by, created_at }
photo_visibility: [private, review_only, published, archived]
A photo does not become public merely because it is attached to an item.
Current: estate-sale-photos, garage-sale-submission-photos. Authority: original seller assets private; candidate assets private; buyer-submission assets private until approved; public delivery uses signed URLs / gateway URLs / approved published derivatives; object paths include tenant scope: {tenant_id}/{sale_id}/{entity_type}/{entity_id}/{asset_id}.{ext}.
Policies validate tenant membership; sale access; candidate/item ownership; file role; publication state; upload permission. The client cannot claim ownership by embedding a different tenant ID in a path.
Temporary Drive/OneDrive/Google Photos/email URLs do not become canonical media URLs. Durable published media references TroveSnap-controlled storage.
seller_profiles separates public profile data from legal name, payout data, private contact, internal notes, tax info, consignor terms. Estate-sale companies reporting to clients/consignors use dedicated private tables: sale_clients, consignors, item_consignor_assignments, client_report_access — stricter permissions than general catalog access. A client_report_viewer receives a narrow report view without other clients, internal pricing guidance, team notes, buyer identities, or connector credentials.
discount_phases: { id, tenant_id, estate_sale_id, name, starts_at, ends_at, discount_type, discount_value, applies_to, status, created_by }
item_price_events: { id, tenant_id, estate_sale_id, item_id, event_type, previous_price, new_price, currency, discount_phase_id, source_type, actor_id, occurred_at, idempotency_key }
Price changes use a controlled function rather than direct arbitrary item updates. The database preserves the difference between public asking price, currently advertised price, internal guidance, observed external tag price, appraisal range, offer amount, final sold amount, auction hammer, buyer premium, shipping-inclusive total, and settlement amount. Do not collapse these into one generic price column.
estate_sale_ai_runs stores item/candidate target; operation type; model/provider; contract version; prompt/schema hashes; token usage; estimated cost; raw-result reference; validated-result reference; status; actor; timestamps. Vision results remain provisional until promoted through P5. (See §61 — P6 must also model the full P3 attempt/validation/receipt + recovery-chain storage.)
appraisals: { id, owner_type, buyer_id, tenant_id, estate_sale_id, item_id, found_context, observed_price, observed_price_type, identification, value_range_low, value_range_high, currency, confidence, value_signal, share_status, share_token_hash, location_context, privacy_settings, created_at, updated_at }
location_context stores an authorized location source, not a copied address:
location_context:
type: linked_sale # linked_sale | public_venue | unlinked | at_home
estate_sale_id: sale_221
display_policy: inherit_from_sale
appraisal_share_status: [private, link, public, seller_inventory]
Private is the default.
An appraisal does not independently decide location privacy — it inherits an authorized location source. Public appraisals may display the location of an explicitly linked public sale or public venue, according to that source's current visibility + address_reveal_policy. They must not expose a private residential address, precise device location, photo geolocation, or unpublished sale location merely because the appraisal is shared publicly. By context:
address_reveal_policy (if full address is already public, the appraisal may show it; if city-only until sale morning, the appraisal follows that and updates automatically when the sale's policy changes).Storing location_context (rather than copying an address) means a sale moving from city-only → full, or a seller hiding the address, is automatically reflected in the appraisal. Other privacy settings: include_photo, include_range, link_sale, city_only, hide_profile, hide_location. A seller linked to a sale receives only approved aggregates from buyer appraisals (count; category distribution; value-signal distribution; demand trends) — never buyer identity or private appraisal details.
P6 physically implements P5. Required tables:
source_connections
credential_refs
source_import_runs
source_import_records
source_import_record_revisions
source_assets
inventory_candidates
candidate_reviews
candidate_conflicts
candidate_duplicate_suggestions
candidate_promotions
item_source_provenance
item_field_provenance
plugin_events
Source records retain raw_payload / raw_payload_ref (immutable), normalized_payload, normalizer_id, normalizer_version, source_identity_key, source_revision_key, content_hash.
promoteInventoryCandidate() is a database transaction or narrowly controlled server transaction backed by privileged RPCs. Only the promotion service may create source-derived canonical items.
Connector, harness, MCP, browser, and buyer roles receive no direct canonical inventory write grants. P6 must test this.
Supabase service_role bypasses RLS; therefore it is server-only; never shipped to web/mobile/MCP/desktop clients; integrations call narrow server endpoints/RPCs; possession is not architectural permission to write arbitrary tables; code scanning + module boundaries prohibit direct canonical writes; a narrower custom database role is used where practical.
First-class, not a later optional column:
qr_codes
external_item_aliases
item_identity_bindings
item_identity_binding_events
qr_scan_events
label_batches
label_batch_items
checkout_scan_events
qr_codes: { id, tenant_id, public_code, code_hash, code_type, state, label_pack_id, printed_at, created_by, created_at, retired_at }
qr_code_state: [unassigned, active, released, retired, lost]
Public codes are random; nonsequential; difficult to enumerate; independent from item UUID; resolvable through a controlled endpoint. The resolver may use the code or a hash; logs avoid retaining the complete reusable public token unnecessarily.
item_identity_bindings: { id, tenant_id, qr_code_id, item_id, estate_sale_id, binding_type, state, bound_at, bound_by, released_at, released_by, version }
binding_type: [trovesnap_qr, reusable_tag, preprinted_pack, external_barcode]
binding_state: [active, released, superseded]
A partial unique index ensures one active canonical binding per code where required. A code may start unbound → bind → release → rebind; every transition writes item_identity_binding_events.
external_item_aliases: { id, tenant_id, item_id, estate_sale_id, source, external_item_id, external_sale_id, external_label_code, status, first_seen_at, last_seen_at, provenance_id }
Unique identity: tenant_id + source + external_item_id. For EstateSail: source: estatesail. An EstateSail QR/barcode scanned inside TroveSnap resolves through this alias layer to the canonical item; the external alias does not replace canonical identity.
label_batches: { id, tenant_id, estate_sale_id, name, label_format, stock_type, status, zone_id, created_by, created_at, printed_at }
label_batch_items: { id, tenant_id, label_batch_id, item_id, qr_code_id, sequence_number, print_status, bind_status, reprint_count, skipped_reason }
Supports print-your-own; preprinted packs; skipped labels; reprints; room grouping; unbound codes; unlabelled high-value-item reports.
qr_scan_events: { id, tenant_id, qr_code_id, item_id, estate_sale_id, scan_type, anonymous_session_id, authenticated_buyer_id, occurred_at, coarse_location, referrer, user_agent_class, privacy_safe_metadata }
May feed anonymous interest, demand signals, attribution, item view counts. Must not automatically reveal a buyer identity to the seller; the seller receives aggregates unless the buyer performs an identity-bearing action (e.g. submitting an offer).
checkout_scan_events: { id, tenant_id, estate_sale_id, item_id, qr_code_id, action, external_transaction_ref, source, actor_id, occurred_at, idempotency_key, metadata }
checkout_action: [scanned, marked_pending, marked_sold, pickup_confirmed, reconciliation_required]
Checkout scanning is an item-status workflow; it does not imply TroveSnap processed payment. Square/EstateSail/PROSALE/Shopify/manual checkout may remain the payment authority.
treasure_taxonomy
item_treasure_tags
sale_treasure_tags
buyer_profiles
saved_searches
watched_sales
item_saves
wanted_items
match_alerts
seller_followers
notifications
notification_settings
token_ledger
garage_sale_submissions
garage_sale_submission_photos
discount_codes
discount_redemptions
appraisals
Buyer-owned records use buyer_id; policies enforce buyer_id = current buyer profile + explicit sharing. Buyer A cannot access Buyer B's saves, wanted items, notifications, private appraisals, submission drafts, or location preferences. Seller membership does not grant access to private buyer records.
Not canonical seller inventory: submission → source record → candidate → review → promotion.
garage_sale_submission_status: [draft, submitted, verification_pending, confirmed, rejected, expired]
Tokens may be awarded only after confirmation per the anti-cheat contract. Photos remain private until approved for public display.
Buyer-safe views/RPCs equivalent to: marketplace_sales_public, marketplace_items_public, marketplace_item_detail_public, marketplace_sale_map_public, public_seller_profiles, public_appraisal_shares, public_qr_item_resolution. May include public title; approved description; public price; public photos; public tags; sale times; allowed location; discount phase; availability; public seller branding; approved external links. Must exclude tenant ID where unnecessary; owner IDs; private address; internal price guidance; appraisal internals; source payloads; private notes; consignor identity; buyer identity; offer internals; promotion config secrets; unpublished media; credential references; raw AI outputs.
Use security_invoker views where supported; narrow security-definer functions with explicit allowlists; restricted grants; dedicated public read models. Do not rely on a default view that accidentally executes with a privileged owner and bypasses RLS.
Initial: live relational views; indexed canonical tables; narrow RPCs; Cloudflare caching — keeps publication/privacy changes immediately correct. Scale upgrade: add materialized/denormalized marketplace read models only when measurements require. Materialized views are not authoritative; contain only public-safe columns; refreshed from canonical state + events; have an explicit refresh strategy; preserve tenant + publication filtering; are rebuilt when privacy rules change. Performance optimization must not weaken privacy.
seller_alerts
item_demand_signals
wishlist_matches
appraisal_recommendations
inventory_status_events
outbound_click_events
seller_analytics_snapshots
(See §61 — P6 must also model the P4 frozen versioned demand_snapshots, ranking_receipts, and versioned actor/watchlist profiles.)
item_demand_signals: { id, tenant_id, estate_sale_id, item_id, signal_type, source, anonymous_session_id, buyer_id, value, occurred_at, privacy_class, deduplication_key }
May derive from item views; saves; repeated views; QR scans; watched sales; wanted-item matches; offer activity; directions requests; external outbound clicks; buyer appraisal activity; category/regional interest. Raw signals and aggregated scores remain distinct.
Sellers receive counts; trends; matched-category summaries; item-level aggregate demand; alerts. Not buyer identity; buyer's private saved-search text; buyer's private appraisal; precise buyer location; unrelated buyer behavior.
wishlist_matches links an item to a permitted buyer demand profile. The seller-facing view exposes match count; confidence; category; whether notifications may be sent — not buyer identity unless the buyer has explicitly entered an identity-bearing transaction or consented workflow.
outbound_click_events: { id, tenant_id, estate_sale_id, item_id, external_listing_url_id, channel, anonymous_session_id, authenticated_buyer_id, campaign, referrer, occurred_at, privacy_safe_metadata }
/api/out: (1) validates an approved destination record; (2) records privacy-safe attribution; (3) prevents arbitrary open redirects; (4) redirects to the stored destination. Raw arbitrary client URLs must not be accepted.
buyer_offers: { id, tenant_id, estate_sale_id, item_id, buyer_id, amount, currency, message, status, created_at, responded_at, responded_by }
offer_status: [submitted, viewed, accepted, declined, withdrawn, expired]
Public users cannot read offers; buyers can read their own; authorized sellers can read offers for their tenant + sale. Accepting an offer does not mark payment complete; it may transition an item to pending through the controlled status function.
disposition_recommendations
disposition_actions
disposition_events
auction_exports
donation_manifests
donation_manifest_items
clearance_batches
consignment_referrals
disposition_recommendations: { id, tenant_id, estate_sale_id, item_id, recommended_type, reason_codes, explanation, confidence, demand_snapshot_id, pricing_snapshot_id, status, generated_at, reviewed_by, reviewed_at }
Advisory — they do not directly change canonical disposition. May consider item demand; QR scans; saves; wishlist matches; offer count; category; estimated value; sale timing; condition; shipping practicality; previous channel performance. The stored recommendation records the evidence snapshot used.
disposition_action: [notify_matched_buyers, request_offers, auction_export, consignment, clearance, donation, return_to_client, retain, haul_away, responsible_disposal, archive]
A seller selects an action through a controlled function.
An unsold item is not operationally complete merely because the sale ended. Completion requires sold + fulfilled; or a completed disposition route; or intentional archive/retention. Responsible disposal is an explicit terminal route.
export_status: [draft, approved, generated, delivered, acknowledged, failed, cancelled]
Nothing is published externally merely because an export package was generated. External auto-posting requires official API/partner permission; seller approval; channel-specific implementation; tracked result. Do not claim channel synchronization until actual inbound/outbound state reconciliation exists.
plugin_events remains the append-only integration audit required by P5.
audit_events: { id, tenant_id, actor_type, actor_id, action, entity_type, entity_id, correlation_id, causation_id, before_ref, after_ref, metadata, occurred_at }
Sensitive before/after values are referenced or redacted, not indiscriminately duplicated.
Application roles receive insert where appropriate; select where authorized; no update; no delete. Corrections are subsequent events.
treasure_taxonomy seeded via supabase/seed_treasure_taxonomy.sql; tags via item_treasure_tags / sale_treasure_tags with stable IDs/codes + versions. Full-text search uses structured fields + normalized tags + a maintained tsvector + GIN indexes. pgvector is an additive later path (visual/semantic similarity; duplicate suggestions; comparable retrieval; wanted-item matching) — it does not replace canonical taxonomy/relational filters. Geospatial upgrade path: PostGIS geography(Point, 4326) for distance sort / radius search / regional demand / map clustering; public location representation still respects address-reveal policy.
Index every FK used in joins; tenant_id; (tenant_id, estate_sale_id); item status dimensions; publication/visibility; source identity keys; import-run status; candidate review/promotion status; active QR bindings; external aliases; event item+time; buyer-owned owner IDs; public sale date ranges; taxonomy join keys; saved-search matching fields; disposition status; idempotency keys. B-tree for equality/ordering; GIN for search vectors + selected JSONB; partial indexes for active states; unique partial indexes for one-active-binding + idempotency invariants. RLS helper queries must have supporting indexes.
39.1 Default posture — every application table: RLS enabled, deny by default. A table without a policy is intentionally inaccessible to normal clients.
39.2 Seller-owned rows — policies use tenant membership + required permission + optional sale assignment, not only row owner_id.
39.3 Buyer-owned rows — buyer_id = current authenticated buyer + explicit sharing.
39.4 Public rows — anonymous access limited to safe views / resolver RPCs / public share RPCs / explicit read models. No direct general access to seller base tables.
39.5 Insert checks — every insert policy has a WITH CHECK; seeing Tenant A data must not allow inserting a row claiming Tenant B.
39.6 Update checks — validate existing + proposed row; a user cannot change tenant_id/buyer_id/sale_id/another authority field to escape scope.
39.7 Delete behavior — canonical operational rows use archive/revoke/supersede/release/soft-delete; hard deletion restricted to admin retention; event/provenance rows are not hard-deleted through normal paths.
Additive membership model (not "permissive now, owner policies later"):
| Role | Typical access |
|---|---|
| Owner | Full tenant control |
| Admin | Manage sales, inventory, team ops; limited ownership actions |
| Cataloger | Capture, candidates, item copy, photos, rooms |
| Pricer | Internal guidance, prices, discounts, appraisal review |
| Label runner | Label batches, binding, room context |
| Checkout | Scan identity, pending/sold/pickup transitions |
| Client report viewer | Narrow assigned-sale reports only |
A person may hold a tenant role + sale-specific assignments + explicit overrides. Policies resolve effective permission rather than hard-coding role names everywhere.
promote_inventory_candidate append_inventory_status_event update_item_price
publish_item unpublish_item bind_item_identity
release_item_identity resolve_public_qr record_qr_scan
record_checkout_scan approve_status_import apply_disposition_action
create_public_appraisal_link record_outbound_click
Each: (1) derives the actor from auth; (2) verifies tenant/buyer scope; (3) verifies permission; (4) validates expected versions; (5) validates domain transition; (6) performs one atomic operation; (7) writes provenance + events; (8) returns a restricted result.
Tightly controlled owner; safe empty/explicit search_path; schema-qualify every relation; avoid caller-controlled object names; validate tenant scope internally; never trust caller-supplied actor IDs; grant execution narrowly; covered by cross-tenant tests; return no secret columns; reviewed as privileged code. Security-definer functions are exceptions, not a shortcut around RLS design.
credential_refs contains pointers only. OAuth secret material belongs in a server secret store, or a private non-exposed structure with encryption + service-only access. Normal/anonymous clients cannot select token material. Desktop harness secrets remain local. The database may store device ID; token hash; credential reference; revocation state; last-used time — not a reusable plaintext harness token.
privacy_class: [public, buyer_private, seller_private, tenant_internal, financial_sensitive, credential_secret, audit_restricted]
Informs views; API responses; export behavior; logging; retention; RLS tests. High-risk private data: exact private address; seller personal contact; consignor identity; buyer identity; private appraisal; internal price guidance; margins/fee splits; offer messages; OAuth tokens; local paths; raw emails; source credentials.
Buyer/public surfaces must never expose private notes; internal pricing guidance; appraisal recommendation internals; seller cost/margin; consignor details; private address before reveal; tenant membership; source raw payloads; provider prompts; AI raw output; unpublished photos; buyer private activity; OAuth tokens; credential references; internal disposition rationale unless approved; client-report financials. Public-view tests check both named columns and serialized JSON — a public JSON payload must not contain a private nested property simply because the top-level SQL view appeared safe.
Privileged credentials never enter browser bundles; routes authenticate + authorize before DB use; narrow RPCs preferred; tenant IDs derived/verified; source payloads untrusted; service calls emit correlation IDs; privileged direct-table code isolated + reviewed; public gateways return projected DTOs, not arbitrary rows. The service role is operational infrastructure, not a substitute for the domain model.
Analytics may use live aggregates; summary tables; materialized views; PostHog; Cloudflare Web Analytics; Supabase aggregates. Read models record source event range; generation time; version; tenant scope; privacy policy version. Projections are not canonical state; rebuilding an aggregate must not change source events or inventory.
May include captured/sold/unsold/donated/removed items; gross; fees; net; room; consignor; audit links to status events. Generated from canonical item state; inventory events; pricing events; transaction imports; disposition events. A report viewer receives a dedicated projection/export, not direct unrestricted table access.
Operational records use archive/retention workflows. Retention distinguishes public listings; canonical transaction history; event history; buyer private data; source raw payloads; OAuth credentials; media; analytics aggregates; legal/financial records. Revoking a connection stops future access; revokes credentials; preserves required import provenance; applies configured raw-payload retention; does not erase canonical items already reviewed + promoted. Buyer account deletion removes/anonymizes buyer-owned private data as required without corrupting seller aggregate history.
Atomic: candidate promotion; item state transition; identity bind/release; price change; publication transition; disposition selection; offer acceptance transition; status-import approval; durable media attachment where canonical references are created. Use row locks; expected version checks; idempotency keys; unique constraints; transaction-scoped event writes. Partial canonical mutations are not acceptable.
(1) Every seller-owned row has a tenant. (2) Sale-owned rows reference a sale in the same tenant. (3) Buyer-owned rows reference the authenticated buyer. (4) A source-derived canonical item references its promotion provenance. (5) An active QR code is not actively bound to conflicting items. (6) An external alias maps uniquely within tenant + source. (7) A public item belongs to a publishable public/unlisted sale. (8) Public photos are explicitly published. (9) Sold timestamps + amounts preserve source semantics. (10) A disposition-completed item has a valid disposition type. (11) A picked-up item has a compatible sold/transferred state. (12) Event idempotency keys are unique within scope. (13) Candidate promotion is idempotent. (14) Source import identity is idempotent. (15) Append-only events cannot be updated by application roles. (16) Cross-tenant foreign keys are rejected. (17) A share token is stored hashed where practical. (18) Temporary source media cannot be a canonical public asset. (19) Seller approval is required before source promotion. (20) An imported status cannot bypass candidate review.
Core tests run without paid APIs.
53.1 Migration — empty-DB migration; prior-version upgrade; full schema.sql bootstrap; seed execution; generated types; rollback where applicable; no duplicate objects after bootstrap.
53.2 Tenant isolation — Tenant A owner/cataloger/pricer/checkout/report-viewer + Tenant B owner: A cannot read/write B rows; cross-tenant FKs fail; role permissions differ; sale assignments honored.
53.3 Buyer isolation — Buyer A vs B: saves/wanted/appraisals/notifications private; seller cannot read buyer-private; public share follows share_status.
53.4 Public views — expose only approved fields; explicitly test absence of private notes; internal guidance; hidden addresses; seller PII; consignor data; buyer data; raw payloads; credentials; unpublished media.
53.5 P5 enforcement — direct canonical writes from connector/MCP/harness/buyer/import roles all fail; promotion through the authorized function succeeds with provenance.
53.6 Status transitions — valid; invalid; stale version; duplicate idempotency key; imported status review; sold/pickup; not-sold/disposition; responsible-disposal completion.
53.7 Identity bindings — unbound; first bind; public resolution; release; rebind; conflicting simultaneous bind; EstateSail alias resolution; opaque public payload; staff-only binding detail.
53.8 Storage policies — tenant uploads; cross-tenant denial; private candidate photo; published item photo; buyer submission photo; temporary source asset; signed/public delivery; deleted/revoked access.
53.9 Role matrix — each role against candidates; items; internal pricing; labels; checkout; reports; disposition; tenant settings.
53.10 Appraisal privacy — private; link share; public city-only; hidden profile; seller-inventory; seller aggregate access; no buyer identity leakage; location_context inherits the sale's current reveal policy.
53.11 Event immutability — application roles cannot update/delete/rewrite inventory_status_events, plugin_events, binding events, price events.
53.12 Service RPC — for every privileged function: authorized actor; unauthorized; wrong tenant; stale version; invalid transition; idempotent replay; event emission; restricted return fields.
Apply the complete migration set to a Supabase branch; run schema migration; RLS persona; storage policy; function permission; public-view leakage; P5 promotion; QR binding; item lifecycle; buyer privacy; disposition suites. The branch test must use real Supabase Auth JWTs for multiple users/roles, not only direct SQL role simulation.
Measure at representative volumes (sales/tenant; items/sale; photos/item; source records/run; candidate queue; events/item; QR scans; buyer saves; demand signals; public map queries). Verify RLS helper functions use indexes; tenant filters appear early in plans; public views avoid full scans; event indexes support histories; source-dedup indexes remain selective; map queries have an upgrade path; search uses GIN; policy functions don't cause pathological repeated scans.
Track RLS denials; cross-tenant access attempts; RPC failures; stale-version conflicts; promotion failures; invalid transitions; QR binding conflicts; source idempotency conflicts; public resolver failures; materialized-read freshness; slow RLS/public queries; event insertion failures; storage-policy denials; role usage; service-role endpoint usage. Sensitive row contents must not be copied into general logs.
57.1 Tracking identity/disposition share the canonical tenant boundary? Yes — seller-owned operational domains carry tenant_id. Anonymous QR scans resolve to the owning tenant + item internally while public responses stay buyer-safe.
57.2 Owner policies now, team later? Use membership + permission helpers now even when the only membership is the owner — team roles become additive. No scattered owner_id = auth.uid().
57.3 Permissive MVP policies? Demo behavior belongs in fixture mode, not permissive DB security. Live DBs use deny-by-default RLS; temporary dev policies are isolated, clearly named, never in production.
57.4 Materialized or live marketplace views? Live safe views + gateway caching first; add materialized/event-fed read models when measured load requires. Read models never become canonical.
57.5 One lifecycle column? No — publication/sale/fulfillment/disposition are separate dimensions with one combined event history + a derived stage for UI.
57.6 Can a connector write status events directly? Not before source record creation, candidate matching, seller review, and authorized transition.
57.7 Does QR identity require a label? No — labels are optional; the identity layer supports labelled/unlabelled items, external aliases, preprinted packs, reusable tags.
57.8 Does an external alias replace TroveSnap identity? No — external identifiers map to one canonical TroveSnap item.
57.9 Is EstateSail called sailpoint? No — the source namespace is estatesail.
57.10 Can public QR resolution expose inventory rows? No — it returns a buyer-safe public item projection.
57.11 Can sellers see buyer appraisal details? No — permitted aggregates only unless the buyer explicitly shares or transacts.
57.12 Is schema.sql alone the migration system? No — it's the complete bootstrap/current-state reference; ordered migrations are authoritative for upgrades.
(1) Updated schema.sql. (2) Ordered migrations. (3) Updated taxonomy seed. (4) Updated generated types. (5) Demo-data contract updates. (6) Tenant table. (7) Membership table. (8) Role/permission model. (9) Sale assignment model. (10) Authorization helpers. (11) Estate-sale canonical model. (12) Sale-zone model. (13) Orthogonal item-state columns. (14) Inventory status events. (15) Price history. (16) Media/storage metadata. (17) P5 ingestion tables. (18) Promotion function + grants. (19) Item + field provenance. (20) QR-code table. (21) External alias table. (22) Identity-binding table. (23) Binding history. (24) Label-batch tables. (25) QR + checkout scan events. (26) Marketplace buyer tables. (27) Appraisal privacy model. (28) Seller-intelligence tables. (29) Outbound attribution. (30) Offer model. (31) Disposition recommendation + action model. (32) Donation/auction/clearance support. (33) Append-only audit model. (34) Public-safe views + RPCs. (35) Public QR resolver. (36) Storage bucket policies. (37) RLS policies for every application table. (38) Grants + revocations. (39) Role-matrix docs. (40) State-transition docs. (41) Data-classification docs. (42) Empty-DB migration tests. (43) Upgrade migration tests. (44) RLS persona tests. (45) Storage-policy tests. (46) Public-data leakage tests. (47) Domain-function tests. (48) P5 one-door enforcement tests. (49) Supabase branch test. (50) Query-plan + index review.
P6 is complete when: (1) schema.sql represents the complete current DB; (2) ordered migrations build + upgrade it; (3) demo mode requires no permissive live policies; (4) every seller-owned row carries tenant_id; (5) sale-owned rows can't reference another tenant's sale; (6) tenant authz uses membership + permission helpers; (7) owner-only MVP works through the membership model; (8) team roles add without rewriting every policy; (9) initial roles include owner/admin/cataloger/pricer/label runner/checkout/client-report viewer; (10) sale-scoped assignments supported; (11) RLS enabled on every application table; (12) live RLS deny-by-default; (13) every insert/update policy checks proposed tenant ownership; (14) anonymous can't directly read seller base tables; (15) public access uses safe views/RPCs; (16) buyer-owned data scoped by buyer identity; (17) seller membership doesn't expose private buyer records; (18) public marketplace views exclude all private fields; (19) address-reveal policy enforced in projection/gateway; (20) internal pricing guidance never in buyer views; (21) seller/consignor PII never in buyer views; (22) private appraisal data never in seller/public views; (23) sellers receive only permitted buyer-demand aggregates; (24) source-derived creation only through promotion; (25) connector/MCP/harness/buyer/import roles can't directly write canonical inventory; (26) promotion atomic + idempotent; (27) every promoted item retains candidate + source provenance; (28) canonical item state relational + queryable; (29) publication/sale/fulfillment/disposition separate; (30) current state changes through controlled functions; (31) every transition appends an immutable event; (32) invalid transitions fail; (33) imported state changes require record+candidate+review+authorized transition; (34) price changes auditable; (35) sold-price semantics distinct from asking/bid/settlement; (36) rooms/zones/pickup first-class; (37) items may associate with zones; (38) tracking identity is a first-class domain; (39) QR codes use opaque public identifiers; (40) public QR resolution returns only buyer-safe projections; (41) QR codes can be unbound/bound/released/rebound; (42) binding history immutable; (43) active-binding uniqueness enforced; (44) external aliases map to canonical items; (45) EstateSail aliases use source: estatesail; (46) label batches support print/skip/reprint/assign/bind status; (47) checkout scanning records item-status activity without claiming payment; (48) storage policies enforce tenant ownership; (49) candidate/original photos private until explicitly published; (50) temporary source-media URLs can't become canonical public assets; (51) Drive/OneDrive/Photos media copied into durable storage before canonical public use; (52) buyer appraisals support private/link/public/seller-inventory; (53) private is the appraisal default; (54) public appraisals can't reveal a private address (location inherits the linked source's policy via location_context); (55) garage-sale submissions untrusted until reviewed; (56) token rewards require confirmed submissions; (57) demand signals preserve privacy classification; (58) external click attribution uses approved stored destinations; (59) /api/out can't be an unrestricted open redirect; (60) offers private to involved buyer + authorized seller roles; (61) accepting an offer doesn't imply completed payment; (62) unsold items enter disposition review; (63) responsible disposal exists as a terminal route; (64) disposition recommendations advisory + explainable; (65) client reports derive from canonical events + state; (66) client-report viewers get narrow assigned-sale projections; (67) event/provenance tables append-only for normal roles; (68) OAuth tokens not readable through application roles; (69) credential_refs contain no secrets; (70) service-role credentials never enter browser/desktop clients; (71) security-definer functions use safe search paths + explicit authz; (72) every sensitive domain function has cross-tenant tests; (73) all FKs + RLS helper paths have supporting indexes; (74) public-view tests verify nested JSON as well as named columns; (75) the full RLS suite passes using real Supabase Auth users; (76) a Supabase branch can apply migrations + run the complete suite; (77) the model supports TroveSnap-native, EstateSail-connected, and bring-your-own-POS modes without separate canonical inventories.
P6 is done when the Supabase database can act as the enforceable canonical item operating system for TroveSnap. For every important row/state change, the system can answer: which tenant owns it? which authenticated actor may see it? which permission allowed the action? which sale + item? did it originate from a candidate or external source? which seller approved it? which current state dimension changed? which immutable event records the transition? which QR code or external alias resolves to it? which fields are public/buyer-private/seller-internal? which source/item version changed? can the operation be replayed safely? can another tenant/buyer/connector/anonymous user access it? can the database prove public views contain no private data? The P6 result is the multi-tenant data authority, identity, privacy, lifecycle, and audit foundation for capture; ingestion; seller review; canonical inventory; QR/label tracking; marketplace publication; buyer demand; POS reconciliation; offers; client reporting; unsold recovery; responsible disposition; and long-term TroveSnap intelligence.
P6 is where the P2–P5 storage seams land. Adopting P6 closes P5 §37 #1 (physical ingestion tables + promotion RPC + direct-write prevention). Remaining seams to resolve before freeze:
estate_sale_ai_runs (§17.1) is lighter than P3 §21's append-only audit chain. P6 must model scan_requests, provider_attempts, canonical_drafts, validation_receipts, and the recovery chain (or expand ai_runs to cover them) — append-only, tenant-scoped, replayable — so P3's trust pipeline has durable storage.item_demand_signals + seller_analytics_snapshots, but not the frozen, versioned, hashed demand_snapshots, the ranking_receipts, or the versioned actor/watchlist profiles P4 §24/§30 consume. Add them to the seller-intelligence domain (immutable receipts; profiles version-referenced).tenant_provider_policies table).The largest gate — sliced 4 ways, dependency-ordered (the first three, with P1+P5, are what unblock Spine per D30; all gate P6, owner Paul):
WITH CHECK, upgrade-path stages), schema.sql + ordered-migration contract, generated types, demo-fixture parity. The base every other slice sits on.inventory_status_events + the transition function, item photos/media + storage RLS, pricing/discount history. Depends on WI-015.promoteInventoryCandidate RPC, direct-write prevention, and the P3 scan/attempt/validation storage (§61 #1). Depends on WI-015 + WI-014 (P5).