-- AWS Billing MCP Server Database Schema
-- Table for storing billing records
CREATE TABLE IF NOT EXISTS billing_records (
id TEXT PRIMARY KEY,
account_id TEXT NOT NULL,
service TEXT NOT NULL,
region TEXT NOT NULL,
usage_type TEXT NOT NULL,
cost REAL NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
tags TEXT, -- JSON string of tags
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Table for storing AWS account configurations
CREATE TABLE IF NOT EXISTS aws_accounts (
id TEXT PRIMARY KEY,
account_name TEXT NOT NULL,
encrypted_credentials TEXT NOT NULL, -- Encrypted JSON of credentials
is_active BOOLEAN NOT NULL DEFAULT 1,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Table for storing authentication sessions
CREATE TABLE IF NOT EXISTS auth_sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
email TEXT NOT NULL,
access_token TEXT NOT NULL,
refresh_token TEXT,
expires_at TEXT NOT NULL,
permissions TEXT, -- JSON string of permissions array
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Table for caching metadata
CREATE TABLE IF NOT EXISTS cache_metadata (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
expires_at TEXT,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_billing_records_account_service ON billing_records(account_id, service);
CREATE INDEX IF NOT EXISTS idx_billing_records_date_range ON billing_records(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_billing_records_cost ON billing_records(cost);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_user_id ON auth_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires_at ON auth_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_cache_metadata_expires_at ON cache_metadata(expires_at);
