-- ============================================================ -- DigiSites — Digital product website builder -- MySQL 8 / MariaDB 10.5+ -- Import once: mysql -u USER -p DBNAME < install.sql -- ============================================================ CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- Public bylines for guides (E-E-A-T). Separate from admin users. CREATE TABLE IF NOT EXISTS authors ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, slug VARCHAR(140) UNIQUE NOT NULL, bio_short VARCHAR(280), bio_long MEDIUMTEXT, photo_url VARCHAR(500), credentials VARCHAR(255), linkedin_url VARCHAR(500), twitter_url VARCHAR(500), website_url VARCHAR(500), email_public VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- A niche is a product category ("Notion templates", "Budget spreadsheets", -- "Printable wedding planners"). Shared defaults for every site in it. CREATE TABLE IF NOT EXISTS niches ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, slug VARCHAR(140) UNIQUE NOT NULL, description TEXT, product_type VARCHAR(120), -- "Notion template", "eBook (PDF)", "Excel spreadsheet", "Lightroom presets"... audience TEXT, -- who typically buys this theme_hints TEXT, palette_hue_base SMALLINT, seo_keywords JSON, -- {primary, variants[], modifiers[]}; [keyword] placeholder = site's primary keyword newsletter_pitch JSON, -- {headline, description, cta_text} created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- One website = one keyword-targeted sales site for one main digital product. CREATE TABLE IF NOT EXISTS websites ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, slug VARCHAR(180) UNIQUE NOT NULL, -- folder name under WEBSITES_ROOT domain VARCHAR(180), -- https://example.com (no trailing slash) niche_id INT NOT NULL, primary_keyword VARCHAR(190) NOT NULL, -- "notion budget template" secondary_keywords TEXT, -- one per line audience TEXT, -- who the product is for product_brief TEXT, -- admin's idea / notes for the main product research JSON, -- AI market research result content JSON, -- AI sales-page content (hero, benefits, faq, ...) theme JSON, -- theme tokens about_text MEDIUMTEXT, meta_title VARCHAR(255), meta_desc VARCHAR(500), custom_head MEDIUMTEXT, custom_body MEDIUMTEXT, currency CHAR(3) NOT NULL DEFAULT 'GBP', guarantee_days SMALLINT NOT NULL DEFAULT 30, -- 0 = no money-back guarantee shown newsletter_enabled TINYINT(1) NOT NULL DEFAULT 1, newsletter_pitch_override JSON, default_author_id INT, favicon_url VARCHAR(500), og_image_url VARCHAR(500), palette_source ENUM('claude','procedural') NULL, tracking_enabled TINYINT(1) NOT NULL DEFAULT 1, indexnow_key CHAR(32), status ENUM('draft','researched','content_ready','site_built','live') NOT NULL DEFAULT 'draft', built_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (niche_id) REFERENCES niches(id), FOREIGN KEY (default_author_id) REFERENCES authors(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; -- Products sold on a website. Exactly one active 'main' product per site is -- expected; any number of 'upsell' products (offered as order bumps, on the -- thank-you page, and on their own /products/ pages). CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, role ENUM('main','upsell') NOT NULL DEFAULT 'upsell', name VARCHAR(190) NOT NULL, slug VARCHAR(190) NOT NULL, tagline VARCHAR(255), short_desc VARCHAR(600), description MEDIUMTEXT, -- longer copy; light markdown (**bold**, [links](/..), blank-line paragraphs) features JSON, -- [{title, text}] included JSON, -- ["30-page PDF guide", "Excel tracker (.xlsx)", ...] format VARCHAR(190), -- "PDF + XLSX, instant download" version VARCHAR(40) DEFAULT '1.0', changelog MEDIUMTEXT, price INT NOT NULL DEFAULT 0, -- minor units (pence/cents) compare_at_price INT, -- optional strike-through price bump_price INT, -- price when added as an order bump (NULL = same as price) bump_headline VARCHAR(190), -- "Yes! Add the Budget Masterclass for just £9" free_for_existing_buyers TINYINT(1) NOT NULL DEFAULT 0, -- 1 = everyone who bought before launched_at gets it free launched_at DATETIME, active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 100, content_updated_at DATETIME, -- last time files/version changed (shown as "Last updated") build_brief JSON, -- AI build brief: deliverable specs, ready prompts, claim map, checklist build_brief_at DATETIME, build_brief_hash CHAR(40), -- sha1 of the product's promises when the brief was made (drift detection) created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_site_slug (website_id, slug), INDEX (website_id, role, active), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Deliverables. kind=file → stored privately under STORAGE_ROOT; kind=link → external URL -- (e.g. a Notion "duplicate" link, Canva template link, course URL). CREATE TABLE IF NOT EXISTS product_files ( id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, kind ENUM('file','link') NOT NULL DEFAULT 'file', label VARCHAR(190) NOT NULL, stored_name VARCHAR(255), original_name VARCHAR(255), mime VARCHAR(120), size_bytes BIGINT, sha256 CHAR(64), url VARCHAR(1000), sort_order INT NOT NULL DEFAULT 100, active TINYINT(1) NOT NULL DEFAULT 1, uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX (product_id), FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Marketing images. Master copies in STORAGE_ROOT/images; copied to the site's -- /assets/img at build time (web-optimised when GD is available). CREATE TABLE IF NOT EXISTS product_images ( id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, stored_name VARCHAR(255) NOT NULL, original_name VARCHAR(255), width INT, height INT, alt VARCHAR(255), sort_order INT NOT NULL DEFAULT 100, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX (product_id), FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- A buyer on a given website. access_token powers the permanent download link. CREATE TABLE IF NOT EXISTS customers ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, email VARCHAR(190) NOT NULL, name VARCHAR(190), stripe_customer_id VARCHAR(64), access_token CHAR(40) NOT NULL, updates_opt_out TINYINT(1) NOT NULL DEFAULT 0, -- stop product-update emails access_revoked TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_site_email (website_id, email), UNIQUE KEY uq_token (access_token), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS orders ( id INT AUTO_INCREMENT PRIMARY KEY, public_id CHAR(16) NOT NULL, website_id INT NOT NULL, customer_id INT, parent_order_id INT, -- set for post-purchase offers status ENUM('pending','paid','processing','refunded','partially_refunded','failed','expired') NOT NULL DEFAULT 'pending', currency CHAR(3) NOT NULL, subtotal INT NOT NULL DEFAULT 0, total INT NOT NULL DEFAULT 0, -- amount actually charged (after discounts / tax) email VARCHAR(190), customer_name VARCHAR(190), country CHAR(2), newsletter_opt_in TINYINT(1) NOT NULL DEFAULT 0, stripe_session_id VARCHAR(255), stripe_payment_intent VARCHAR(64), source_page VARCHAR(255), referrer_host VARCHAR(190), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME, receipt_sent_at DATETIME, UNIQUE KEY uq_public (public_id), UNIQUE KEY uq_session (stripe_session_id), INDEX (website_id, status, created_at), INDEX (customer_id), INDEX (stripe_payment_intent), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT, name VARCHAR(190) NOT NULL, unit_amount INT NOT NULL, is_bump TINYINT(1) NOT NULL DEFAULT 0, INDEX (order_id), INDEX (product_id), FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS download_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, file_id INT, ip_truncated VARCHAR(64), user_agent VARCHAR(255), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX (customer_id, created_at), INDEX (file_id), FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Newsletter / marketing list, per website. CREATE TABLE IF NOT EXISTS subscribers ( id BIGINT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, email VARCHAR(190) NOT NULL, source ENUM('checkout','form','import') NOT NULL DEFAULT 'form', source_page VARCHAR(255), customer_id INT, unsub_token CHAR(32) NOT NULL, ip_truncated VARCHAR(64), subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP, unsubscribed_at DATETIME, UNIQUE KEY uq_email_site (website_id, email), UNIQUE KEY uq_unsub (unsub_token), INDEX (unsubscribed_at), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Broadcast emails composed in admin (product updates, launches, newsletters). CREATE TABLE IF NOT EXISTS broadcasts ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT, -- NULL = network-wide product_id INT, kind ENUM('update','launch','newsletter') NOT NULL DEFAULT 'newsletter', audience ENUM('site_buyers','product_buyers','site_subscribers') NOT NULL, subject VARCHAR(255) NOT NULL, body_md MEDIUMTEXT NOT NULL, status ENUM('draft','queued','sent') NOT NULL DEFAULT 'draft', recipients INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, queued_at DATETIME, FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; -- Outbound email queue (transactional mails are attempted instantly and -- recorded here; broadcasts are drained by cron/send_emails.php). CREATE TABLE IF NOT EXISTS email_queue ( id BIGINT AUTO_INCREMENT PRIMARY KEY, website_id INT, broadcast_id INT, kind VARCHAR(20) NOT NULL, -- receipt | access | update | launch | newsletter | test to_email VARCHAR(190) NOT NULL, subject VARCHAR(255) NOT NULL, html MEDIUMTEXT NOT NULL, text_body MEDIUMTEXT NOT NULL, headers JSON, status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued', attempts TINYINT NOT NULL DEFAULT 0, last_error VARCHAR(500), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME, INDEX (status, id), INDEX (website_id, kind, created_at), INDEX (to_email) ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS guides ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, description TEXT, body JSON, meta_title VARCHAR(255), meta_desc VARCHAR(500), status ENUM('draft','published') DEFAULT 'draft', author_id INT, published_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(website_id), INDEX(author_id), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE, FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS guide_suggestions ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, title VARCHAR(255) NOT NULL, description TEXT, angle TEXT, evidence TEXT, search_intent VARCHAR(255), funnel_stage VARCHAR(20), status ENUM('pending','used','dismissed') NOT NULL DEFAULT 'pending', used_guide_id INT, used_at DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX(website_id, status), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE, FOREIGN KEY (used_guide_id) REFERENCES guides(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS site_texts ( `key` VARCHAR(64) PRIMARY KEY, body MEDIUMTEXT, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS batches ( id INT AUTO_INCREMENT PRIMARY KEY, website_id INT, type ENUM('research_market','generate_guide','generate_content','generate_brief') NOT NULL, status ENUM('queued','submitted','in_progress','ended','processed','failed','canceled') DEFAULT 'queued', anthropic_id VARCHAR(80), request_payload JSON, result_payload JSON, error_message TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, submitted_at DATETIME, completed_at DATETIME, INDEX(status), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS settings ( `key` VARCHAR(64) PRIMARY KEY, `value` TEXT ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS stripe_events ( id VARCHAR(64) PRIMARY KEY, type VARCHAR(64), processed_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- Traffic analytics (privacy-preserving, see lib/shared/tracking.php) CREATE TABLE IF NOT EXISTS pageview_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, path VARCHAR(255) NOT NULL, page_type VARCHAR(32), referrer_host VARCHAR(120), referrer_kind ENUM('direct','search','social','referral','internal','unknown','ai') NOT NULL DEFAULT 'unknown', ua_hash CHAR(16), session_hash CHAR(16), is_bot TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_website_date (website_id, created_at), INDEX idx_created (created_at), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS pageview_daily ( id BIGINT AUTO_INCREMENT PRIMARY KEY, website_id INT NOT NULL, day DATE NOT NULL, pageviews INT NOT NULL DEFAULT 0, sessions INT NOT NULL DEFAULT 0, search_count INT NOT NULL DEFAULT 0, ai_count INT NOT NULL DEFAULT 0, social_count INT NOT NULL DEFAULT 0, referral_count INT NOT NULL DEFAULT 0, direct_count INT NOT NULL DEFAULT 0, UNIQUE KEY uniq_website_day (website_id, day), FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS tracking_salt ( day DATE PRIMARY KEY, salt CHAR(32) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB CHARSET=utf8mb4; -- ------------------------------------------------------------ -- Seed data -- ------------------------------------------------------------ INSERT IGNORE INTO site_texts (`key`, body) VALUES ('promise_updates', 'Free lifetime updates. Every improvement we make to {product} is yours at no extra cost, and we email you the moment a new version is ready. Your download link always serves the latest version.'), ('promise_future', 'Free access to our future products. If we launch a new digital product on {site}, existing customers get it free, and we will let you know by email.'), ('terms', '

Terms of sale

{site} sells digital products that are delivered instantly by download after payment. When you buy, you receive a personal, non-transferable licence to use the product for your own personal or business purposes. You may not resell, share or redistribute the files.

Payments are processed securely by Stripe. Prices are shown in {currency} and include any applicable taxes shown at checkout.

Questions? Email {support_email}.

'), ('privacy', '

Privacy policy

When you buy from {site} we store your email address, name and order details so we can deliver your product, send you your download link, and email you product updates you are entitled to. Card payments are handled by Stripe; we never see or store your card number.

If you opt in to our emails we will occasionally send you tips and news. Every email has a one-click unsubscribe link.

We use minimal, privacy-friendly analytics without cookies. To access or delete your data, email {support_email}.

'), ('refund', '

Refund policy

Because our products are delivered instantly as digital downloads, all sales are final unless a money-back guarantee is shown on the product page. Where a guarantee applies, email {support_email} within the guarantee period with your order email and we will refund you in full.

'), ('contact', '

Contact

We read every message. For order help, lost download links or product questions, email {support_email}. You can also get your download link re-sent instantly from the access page.

'); INSERT IGNORE INTO settings (`key`, `value`) VALUES ('default_currency', 'GBP'), ('newsletter_default_checked', '1'), ('palette_source', 'procedural'), ('email_from_name', 'Support Team'), ('email_from_address', 'support@example.com'), ('smtp_port', '587'), ('smtp_secure', 'tls'), ('email_rate_per_minute', '60'), ('download_limit_per_day', '30'), ('stripe_allow_promo_codes', '1'), ('stripe_automatic_tax', '0'), ('stripe_invoice_creation', '0'), ('revoke_on_refund', '1'), ('indexnow_enabled', '1'); -- Default admin — username: admin / password: changeme (change immediately) INSERT IGNORE INTO users (username, password) VALUES ('admin', '$2y$10$9ko0nEvWH0MMDLdoTvgOvuuoUJnhSp2vlKkdF8vEEV2Mw196WvaAy');