Gap of the Day

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE workspaces (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  stripe_customer_id TEXT UNIQUE, -- For billing the workspace for API CostPilot
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE workspace_members (
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member', -- 'owner', 'admin', 'member'
  PRIMARY KEY (workspace_id, user_id)
);

CREATE TABLE api_connections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  provider TEXT NOT NULL, -- e.g., 'openai', 'stripe', 'twilio'
  api_key TEXT, -- Encrypted API key or OAuth token
  api_secret TEXT, -- Encrypted API secret if needed
  oauth_access_token TEXT, -- For OAuth providers
  status TEXT NOT NULL DEFAULT 'active', -- 'active', 'inactive', 'error'
  connected_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(workspace_id, provider) -- Only one connection per provider per workspace for simplicity
);

CREATE TABLE usage_records (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  api_connection_id UUID REFERENCES api_connections(id) ON DELETE SET NULL,
  provider TEXT NOT NULL,
  event_timestamp TIMESTAMPTZ NOT NULL,
  usage_units NUMERIC NOT NULL,
  unit_type TEXT NOT NULL, -- e.g., 'tokens', 'requests', 'messages'
  estimated_cost NUMERIC NOT NULL DEFAULT 0.0,
  currency TEXT NOT NULL DEFAULT 'USD',
  project_tag TEXT, -- Optional tag for cost attribution
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_usage_records_workspace_id ON usage_records(workspace_id);
CREATE INDEX idx_usage_records_event_timestamp ON usage_records(event_timestamp);

CREATE TABLE alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  alert_type TEXT NOT NULL, -- 'spending_threshold', 'usage_spike'
  provider TEXT, -- NULL for all providers, or specific provider
  threshold_value NUMERIC NOT NULL,
  threshold_unit TEXT, -- 'USD', 'tokens', 'requests'
  time_period TEXT NOT NULL, -- 'daily', 'weekly', 'monthly'
  recipient_email TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active', -- 'active', 'paused'
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Stripe subscription details for API CostPilot itself
CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  stripe_subscription_id TEXT UNIQUE NOT NULL,
  stripe_price_id TEXT NOT NULL,
  status TEXT NOT NULL, -- 'active', 'canceled', 'trialing'
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);