-- ===================================================================== -- ReviewsEmbed SaaS — Canonical Database Schema -- Version: 1.0.0.0 -- Target: MySQL 8.0 -- Engine: InnoDB, Charset: utf8mb4, Collation: utf8mb4_unicode_ci -- ===================================================================== -- Order is dependency-aware. Run top to bottom on a fresh database. -- All FKs use ON DELETE CASCADE for tenant data so user removal is clean. -- All monetary values are stored in cents (BIGINT) to avoid float drift. -- All timestamps are stored as DATETIME UTC (no TIMESTAMP / TZ surprises). -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- --------------------------------------------------------------------- -- USERS — subscriber accounts -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, is_admin TINYINT(1) NOT NULL DEFAULT 0, email_verified_at DATETIME NULL, last_login_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SESSIONS — server-side session tokens -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sessions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, token CHAR(64) NOT NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(500) NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_sessions_token (token), KEY ix_sessions_user (user_id), KEY ix_sessions_expires (expires_at), CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- PLANS — catalog of subscription tiers -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS plans ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tier VARCHAR(32) NOT NULL, display_name VARCHAR(100) NOT NULL, monthly_price_cents BIGINT NOT NULL, stripe_price_id VARCHAR(255) NULL, max_locations INT NOT NULL, sync_interval_minutes INT NOT NULL, max_widgets INT NOT NULL, allows_custom_css TINYINT(1) NOT NULL DEFAULT 0, allows_ai_draft_reply TINYINT(1) NOT NULL DEFAULT 0, ai_draft_monthly_quota INT NOT NULL DEFAULT 0, allows_custom_font TINYINT(1) NOT NULL DEFAULT 0, allows_white_label TINYINT(1) NOT NULL DEFAULT 0, allows_api_access TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_plans_tier (tier) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SUBSCRIPTIONS — Stripe subscription state mirror -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL, stripe_customer_id VARCHAR(255) NOT NULL, stripe_subscription_id VARCHAR(255) NULL, status VARCHAR(32) NOT NULL DEFAULT 'incomplete', current_period_start DATETIME NULL, current_period_end DATETIME NULL, cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0, canceled_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_subscriptions_stripe_sub (stripe_subscription_id), KEY ix_subscriptions_user (user_id), KEY ix_subscriptions_status (status), CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- GOOGLE_CONNECTIONS — OAuth tokens, encrypted at rest -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS google_connections ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, google_account_email VARCHAR(255) NOT NULL, encrypted_access_token TEXT NOT NULL, encrypted_refresh_token TEXT NOT NULL, access_token_expires_at DATETIME NOT NULL, scopes_granted TEXT NOT NULL, last_synced_at DATETIME NULL, sync_status VARCHAR(32) NOT NULL DEFAULT 'ok', last_sync_error TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_google_conn_user (user_id), KEY ix_google_conn_status (sync_status), CONSTRAINT fk_google_conn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- BUSINESS_LOCATIONS — locations linked to a user's Google Business Profile -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS business_locations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, google_account_id VARCHAR(255) NOT NULL, google_location_id VARCHAR(255) NOT NULL, google_resource_name VARCHAR(500) NOT NULL, display_name VARCHAR(255) NOT NULL, place_id VARCHAR(255) NULL, address_line VARCHAR(500) NULL, average_rating DECIMAL(3,2) NULL, total_review_count INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, last_synced_at DATETIME NULL, next_sync_due_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_locations_user_gloc (user_id, google_location_id), KEY ix_locations_user (user_id), KEY ix_locations_next_sync (next_sync_due_at, is_active), CONSTRAINT fk_locations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- REVIEWS — cached reviews from Google -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS reviews ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, location_id BIGINT UNSIGNED NOT NULL, google_review_id VARCHAR(255) NOT NULL, google_review_name VARCHAR(500) NOT NULL, reviewer_display_name VARCHAR(255) NOT NULL, reviewer_photo_url VARCHAR(1000) NULL, reviewer_is_anonymous TINYINT(1) NOT NULL DEFAULT 0, star_rating TINYINT NOT NULL, comment TEXT NULL, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, has_owner_reply TINYINT(1) NOT NULL DEFAULT 0, owner_reply_text TEXT NULL, owner_reply_time DATETIME NULL, is_hidden TINYINT(1) NOT NULL DEFAULT 0, fetched_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_reviews_google_id (google_review_id), KEY ix_reviews_location_time (location_id, create_time), KEY ix_reviews_rating (star_rating), CONSTRAINT fk_reviews_location FOREIGN KEY (location_id) REFERENCES business_locations(id) ON DELETE CASCADE, CONSTRAINT chk_reviews_rating CHECK (star_rating BETWEEN 1 AND 5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- WIDGET_CONFIGS — per-widget customization (multiple widgets allowed on Pro+) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS widget_configs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, public_widget_id CHAR(32) NOT NULL, widget_name VARCHAR(100) NOT NULL DEFAULT 'My Reviews Widget', isolation_mode VARCHAR(16) NOT NULL DEFAULT 'iframe', -- Layout layout VARCHAR(16) NOT NULL DEFAULT 'GRID', cards_per_row TINYINT NOT NULL DEFAULT 3, max_reviews_shown INT NOT NULL DEFAULT 12, pagination_style VARCHAR(24) NOT NULL DEFAULT 'none', width_mode VARCHAR(16) NOT NULL DEFAULT 'container', max_width_px INT NOT NULL DEFAULT 1200, equal_height_cards TINYINT(1) NOT NULL DEFAULT 1, -- Theme + colors theme VARCHAR(16) NOT NULL DEFAULT 'LIGHT', color_background CHAR(7) NOT NULL DEFAULT '#FFFFFF', color_card CHAR(7) NOT NULL DEFAULT '#FFFFFF', color_text CHAR(7) NOT NULL DEFAULT '#1A1A1A', color_accent CHAR(7) NOT NULL DEFAULT '#0EA5E9', color_star CHAR(7) NOT NULL DEFAULT '#FBBF24', color_star_empty CHAR(7) NOT NULL DEFAULT '#E5E7EB', color_border CHAR(7) NOT NULL DEFAULT '#E5E7EB', color_hover_overlay CHAR(7) NOT NULL DEFAULT '#0EA5E9', -- Borders + shape border_style VARCHAR(16) NOT NULL DEFAULT 'solid', border_width_px TINYINT NOT NULL DEFAULT 1, border_radius_px TINYINT NOT NULL DEFAULT 8, card_shadow_intensity TINYINT NOT NULL DEFAULT 2, -- Typography font_family VARCHAR(100) NOT NULL DEFAULT 'system', font_size VARCHAR(8) NOT NULL DEFAULT 'medium', star_size VARCHAR(8) NOT NULL DEFAULT 'medium', -- Content controls show_reviewer_photo TINYINT(1) NOT NULL DEFAULT 1, show_date TINYINT(1) NOT NULL DEFAULT 1, show_owner_reply TINYINT(1) NOT NULL DEFAULT 1, show_verified_badge TINYINT(1) NOT NULL DEFAULT 1, truncate_at_chars INT NOT NULL DEFAULT 0, min_star_rating_filter TINYINT NOT NULL DEFAULT 1, hide_empty_text_reviews TINYINT(1) NOT NULL DEFAULT 0, -- Animation (entrance) animation_style VARCHAR(16) NOT NULL DEFAULT 'NONE', animation_duration_ms INT NOT NULL DEFAULT 400, animation_easing VARCHAR(32) NOT NULL DEFAULT 'ease-out', -- Carousel-specific autoplay_ms INT NOT NULL DEFAULT 0, carousel_direction VARCHAR(16) NOT NULL DEFAULT 'ltr', carousel_pause_on_hover TINYINT(1) NOT NULL DEFAULT 1, carousel_transition VARCHAR(16) NOT NULL DEFAULT 'slide', -- Hover / mouse effects hover_effect VARCHAR(16) NOT NULL DEFAULT 'LIFT', star_hover_effect VARCHAR(16) NOT NULL DEFAULT 'NONE', cursor_style VARCHAR(16) NOT NULL DEFAULT 'default', click_behavior VARCHAR(24) NOT NULL DEFAULT 'none', click_url_template VARCHAR(500) NULL, -- Advanced (plan-gated) custom_css TEXT NULL, hide_powered_by TINYINT(1) NOT NULL DEFAULT 0, locale VARCHAR(8) NOT NULL DEFAULT 'en', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_widgets_public_id (public_widget_id), KEY ix_widgets_user (user_id), CONSTRAINT fk_widgets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT chk_widgets_cards_per_row CHECK (cards_per_row BETWEEN 1 AND 6), CONSTRAINT chk_widgets_max_reviews CHECK (max_reviews_shown BETWEEN 1 AND 50), CONSTRAINT chk_widgets_min_rating CHECK (min_star_rating_filter BETWEEN 1 AND 5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- WIDGET_LOCATIONS — which locations each widget pulls reviews from -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS widget_locations ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, widget_config_id BIGINT UNSIGNED NOT NULL, location_id BIGINT UNSIGNED NOT NULL, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_widget_locations (widget_config_id, location_id), KEY ix_widget_locations_loc (location_id), CONSTRAINT fk_wl_widget FOREIGN KEY (widget_config_id) REFERENCES widget_configs(id) ON DELETE CASCADE, CONSTRAINT fk_wl_location FOREIGN KEY (location_id) REFERENCES business_locations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- AI_REPLY_DRAFTS — Claude-drafted reply suggestions -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS ai_reply_drafts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, review_id BIGINT UNSIGNED NOT NULL, tone VARCHAR(24) NOT NULL DEFAULT 'professional', draft_text TEXT NOT NULL, was_used TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY ix_drafts_user_month (user_id, created_at), KEY ix_drafts_review (review_id), CONSTRAINT fk_drafts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_drafts_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SYNC_JOBS — audit log of sync runs -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS sync_jobs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, location_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, job_type VARCHAR(32) NOT NULL, status VARCHAR(32) NOT NULL DEFAULT 'running', reviews_added INT NOT NULL DEFAULT 0, reviews_updated INT NOT NULL DEFAULT 0, error_message TEXT NULL, started_at DATETIME NOT NULL, finished_at DATETIME NULL, PRIMARY KEY (id), KEY ix_sync_jobs_location (location_id, started_at), KEY ix_sync_jobs_status (status, started_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- WEBHOOK_EVENTS — Stripe webhook idempotency log -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS webhook_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, stripe_event_id VARCHAR(255) NOT NULL, event_type VARCHAR(100) NOT NULL, processed_at DATETIME NOT NULL, processing_status VARCHAR(32) NOT NULL DEFAULT 'ok', error_message TEXT NULL, PRIMARY KEY (id), UNIQUE KEY uq_webhook_stripe_id (stripe_event_id), KEY ix_webhook_type (event_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- AUDIT_LOG — security-relevant actions -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS audit_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, action VARCHAR(64) NOT NULL, target_type VARCHAR(64) NULL, target_id VARCHAR(64) NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(500) NULL, metadata_json TEXT NULL, occurred_at DATETIME NOT NULL, PRIMARY KEY (id), KEY ix_audit_user_time (user_id, occurred_at), KEY ix_audit_action (action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SYSTEM_CACHE — short-lived MySQL-backed cache -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS system_cache ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, cache_key VARCHAR(255) NOT NULL, cache_value LONGTEXT NOT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_cache_key (cache_key), KEY ix_cache_expires (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- END OF SCHEMA -- =====================================================================