Supabase Postgres schema for the 1 OAK MLS Platform - all tables are multi-tenant with workspace_id

Database Schema (Supabase Postgres)

All tables are multi-tenant with workspace_id as the partition key.

Entity Relationship Overview

workspaces
    ├── workspace_domains (1:many)
    ├── mls_connections (1:many, typically 1 per provider)
    │       └── import_tasks (1:many)
    ├── field_mappings (1:many, versioned)
    ├── listings (1:many)
    │       ├── listing_media (1:many, MLS synced)
    │       ├── listing_photos (1:many, agent uploads)
    │       └── ai_content_audit (1:many)
    ├── user_profiles (1:many)
    │       ├── favorites (1:many)
    │       └── saved_searches (1:many)
    ├── compliance_profiles (1:1)
    ├── ai_usage (1:many)
    ├── sync_runs (1:many)
    ├── stripe_customers (1:1)
    ├── subscriptions (1:1)
    └── payment_events (1:many)

Tables

1. `workspaces`

Root tenant entity. Every other table references this.

Column	Type	Notes
`id`	uuid	PK, auto-generated
`slug`	text	Unique, URL-safe identifier (e.g., `lainey-levin`)
`name`	text	Display name
`status`	text	`active` \| `paused` \| `archived`
`created_at`	timestamptz
`updated_at`	timestamptz

2. `workspace_domains`

Custom domains for each workspace's client site.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`domain`	text	e.g., `laineylevin.com`
`is_primary`	boolean	Default false
`created_at`	timestamptz

Unique: (workspace_id, domain)

3. `mls_connections`

Bridge/RESO API connection configuration per workspace.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`provider`	text	`bridge` \| future providers
`dataset_id`	text	Bridge dataset identifier (OData path segment)
`token_ciphertext`	text	Encrypted — never log or return in API
`token_type`	text	`server` \| `oauth`
`base_url`	text	Default: `https://api.bridgedataoutput.com/api/v2/OData`
`status`	text	`pending` \| `active` \| `error` \| `paused`
`sync_cadence`	text	`manual` \| `hourly` \| `daily`
`last_synced_at`	timestamptz
`last_sync_cursor`	text	Optional checkpoint/cursor
`created_at`	timestamptz
`updated_at`	timestamptz

Unique: (workspace_id, provider)

4. `field_mappings`

Versioned RESO → canonical field mapping rules.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`provider`	text	`bridge`
`version`	int	Increment on changes
`is_active`	boolean	Only one active per workspace/provider
`mapping`	jsonb	See mapping format below
`created_at`	timestamptz
`updated_at`	timestamptz

Unique: (workspace_id, provider, version)

Mapping JSON format:

{
  "source_listing_key": "ListingKey",
  "mls_number": "ListingId",
  "standard_status": "StandardStatus",
  "modification_ts": "ModificationTimestamp",
  "property_type": "PropertyType",
  "list_price": "ListPrice",
  "bedrooms": "BedroomsTotal",
  "bathrooms": "BathroomsTotalInteger",
  "living_area_sqft": "LivingArea",
  "year_built": "YearBuilt",
  "address_full": "UnparsedAddress",
  "city": "City",
  "state": "StateOrProvince",
  "postal_code": "PostalCode",
  "subdivision": "SubdivisionName",
  "building_name": "BuildingName",
  "latitude": "Latitude",
  "longitude": "Longitude",
  "public_remarks": "PublicRemarks"
}

5. `listings`

