← back to Animals
migrations/003_community_marketplace.sql
206 lines
-- Project: Animals — community + marketplace layer.
--
-- Adds the social-graph + marketplace tables that turn the directory into a
-- user-generated platform (sub-brand: "PawCircles" inside AnimalsDirectory).
--
-- Design tenets:
-- - ZIP is the primary unit of community. Everyone sees their own ZIP by
-- default. Cross-ZIP visibility is opt-in via `circle_memberships`.
-- - No copy-paste from existing players (Nextdoor, Facebook Groups). The
-- vocabulary here is generic: "circle", "block", "post", "listing".
-- - Listings have a small finite type set so they can be priced + searched.
-- - Soft-delete (status='removed') instead of hard-delete; we need to keep
-- evidence of policy violations.
BEGIN;
-- Extend app_users with the social fields we need (already exists from migration 001).
ALTER TABLE app_users
ADD COLUMN IF NOT EXISTS handle TEXT UNIQUE,
ADD COLUMN IF NOT EXISTS home_zip TEXT,
ADD COLUMN IF NOT EXISTS home_city TEXT,
ADD COLUMN IF NOT EXISTS home_state TEXT,
ADD COLUMN IF NOT EXISTS home_lat NUMERIC(10,7),
ADD COLUMN IF NOT EXISTS home_lng NUMERIC(10,7),
ADD COLUMN IF NOT EXISTS species_pets TEXT[], -- ['dog','cat']
ADD COLUMN IF NOT EXISTS visibility_default TEXT NOT NULL DEFAULT 'home_zip'
CHECK (visibility_default IN ('home_zip','home_zip_25mi','statewide','approved_circles_only','public')),
ADD COLUMN IF NOT EXISTS approved_circles BIGINT[] NOT NULL DEFAULT '{}',
ADD COLUMN IF NOT EXISTS approved_zips TEXT[] NOT NULL DEFAULT '{}',
ADD COLUMN IF NOT EXISTS marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS suspended_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS suspension_reason TEXT;
CREATE INDEX IF NOT EXISTS idx_app_users_zip ON app_users(home_zip);
CREATE INDEX IF NOT EXISTS idx_app_users_state ON app_users(home_state);
-- ── Sessions (simple opaque cookie token, bcrypt-validated on signup/login) ─
CREATE TABLE IF NOT EXISTS app_sessions (
token TEXT PRIMARY KEY,
app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
ip TEXT,
user_agent TEXT,
expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '90 days'
);
CREATE INDEX IF NOT EXISTS idx_app_sessions_user ON app_sessions(app_user_id);
-- ── Circles (ZIP-based by default, can be user-curated topical too) ────────
CREATE TABLE IF NOT EXISTS circles (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('zip','city','county','topic','breed')),
slug TEXT NOT NULL UNIQUE, -- 'zip-90025' / 'city-los-angeles-ca' / 'topic-puppy-training'
name TEXT NOT NULL,
description TEXT,
zip TEXT, -- populated when kind='zip'
city TEXT,
state TEXT,
breed_id BIGINT REFERENCES breeds(id) ON DELETE SET NULL,
member_count INTEGER NOT NULL DEFAULT 0,
is_official BOOLEAN NOT NULL DEFAULT FALSE, -- auto-created vs user-curated
created_by BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_circles_kind ON circles(kind);
CREATE INDEX IF NOT EXISTS idx_circles_zip ON circles(zip);
CREATE TABLE IF NOT EXISTS circle_memberships (
circle_id BIGINT NOT NULL REFERENCES circles(id) ON DELETE CASCADE,
app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member','moderator','owner')),
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (circle_id, app_user_id)
);
CREATE INDEX IF NOT EXISTS idx_memberships_user ON circle_memberships(app_user_id);
-- ── Marketplace listings (the post engine) ─────────────────────────────────
-- Listing types are tightly enumerated so we can price + filter precisely.
CREATE TABLE IF NOT EXISTS marketplace_listings (
id BIGSERIAL PRIMARY KEY,
app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
listing_type TEXT NOT NULL CHECK (listing_type IN (
'lost_pet','found_pet','adoption','for_sale','wanted',
'service_offered','service_wanted','event','recommendation','question',
'business_promo','free_to_good_home'
)),
title TEXT NOT NULL,
body TEXT,
zip TEXT NOT NULL, -- always tied to a ZIP for radius search
city TEXT,
state TEXT,
latitude NUMERIC(10,7),
longitude NUMERIC(10,7),
visibility TEXT NOT NULL DEFAULT 'home_zip_25mi'
CHECK (visibility IN ('home_zip','home_zip_25mi','statewide','approved_circles_only','public')),
visible_circles BIGINT[] NOT NULL DEFAULT '{}',
price_cents INTEGER,
species TEXT, -- 'dog' | 'cat' | 'bird' | etc.
breed_id BIGINT REFERENCES breeds(id) ON DELETE SET NULL,
contact_email TEXT,
contact_phone TEXT,
photo_urls TEXT[],
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('draft','active','expired','closed','removed','flagged')),
view_count INTEGER NOT NULL DEFAULT 0,
reaction_count INTEGER NOT NULL DEFAULT 0,
comment_count INTEGER NOT NULL DEFAULT 0,
bumped_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '30 days',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_listings_zip ON marketplace_listings(zip);
CREATE INDEX IF NOT EXISTS idx_listings_state ON marketplace_listings(state);
CREATE INDEX IF NOT EXISTS idx_listings_type ON marketplace_listings(listing_type);
CREATE INDEX IF NOT EXISTS idx_listings_status ON marketplace_listings(status);
CREATE INDEX IF NOT EXISTS idx_listings_bumped ON marketplace_listings(bumped_at DESC);
CREATE INDEX IF NOT EXISTS idx_listings_user ON marketplace_listings(app_user_id);
CREATE OR REPLACE FUNCTION bump_marketplace_updated() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_listings_updated BEFORE UPDATE ON marketplace_listings
FOR EACH ROW EXECUTE FUNCTION bump_marketplace_updated();
-- ── Comments + reactions (the social-graph plumbing) ───────────────────────
CREATE TABLE IF NOT EXISTS comments (
id BIGSERIAL PRIMARY KEY,
parent_type TEXT NOT NULL CHECK (parent_type IN ('listing','comment')),
parent_id BIGINT NOT NULL,
app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
body TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','removed','flagged')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_type, parent_id);
CREATE INDEX IF NOT EXISTS idx_comments_user ON comments(app_user_id);
CREATE TABLE IF NOT EXISTS reactions (
id BIGSERIAL PRIMARY KEY,
parent_type TEXT NOT NULL CHECK (parent_type IN ('listing','comment')),
parent_id BIGINT NOT NULL,
app_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
emoji TEXT NOT NULL, -- '🐾' | '❤️' | '🦴' | '👀' | '🆘'
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (parent_type, parent_id, app_user_id, emoji)
);
CREATE INDEX IF NOT EXISTS idx_reactions_parent ON reactions(parent_type, parent_id);
-- ── B2B data orders (selling the curated list to marketing firms) ──────────
-- A "list" is a saved query (state, category, has_email, has_phone, etc.)
-- with a price tier. Buyer pays, gets a CSV download URL + monthly refresh.
CREATE TABLE IF NOT EXISTS data_orders (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
company TEXT,
email TEXT NOT NULL,
phone TEXT,
list_label TEXT NOT NULL, -- 'CA vet clinics with email', 'all US shelters', etc.
filters_json JSONB NOT NULL, -- {state:'CA', category:'vet_clinic', has_email:true}
estimated_rows INTEGER,
tier TEXT NOT NULL DEFAULT 'one_time' CHECK (tier IN ('one_time','monthly_refresh','quarterly')),
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'pending_payment'
CHECK (status IN ('pending_payment','paid','generated','delivered','refunded','cancelled')),
payment_link TEXT,
stripe_session_id TEXT,
paid_at TIMESTAMPTZ,
generated_csv_path TEXT,
download_url TEXT, -- one-time signed URL after payment
delivered_at TIMESTAMPTZ,
notes TEXT,
ip TEXT,
user_agent TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_data_orders_status ON data_orders(status);
CREATE INDEX IF NOT EXISTS idx_data_orders_email ON data_orders(LOWER(email));
-- ── Traffic-research signals (what queries drive traffic in pet/dog) ───────
CREATE TABLE IF NOT EXISTS traffic_signals (
id BIGSERIAL PRIMARY KEY,
source TEXT NOT NULL, -- 'google_autocomplete' | 'google_trends_rss' | 'reddit_top' | 'wikipedia_views'
topic TEXT NOT NULL, -- the seed query, e.g., 'best dog food'
suggestion TEXT NOT NULL, -- the suggested expansion / related query
signal_value NUMERIC(8,2), -- normalized 0..100 (or count)
region TEXT DEFAULT 'US',
recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE (source, topic, suggestion, region, recorded_at)
);
CREATE INDEX IF NOT EXISTS idx_traffic_topic ON traffic_signals(topic);
CREATE INDEX IF NOT EXISTS idx_traffic_value ON traffic_signals(signal_value DESC NULLS LAST);
COMMIT;