pounce/DATABASE_MIGRATIONS.md

Database Migrations Guide

Quick Overview

When deploying Pounce to a new server, these tables need to be created:

✅ Core Tables (17) - User, Subscription, Domain, TLD, etc.
🆕 New Tables (6)  - Listings, Sniper Alerts, SEO Data

---

Automatic Migration

The easiest way to create all tables:

cd backend
source venv/bin/activate
python scripts/init_db.py

This creates all tables from the SQLAlchemy models automatically.

---

Manual SQL Migration

If you need to run migrations manually (e.g., on an existing database), use the SQL below.

NEW Table 1: Domain Listings (For Sale Marketplace)

-- Main listing table
CREATE TABLE domain_listings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    domain VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(300) NOT NULL UNIQUE,
    title VARCHAR(200),
    description TEXT,
    asking_price FLOAT,
    min_offer FLOAT,
    currency VARCHAR(3) DEFAULT 'USD',
    price_type VARCHAR(20) DEFAULT 'fixed',  -- 'fixed', 'negotiable', 'make_offer'
    pounce_score INTEGER,
    estimated_value FLOAT,
    verification_status VARCHAR(20) DEFAULT 'not_started',  -- 'not_started', 'pending', 'verified', 'failed'
    verification_code VARCHAR(64),
    verified_at TIMESTAMP,
    status VARCHAR(30) DEFAULT 'draft',  -- 'draft', 'published', 'sold', 'expired', 'removed'
    show_valuation BOOLEAN DEFAULT TRUE,
    allow_offers BOOLEAN DEFAULT TRUE,
    featured BOOLEAN DEFAULT FALSE,
    view_count INTEGER DEFAULT 0,
    inquiry_count INTEGER DEFAULT 0,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    published_at TIMESTAMP
);

CREATE INDEX idx_listings_user_id ON domain_listings(user_id);
CREATE INDEX idx_listings_domain ON domain_listings(domain);
CREATE INDEX idx_listings_slug ON domain_listings(slug);
CREATE INDEX idx_listings_status ON domain_listings(status);
CREATE INDEX idx_listings_price ON domain_listings(asking_price);

NEW Table 2: Listing Inquiries