Canonical listing data. Source of truth after normalization.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
Source identifiers
`source_provider`	text	`bridge`
`source_dataset_id`	text	Dataset this came from
`source_listing_key`	text	ListingKey from source
`mls_number`	text	Public MLS number
Status & timestamps
`standard_status`	text	`Active` \| `Pending` \| `Closed` \| etc.
`modification_ts`	timestamptz	From source
`list_date`	date
`close_date`	date
Address
`address_full`	text	Full formatted address
`street_number`	text
`street_name`	text
`unit_number`	text
`city`	text
`state`	text
`postal_code`	text
Location taxonomy
`subdivision`	text	Neighborhood
`building_name`	text	Condo/building
`community`	text	Optional
`county`	text
Geo
`latitude`	double precision
`longitude`	double precision
Property facts
`property_type`	text	Condominium, Single Family, etc.
`bedrooms`	int
`bathrooms`	double precision
`living_area_sqft`	int
`lot_size_sqft`	int
`year_built`	int
Pricing
`list_price`	int
`close_price`	int
`price_per_sqft`	int	Computed or from source
Content
`public_remarks`	text	Original MLS remarks
`marketing_description`	text	AI-enhanced or custom marketing description
`marketing_description_source`	text	`ai` \| `manual`
`marketing_description_approved_by`	uuid	Reviewer who approved
`marketing_description_approved_at`	timestamptz	Approval timestamp
`photo_count`	int
Flags
`is_active`	boolean	For soft-delete
`import_task_id`	uuid	FK → import_tasks (which task imported this)
Raw data
`raw`	jsonb	Original source payload
`created_at`	timestamptz
`updated_at`	timestamptz

Unique: (workspace_id, source_provider, source_listing_key)

Indexes:

(workspace_id, standard_status)
(workspace_id, city)
(workspace_id, subdivision)
(workspace_id, building_name)
(workspace_id, modification_ts DESC)

6. `listing_media`

Photos and media from MLS sync.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`listing_id`	uuid	FK → listings
`url`	text	Photo URL (hotlinked from MLS)
`sort_order`	int	Display order
`caption`	text	Optional
`created_at`	timestamptz

Unique: (listing_id, url)

6a. `listing_photos`

Agent-uploaded photos (takes priority over listing_media).

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`listing_id`	uuid	FK → listings
`storage_path`	text	Supabase Storage path
`url`	text	Public URL
`thumbnail_url`	text	Resized version (optional)
`source`	text	`upload` or `import`
`sort_order`	int	Display order
`is_primary`	boolean	Primary photo for listing
`caption`	text	Optional caption
`alt_text`	text	Accessibility text
`mime_type`	text	File MIME type
`size_bytes`	int	File size
`width`	int	Image width (optional)
`height`	int	Image height (optional)
`uploaded_by`	uuid	FK → auth.users
`created_at`	timestamptz

Indexes:

(listing_id) — Photo lookup
(listing_id, is_primary) — Primary photo lookup

6b. `import_tasks`

Configurable listing import tasks with categories.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`mls_connection_id`	uuid	FK → mls_connections
`name`	text	Task display name
`description`	text	Optional description
`category`	text	`personal`, `office`, `search`, `custom`
`filter_config`	jsonb	MLS query configuration
`status_filters`	text[]	e.g., `['Active', 'Pending']`
`price_min`	int	Minimum price filter
`price_max`	int	Maximum price filter
`max_import_limit`	int	Max listings to import
`field_mapping_id`	uuid	FK → field_mappings (optional)
`sync_cadence`	text	`manual`, `hourly`, `daily`
`is_enabled`	boolean	Task is active
`is_agent_visible`	boolean	Show in agent dashboard
`last_synced_at`	timestamptz	Last sync timestamp
`last_sync_cursor`	text	Incremental sync cursor
`created_at`	timestamptz
`updated_at`	timestamptz

Filter Config Examples:

Personal listings:

{ "agentMlsId": "12345" }

Office listings:

{ "officeMlsId": "BROKER123" }

Service area:

{ "cities": ["miami"], "subdivisions": ["coconut-grove"] }

7. `user_profiles`

Extends Supabase Auth users with workspace context.

Column	Type	Notes
`id`	uuid	PK, FK → auth.users
`workspace_id`	uuid	FK → workspaces
`role`	text	`admin` \| `agent` \| `client`
`full_name`	text
`created_at`	timestamptz
`updated_at`	timestamptz

8. `favorites`

User's favorited listings.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`user_id`	uuid	FK → auth.users
`listing_id`	uuid	FK → listings
`created_at`	timestamptz

Unique: (workspace_id, user_id, listing_id)

9. `saved_searches`

Stored search queries for alerts.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`user_id`	uuid	FK → auth.users
`name`	text	User-defined name
`query`	jsonb	Typesense-compatible query params
`created_at`	timestamptz
`updated_at`	timestamptz

10. `compliance_profiles`

MLS attribution and disclaimer configuration.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces (1:1)
`attribution_html`	text	Required MLS attribution text
`disclaimer_html`	text	Legal disclaimer
`display_on_search`	boolean	Show on search results
`display_on_detail`	boolean	Show on listing detail
`created_at`	timestamptz
`updated_at`	timestamptz

