-- Subscription Tracker Database Schema
-- MySQL 8.0+
CREATE DATABASE IF NOT EXISTS subscription_tracker;
USE subscription_tracker;
-- Subscriptions table
CREATE TABLE IF NOT EXISTS subscriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
service VARCHAR(255) NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
currency VARCHAR(10) DEFAULT 'USD',
renewal_date DATE NOT NULL,
plan_name VARCHAR(255),
cycle VARCHAR(50),
status VARCHAR(50) DEFAULT 'active',
email_id VARCHAR(255),
email_date DATETIME,
category VARCHAR(100),
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_renewal_date (renewal_date),
INDEX idx_service (service),
INDEX idx_status (status),
INDEX idx_email_id (email_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Renewal alerts table
CREATE TABLE IF NOT EXISTS renewal_alerts (
id INT AUTO_INCREMENT PRIMARY KEY,
subscription_id INT NOT NULL,
alert_type VARCHAR(50) NOT NULL,
alert_sent BOOLEAN DEFAULT FALSE,
alert_sent_at TIMESTAMP NULL,
scheduled_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
INDEX idx_subscription_id (subscription_id),
INDEX idx_alert_sent (alert_sent),
INDEX idx_scheduled_date (scheduled_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Price history table (for anomaly detection)
CREATE TABLE IF NOT EXISTS price_history (
id INT AUTO_INCREMENT PRIMARY KEY,
subscription_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
currency VARCHAR(10) DEFAULT 'USD',
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
INDEX idx_subscription_id (subscription_id),
INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Sample data for testing
INSERT INTO subscriptions (service, amount, renewal_date, plan_name, cycle, email_id) VALUES
('Netflix', 15.99, '2024-03-15', 'Standard', 'monthly', 'sample123'),
('Spotify', 9.99, '2024-03-20', 'Individual', 'monthly', 'sample456'),
('AWS', 45.00, '2024-03-25', 'Basic', 'monthly', 'sample789');