-- Contact inquiries from potential buyers
CREATE TABLE listing_inquiries (
    id SERIAL PRIMARY KEY,
    listing_id INTEGER NOT NULL REFERENCES domain_listings(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    company VARCHAR(200),
    message TEXT NOT NULL,
    offer_amount FLOAT,
    status VARCHAR(20) DEFAULT 'new',  -- 'new', 'read', 'replied', 'archived'
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW(),
    read_at TIMESTAMP,
    replied_at TIMESTAMP
);

CREATE INDEX idx_inquiries_listing_id ON listing_inquiries(listing_id);
CREATE INDEX idx_inquiries_status ON listing_inquiries(status);

NEW Table 3: Listing Views

-- Analytics: page views
CREATE TABLE listing_views (
    id SERIAL PRIMARY KEY,
    listing_id INTEGER NOT NULL REFERENCES domain_listings(id) ON DELETE CASCADE,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    referrer VARCHAR(500),
    user_id INTEGER REFERENCES users(id),
    viewed_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_views_listing_id ON listing_views(listing_id);
CREATE INDEX idx_views_date ON listing_views(viewed_at);

NEW Table 4: Sniper Alerts

-- Saved filter configurations for personalized auction alerts
CREATE TABLE sniper_alerts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    name VARCHAR(100) NOT NULL,
    description VARCHAR(500),
    
    -- Filter criteria (stored as JSON for flexibility)
    filter_criteria JSONB NOT NULL DEFAULT '{}',
    
    -- Quick filters (also stored as columns for fast queries)
    tlds VARCHAR(500),              -- comma-separated: "com,net,io"
    keywords VARCHAR(500),          -- comma-separated search terms
    exclude_keywords VARCHAR(500),  -- words to exclude
    max_length INTEGER,
    min_length INTEGER,
    max_price FLOAT,
    min_price FLOAT,
    max_bids INTEGER,
    ending_within_hours INTEGER,
    platforms VARCHAR(200),         -- "GoDaddy,Sedo,NameJet"
    
    -- Vanity filters
    no_numbers BOOLEAN DEFAULT FALSE,
    no_hyphens BOOLEAN DEFAULT FALSE,
    exclude_chars VARCHAR(50),
    
    -- Notification settings
    notify_email BOOLEAN DEFAULT TRUE,
    notify_sms BOOLEAN DEFAULT FALSE,
    notify_push BOOLEAN DEFAULT FALSE,
    max_notifications_per_day INTEGER DEFAULT 10,
    cooldown_minutes INTEGER DEFAULT 30,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    matches_count INTEGER DEFAULT 0,
    notifications_sent INTEGER DEFAULT 0,
    last_matched_at TIMESTAMP,
    last_notified_at TIMESTAMP,
    
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_alerts_user_id ON sniper_alerts(user_id);
CREATE INDEX idx_alerts_active ON sniper_alerts(is_active);

NEW Table 5: Sniper Alert Matches

-- Matched auctions for each alert
CREATE TABLE sniper_alert_matches (
    id SERIAL PRIMARY KEY,
    alert_id INTEGER NOT NULL REFERENCES sniper_alerts(id) ON DELETE CASCADE,
    domain VARCHAR(255) NOT NULL,
    platform VARCHAR(50) NOT NULL,
    current_bid FLOAT NOT NULL,
    end_time TIMESTAMP NOT NULL,
    auction_url VARCHAR(500),
    notified BOOLEAN DEFAULT FALSE,
    clicked BOOLEAN DEFAULT FALSE,
    matched_at TIMESTAMP DEFAULT NOW(),
    notified_at TIMESTAMP
);

CREATE INDEX idx_matches_alert_id ON sniper_alert_matches(alert_id);
CREATE INDEX idx_matches_domain ON sniper_alert_matches(domain);
CREATE INDEX idx_matches_notified ON sniper_alert_matches(notified);

NEW Table 6: SEO Data (Tycoon Feature)

-- Cached SEO metrics for domains (Moz API or estimation)
CREATE TABLE domain_seo_data (
    id SERIAL PRIMARY KEY,
    domain VARCHAR(255) NOT NULL UNIQUE,
    
    -- Core metrics
    domain_authority INTEGER,       -- 0-100
    page_authority INTEGER,         -- 0-100
    spam_score INTEGER,             -- 0-100
    total_backlinks INTEGER,
    referring_domains INTEGER,
    
    -- Backlink analysis
    top_backlinks JSONB,            -- [{domain, authority, page}, ...]
    notable_backlinks TEXT,         -- comma-separated high-value domains
    
    -- Notable link flags
    has_wikipedia_link BOOLEAN DEFAULT FALSE,
    has_gov_link BOOLEAN DEFAULT FALSE,
    has_edu_link BOOLEAN DEFAULT FALSE,
    has_news_link BOOLEAN DEFAULT FALSE,
    
    -- Value estimation
    seo_value_estimate FLOAT,       -- Estimated $ value based on SEO metrics
    
    -- Metadata
    data_source VARCHAR(50) DEFAULT 'estimated',  -- 'moz', 'estimated'
    last_updated TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP,           -- Cache expiry (7 days)
    fetch_count INTEGER DEFAULT 0
);

CREATE INDEX idx_seo_domain ON domain_seo_data(domain);
CREATE INDEX idx_seo_da ON domain_seo_data(domain_authority);

---

All Tables Summary

Core Tables (Already Implemented)

Table	Purpose
users	User accounts and authentication
subscriptions	Subscription plans (Scout, Trader, Tycoon)
domains	Tracked domains in watchlists
domain_checks	Domain availability check history
tld_prices	TLD price history (886+ TLDs)
tld_info	TLD metadata and categories
portfolio_domains	User-owned domains
domain_valuations	Domain valuation history
domain_auctions	Scraped auction listings
auction_scrape_logs	Scraping job logs
newsletter_subscribers	Email newsletter list
price_alerts	TLD price change alerts
admin_activity_logs	Admin action audit log
blog_posts	Blog content

New Tables (v2.0)

Table	Purpose	Required For
domain_listings	For Sale marketplace	/command/listings, /buy
listing_inquiries	Buyer messages	Marketplace inquiries
listing_views	View analytics	Listing stats
sniper_alerts	Alert configurations	/command/alerts
sniper_alert_matches	Matched auctions	Alert notifications
domain_seo_data	SEO metrics cache	/command/seo (Tycoon)

---

Verification

After migration, verify all tables exist:

-- PostgreSQL
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
ORDER BY table_name;

-- Should include:
-- domain_listings
-- listing_inquiries
-- listing_views
-- sniper_alerts
-- sniper_alert_matches
-- domain_seo_data

---

Environment Variables for New Features

Moz API (Optional - for real SEO data)

MOZ_ACCESS_ID=your_moz_access_id
MOZ_SECRET_KEY=your_moz_secret_key

Without these variables, the SEO analyzer uses estimation mode based on domain characteristics (length, TLD, keywords).

Stripe (Required for payments)

STRIPE_SECRET_KEY=sk_live_xxx
STRIPE_WEBHOOK_SECRET=whsec_xxx
STRIPE_PRICE_TRADER=price_xxx  # €9/month
STRIPE_PRICE_TYCOON=price_xxx  # €29/month

---

Scheduler Jobs

These background jobs run automatically when the backend starts:

Job	Schedule	Table Affected
Sniper Alert Matching	Every 15 min	sniper_alert_matches
Auction Scrape	Hourly	domain_auctions
TLD Price Scrape	Daily 03:00	tld_prices
Domain Check	Daily 06:00	domain_checks

---

Rollback

If you need to remove the new tables:

DROP TABLE IF EXISTS sniper_alert_matches CASCADE;
DROP TABLE IF EXISTS sniper_alerts CASCADE;
DROP TABLE IF EXISTS listing_views CASCADE;
DROP TABLE IF EXISTS listing_inquiries CASCADE;
DROP TABLE IF EXISTS domain_listings CASCADE;
DROP TABLE IF EXISTS domain_seo_data CASCADE;

---

Related Documentation

• README.md - Full deployment guide
• DEPLOYMENT.md - Server setup details
• backend/app/models/ - SQLAlchemy model definitions