How to build a subscription tracker with Claude

Set up a subscription tracker for me in databasedmcp, in one go.

1. Create a database called subscriptions, described as "Subscription tracker: what I pay for, how often and when it renews, kept by Claude. No bank connection."
2. Create two tables:
   - subscriptions: id, name (required), category, price_cents (required, in cents), currency (defaults to EUR), cycle (weekly, monthly or yearly, required), status (trial, active or cancelled, defaults to active), started_on (date, defaults to today), next_renewal (date of the next charge), trial_ends (date, for trials), cancelled_on (date), notes.
   - price_changes: id, subscription_id (references subscriptions, required), old_cents (required), new_cents (required), changed_on (date, defaults to today).
3. Write the database a guide that says what each table is for, that prices are in cents, that next_renewal moves on by one cycle when a renewal passes, that a price change is logged in price_changes and the subscription's price updated in the same session, and the queries for "monthly cost of active subscriptions, with weekly and yearly plans spread over the month" and "what renews in the next 30 days".

From then on: when I mention signing up, add the subscription with its price, cycle and next renewal date. When I start a trial, add it as a trial with the date it ends. When a price changes, log the old and new price and update the subscription. When I cancel, mark it cancelled with today's date and clear the next renewal. Show me the tables when you're finished.

CREATE TABLE subscriptions (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT,
  price_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'EUR',
  cycle TEXT NOT NULL CHECK (cycle IN ('weekly', 'monthly', 'yearly')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('trial', 'active', 'cancelled')),
  started_on TEXT NOT NULL DEFAULT (date('now')),
  next_renewal TEXT,
  trial_ends TEXT,
  cancelled_on TEXT,
  notes TEXT
);

CREATE TABLE price_changes (
  id INTEGER PRIMARY KEY,
  subscription_id INTEGER NOT NULL REFERENCES subscriptions(id),
  old_cents INTEGER NOT NULL,
  new_cents INTEGER NOT NULL,
  changed_on TEXT NOT NULL DEFAULT (date('now'))
);

# subscriptions: what I pay for

- **subscriptions**: one row per subscription. Prices in cents. cycle: weekly, monthly, yearly. status: trial, active, cancelled. `next_renewal` is the next charge date; `trial_ends` for trials; clear it when a trial becomes active. When a renewal passes, move `next_renewal` on by one cycle.
- **price_changes**: old and new price whenever a price changes. Update `subscriptions.price_cents` in the same session.

Monthly cost: `SELECT round(sum(CASE cycle WHEN 'weekly' THEN price_cents * 52 / 12.0 WHEN 'monthly' THEN price_cents WHEN 'yearly' THEN price_cents / 12.0 END) / 100.0, 2) AS per_month FROM subscriptions WHERE status = 'active'`

Renewing in 30 days: `SELECT name, price_cents / 100.0 AS price, next_renewal FROM subscriptions WHERE status = 'active' AND next_renewal <= date('now', '+30 days') ORDER BY next_renewal`

INSERT INTO subscriptions (name, category, price_cents, cycle, next_renewal) VALUES (?, 'video', 1299, 'monthly', date('now', 'start of month', '+1 month', '+2 days')), (?, 'news', 8900, 'yearly', date('now', 'start of year', '+1 year', '+2 months')), (?, 'storage', 299, 'monthly', date('now', 'start of month', '+19 days'))
-- params: ["Streamwell","Northpage","Cloudnest"]

INSERT INTO subscriptions (name, category, price_cents, cycle, status, trial_ends, next_renewal) VALUES (?, ?, ?, 'monthly', 'trial', date('now', '+14 days'), date('now', '+14 days'))
-- params: ["Tunebox","music",1099]

INSERT INTO price_changes (subscription_id, old_cents, new_cents) SELECT id, price_cents, ? FROM subscriptions WHERE id = ?
-- params: [1499,1]

UPDATE subscriptions SET price_cents = ? WHERE id = ?
-- params: [1499,1]

UPDATE subscriptions SET status = 'cancelled', cancelled_on = date('now'), next_renewal = NULL WHERE id = ?
-- params: [3]

SELECT category, round(sum(CASE cycle WHEN 'weekly' THEN price_cents * 52 / 12.0 WHEN 'monthly' THEN price_cents WHEN 'yearly' THEN price_cents / 12.0 END) / 100.0, 2) AS per_month FROM subscriptions WHERE status = 'active' GROUP BY category ORDER BY per_month DESC

SELECT name, status, price_cents / 100.0 AS price, coalesce(trial_ends, next_renewal) AS charge_on FROM subscriptions WHERE status IN ('active', 'trial') AND coalesce(trial_ends, next_renewal) <= date('now', '+30 days') ORDER BY charge_on

SELECT s.name, pc.old_cents / 100.0 AS was, pc.new_cents / 100.0 AS now, pc.changed_on FROM price_changes pc JOIN subscriptions s ON s.id = pc.subscription_id WHERE pc.changed_on >= date('now', 'start of year') ORDER BY pc.changed_on