rastrillo / abono Public

Download this file

1	-- abono's tables. Timestamps are DATETIME, not TEXT: modernc.org/sqlite
2	-- decodes a stored timestamp into a time.Time only when the column's
3	-- declared type is DATE, DATETIME or TIMESTAMP.
4	--
5	-- No foreign key reaches an app's identity table: `subject` is the
6	-- rastrillo session subject, and apps own their identity schema.
7
8	-- The plans an app sells. `plan` is the app's own name for it.
9	CREATE TABLE abono_offers (
10	plan TEXT NOT NULL PRIMARY KEY,
11	name TEXT NOT NULL DEFAULT '',
12	created_at DATETIME NOT NULL,
13	archived_at DATETIME
14	);
15
16	-- The Stripe prices that belong to a plan. `sold` marks a price the
17	-- app offers to new buyers; a price no longer sold stays, so that
18	-- existing subscriptions on it still resolve to their plan. A price
19	-- bills every `interval_count` `interval`s, as Stripe last said (0 until
20	-- read); v0.1 sells only every one month or one year.
21	CREATE TABLE abono_prices (
22	stripe_price_id TEXT NOT NULL PRIMARY KEY,
23	plan TEXT NOT NULL REFERENCES abono_offers(plan),
24	sold INTEGER NOT NULL DEFAULT 0,
25	interval TEXT NOT NULL DEFAULT '',
26	interval_count INTEGER NOT NULL DEFAULT 0,
27	amount INTEGER NOT NULL DEFAULT 0,
28	currency TEXT NOT NULL DEFAULT '',
29	refreshed_at DATETIME
30	);
31	CREATE INDEX abono_prices_plan ON abono_prices(plan);
32
33	-- Which Stripe customer is which subject.
34	CREATE TABLE abono_customers (
35	stripe_customer_id TEXT NOT NULL PRIMARY KEY,
36	subject TEXT NOT NULL
37	);
38	CREATE INDEX abono_customers_subject ON abono_customers(subject);
39
40	-- Stripe's subscriptions, as last reported. plan = '' means the price
41	-- was not recognised. subject = '' with `email` set means unclaimed:
42	-- paid for, not yet tied to a signed-in person. unpaid_since is the
43	-- first unpaid day abono saw (the unpaid period's start), kept while the
44	-- subscription stays past_due or unpaid and cleared when it is paid: the
45	-- 30 days of past_due count from it, never from a later period's start.
46	-- subject_pinned is set when the subject was decided explicitly (an
47	-- owner's Adopt for a subject, a reader's Claim): from then on a hint
48	-- from Checkout or Stripe's metadata never changes it.
49	CREATE TABLE abono_subscriptions (
50	stripe_subscription_id TEXT NOT NULL PRIMARY KEY,
51	stripe_customer_id TEXT NOT NULL,
52	subject TEXT NOT NULL DEFAULT '',
53	subject_pinned INTEGER NOT NULL DEFAULT 0,
54	plan TEXT NOT NULL DEFAULT '',
55	stripe_price_id TEXT NOT NULL DEFAULT '',
56	status TEXT NOT NULL,
57	current_period_start DATETIME,
58	current_period_end DATETIME,
59	cancel_at_period_end INTEGER NOT NULL DEFAULT 0,
60	unpaid_since DATETIME,
61	email TEXT NOT NULL DEFAULT '',
62	created_at DATETIME NOT NULL,
63	updated_at DATETIME NOT NULL
64	);
65	CREATE INDEX abono_subscriptions_subject ON abono_subscriptions(subject);
66	CREATE INDEX abono_subscriptions_customer ON abono_subscriptions(stripe_customer_id);
67
68	-- Plans an operator gives a subject without payment. A comp, not a
69	-- grant, so it is never confused with "grants access".
70	CREATE TABLE abono_comps (
71	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
72	subject TEXT NOT NULL,
73	plan TEXT NOT NULL,
74	status TEXT NOT NULL CHECK (status IN ('active', 'canceled')),
75	ends_at DATETIME,
76	note TEXT NOT NULL DEFAULT '',
77	created_at DATETIME NOT NULL,
78	updated_at DATETIME NOT NULL
79	);
80	CREATE INDEX abono_comps_subject ON abono_comps(subject);
81
82	-- Stripe webhook events already handled, so a redelivery is a no-op.
83	CREATE TABLE abono_events (
84	id TEXT NOT NULL PRIMARY KEY,
85	type TEXT NOT NULL,
86	received_at DATETIME NOT NULL
87	);
88