← back to Stayclaim
db/schema.sql
161 lines
-- stayclaim schema
-- Listings auto-ingested from external sources (Airbnb / VRBO / Booking)
-- then claimable by owners who upgrade to paid tier.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS listing (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
slug text UNIQUE NOT NULL,
source text NOT NULL, -- 'airbnb' | 'vrbo' | 'booking' | 'manual'
source_id text, -- external id from source
title text NOT NULL,
headline text,
description text,
address_line1 text,
city text,
state text,
postal_code text,
country text DEFAULT 'US',
latitude numeric(9,6),
longitude numeric(9,6),
bedrooms int,
bathrooms numeric(3,1),
sleeps int,
property_type text, -- 'entire home' / 'apartment' / ...
amenities jsonb DEFAULT '[]'::jsonb,
hero_image text,
images jsonb DEFAULT '[]'::jsonb,
nightly_price numeric(10,2),
currency text DEFAULT 'USD',
source_url text,
ingested_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
claimed_by uuid, -- -> owner.id once claimed
claimed_at timestamptz,
tier text NOT NULL DEFAULT 'free', -- free | basic | pro
is_public boolean NOT NULL DEFAULT true,
UNIQUE (source, source_id)
);
CREATE INDEX IF NOT EXISTS idx_listing_city ON listing (lower(city));
CREATE INDEX IF NOT EXISTS idx_listing_claimed ON listing (claimed_by) WHERE claimed_by IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_listing_tier ON listing (tier);
CREATE INDEX IF NOT EXISTS idx_listing_public ON listing (is_public) WHERE is_public = true;
CREATE TABLE IF NOT EXISTS owner (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text UNIQUE NOT NULL,
name text,
phone text,
stripe_customer_id text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS claim_request (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
listing_id uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
email text NOT NULL,
proof_note text,
token text UNIQUE NOT NULL, -- email-verification token
status text NOT NULL DEFAULT 'pending', -- pending | verified | rejected
created_at timestamptz NOT NULL DEFAULT now(),
verified_at timestamptz
);
CREATE TABLE IF NOT EXISTS upgrade (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
listing_id uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
owner_id uuid NOT NULL REFERENCES owner(id) ON DELETE CASCADE,
tier text NOT NULL, -- basic ($9.99) | pro | ...
stripe_subscription_id text,
status text NOT NULL DEFAULT 'active', -- active | canceled | past_due
started_at timestamptz NOT NULL DEFAULT now(),
canceled_at timestamptz,
UNIQUE (listing_id, tier)
);
-- ---------------------------------------------------------------
-- Graph layer: Person / Business entities and their ↔ Place associations.
-- Research report mandate (docs/deep-research-report.md):
-- * Every person/business association is a CLAIM with dates + evidence.
-- * Never stored as eternal truth.
-- * Source tier A (govt) / B (archive) / C (verified secondary) / D (social lead).
-- * NO current residences of living people. association.date_range MUST end
-- before today-N years OR the person must be historical/deceased/public-figure-historical.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS entity (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
slug text UNIQUE NOT NULL,
kind text NOT NULL, -- 'person' | 'business' | 'architect' | 'studio'
display_name text NOT NULL,
also_known_as text[],
birth_year int,
death_year int,
is_living boolean, -- nullable until verified
bio_short text,
wiki_url text,
hero_image text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_entity_kind ON entity (kind);
CREATE INDEX IF NOT EXISTS idx_entity_name ON entity (lower(display_name));
CREATE TABLE IF NOT EXISTS entity_place_association (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
entity_id uuid NOT NULL REFERENCES entity(id) ON DELETE CASCADE,
listing_id uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
relation text NOT NULL, -- 'resident' | 'owner' | 'designer' | 'guest' | 'tenant'
date_from date,
date_to date,
summary text,
source_tier char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')),
source_urls text[],
source_notes text,
confidence numeric(3,2) NOT NULL DEFAULT 0.5 CHECK (confidence BETWEEN 0 AND 1),
review_status text NOT NULL DEFAULT 'pending', -- pending | approved | rejected
public_visible boolean NOT NULL DEFAULT false, -- hard gate — only after review
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_epa_entity ON entity_place_association (entity_id);
CREATE INDEX IF NOT EXISTS idx_epa_listing ON entity_place_association (listing_id);
CREATE INDEX IF NOT EXISTS idx_epa_visible ON entity_place_association (public_visible) WHERE public_visible;
-- Privacy gate: no association for a LIVING person with date_to either
-- NULL or within the last 25 years is allowed to be publicly visible.
CREATE OR REPLACE FUNCTION epa_privacy_gate() RETURNS trigger AS $$
BEGIN
IF NEW.public_visible THEN
IF EXISTS (
SELECT 1 FROM entity e WHERE e.id = NEW.entity_id
AND COALESCE(e.is_living, true) = true
AND (NEW.date_to IS NULL OR NEW.date_to > (CURRENT_DATE - INTERVAL '25 years'))
) THEN
RAISE EXCEPTION 'privacy gate: cannot publish a living-person association ending within the last 25 years';
END IF;
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS epa_privacy_gate_trg ON entity_place_association;
CREATE TRIGGER epa_privacy_gate_trg BEFORE INSERT OR UPDATE ON entity_place_association
FOR EACH ROW EXECUTE FUNCTION epa_privacy_gate();
-- Editor fields: what owner adds after claiming.
-- Separate table so we can diff against the auto-ingested source-of-truth `listing` row.
CREATE TABLE IF NOT EXISTS listing_override (
listing_id uuid PRIMARY KEY REFERENCES listing(id) ON DELETE CASCADE,
title text,
headline text,
description text,
address_line1 text,
images jsonb,
amenities jsonb,
contact_email text,
contact_phone text,
booking_url text,
custom_domain text,
updated_at timestamptz NOT NULL DEFAULT now()
);