Unique: (workspace_id)

11. `sync_runs`

Audit log of sync operations.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`mls_connection_id`	uuid	FK → mls_connections
`import_task_id`	uuid	FK → import_tasks (optional)
`started_at`	timestamptz
`finished_at`	timestamptz
`status`	text	`running` \| `success` \| `error`
`stats`	jsonb	`{ fetched, upserted, indexed, errors, duration_ms }`
`error`	text	Error message if failed

Index: (workspace_id, started_at DESC)

Stats JSON:

{
  "fetched": 150,
  "upserted": 148,
  "indexed": 148,
  "errors": 2,
  "duration_ms": 45000
}

12. `ai_usage`

AI token usage tracking per workspace.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`operation`	text	`website_blocks`, `listing_description`, etc.
`model`	text	`claude-3-haiku`, `claude-sonnet-4`, etc.
`input_tokens`	int	Input token count
`output_tokens`	int	Output token count
`cost_cents`	int	Calculated cost in cents
`created_at`	timestamptz

13. `ai_content_audit`

Audit trail for all AI-generated content.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces
`listing_id`	uuid	FK → listings (nullable)
`content_type`	text	Type of content generated
`model_id`	text	Model used for generation
`generated_content`	text	Raw AI output
`approved_content`	text	Final approved version
`fair_housing_check_passed`	boolean	Fair Housing filter result
`flagged_terms`	text[]	Terms flagged by safeguards
`status`	text	`pending` \| `approved` \| `rejected`
`reviewed_by`	uuid	FK → auth.users
`reviewed_at`	timestamptz
`created_at`	timestamptz

RLS: Both AI tables have RLS enabled with no policies (service role only).

14. `stripe_customers`

Maps workspaces to Stripe customer IDs. One-to-one with workspaces.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces, unique
`stripe_customer_id`	text	Stripe `cus_...` ID, unique
`email`	text	Customer email
`created_at`	timestamptz
`updated_at`	timestamptz

15. `subscriptions`

Workspace subscription state. One-to-one with workspaces.

Column	Type	Notes
`id`	uuid	PK
`workspace_id`	uuid	FK → workspaces, unique
`stripe_customer_id`	text	Stripe customer ID
`stripe_subscription_id`	text	Stripe `sub_...` ID, unique
`plan`	workspace_plan	`agent` \| `professional` \| `prestige`
`status`	subscription_status	`active` \| `trialing` \| `past_due` \| `canceled` \| etc.
`billing_interval`	billing_interval	`month` \| `year`
`current_period_start`	timestamptz
`current_period_end`	timestamptz	Used for grace period calculation
`cancel_at_period_end`	boolean	Default false
`trial_end`	timestamptz
`is_billing_override`	boolean	Default false — charter partner flag
`override_reason`	text
`override_granted_by`	uuid	FK → auth.users
`created_at`	timestamptz
`updated_at`	timestamptz

16. `payment_events`

Append-only audit log of Stripe webhook events. Unique constraint on stripe_event_id for idempotency.

Column	Type	Notes
`id`	uuid	PK
`stripe_event_id`	text	Stripe `evt_...` ID, unique
`stripe_event_type`	text	e.g., `customer.subscription.updated`
`workspace_id`	uuid	FK → workspaces, nullable
`amount_cents`	integer	Payment amount (invoice events)
`raw`	jsonb	Full event payload
`created_at`	timestamptz

RLS: All three billing tables have RLS enabled with no policies (service role only).

Row Level Security (RLS)

Enable RLS on user-facing tables:

ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE favorites ENABLE ROW LEVEL SECURITY;
ALTER TABLE saved_searches ENABLE ROW LEVEL SECURITY;

V1 Strategy:

Admin operations use service_role key (server-side only)
Client site uses server routes to fetch listings (no direct DB access from client)
User favorites/saved searches: RLS policies based on auth.uid()

Migration Order

Extensions (pgcrypto)
workspaces
workspace_domains
mls_connections
field_mappings
listings
listing_media
user_profiles
favorites
saved_searches
compliance_profiles
sync_runs
Indexes
RLS policies

Database Schema

On this page