cgalo5758
ce3668d59e
Replace domain claims with open invoices, show monthly recurring revenue, count team organizations, and add more informative trend captions. Use standard bordered cards and add a README screenshot with reversible sample-data tooling.
329 lines
16 KiB
PL/PgSQL
329 lines
16 KiB
PL/PgSQL
-- mockshot-seed.sql — reversible sample data at deployment scale, for
|
|
-- screenshots and demos (first used for the README operator-overview shot).
|
|
-- Every row carries a marker (oidc_subject/slug prefix 'mockshot', accounts
|
|
-- metadata {"mockshot": true}, grants.description = 'mockshot', org_types/
|
|
-- products carrying a mockshot marker) so mockshot-nuke.sql can reverse it
|
|
-- exactly.
|
|
--
|
|
-- Run against a test stack (port/creds from bootstrap-stack.sh output):
|
|
-- psql -h localhost -p $POSTGRES_PORT -U member_console -d member_console \
|
|
-- -v ON_ERROR_STOP=1 -f test/mockshot-seed.sql
|
|
-- Reverse with mockshot-nuke.sql the same way. Nuke BEFORE restarting the
|
|
-- app: the boot-time entitlement materializer must never reconcile the mock
|
|
-- provisions into real pool entitlements.
|
|
--
|
|
-- Shape follows the app's own mechanics: signup mints a personal org per
|
|
-- member (backdated over ~19 months of joins) and issues a default-plan
|
|
-- grant to it; team orgs are created by long-standing members; upgrading to
|
|
-- a paid subscription ends the free default's delivery (ladder exclusion);
|
|
-- subscriptions carry priced items so monthly recurring money is real.
|
|
--
|
|
-- Targets (existing → after): People 5→2,847 (~148 joined in 30 days),
|
|
-- Team orgs 0→38, Subscriptions 0→214 (205 active + 9 trialing, MRR
|
|
-- $2,160.00), Open invoices 0→9 ($372.00 outstanding), Delivering operator
|
|
-- grants →19 with ≈2,669 signup defaults in the caption, Catalog
|
|
-- products 10→15.
|
|
BEGIN;
|
|
|
|
-- 2,842 users + persons, joined over ~19 months (i*5h + jitter)
|
|
WITH new_users AS (
|
|
INSERT INTO core.users (oidc_subject, created_at)
|
|
SELECT 'mockshot-sub-' || i,
|
|
now() - (i * 5 || ' hours')::interval - (random() * interval '55 minutes')
|
|
FROM generate_series(1, 2842) AS i
|
|
RETURNING user_id, created_at, split_part(oidc_subject, '-', 3)::int AS i
|
|
)
|
|
INSERT INTO core.persons (user_id, display_name, primary_email, primary_email_verified, created_at)
|
|
SELECT
|
|
user_id,
|
|
-- Stride 7 is coprime to 20, so consecutive members vary in both names
|
|
-- and (first, last) pairs stay unique across lcm(21,20)=420 indices.
|
|
(ARRAY['Ada','Basil','Corin','Dara','Emil','Freya','Gus','Hana','Iris','Jonas','Kira',
|
|
'Lior','Mara','Nils','Odile','Piotr','Quinn','Rosa','Sana','Theo','Uma'])[1 + (i % 21)]
|
|
|| ' ' ||
|
|
(ARRAY['Aldana','Brook','Calder','Dove','Ellis','Fen','Grange','Hale','Ingram','Jarvis',
|
|
'Keel','Lark','Marsh','North','Orr','Penn','Quill','Reyes','Sorrel','Vale'])[1 + ((i * 7) % 20)],
|
|
'mock-p' || i || '@mockshot.test',
|
|
true,
|
|
created_at + interval '1 minute'
|
|
FROM new_users;
|
|
|
|
-- One personal org per mock person, named and timed the way signup mints
|
|
-- them (two minutes after the person row).
|
|
WITH owners AS (
|
|
SELECT person_id, display_name, created_at,
|
|
row_number() OVER (ORDER BY person_id) AS rn
|
|
FROM core.persons WHERE primary_email LIKE '%@mockshot.test'
|
|
)
|
|
INSERT INTO core.organizations (name, slug, org_type, owner_person_id, created_at)
|
|
SELECT
|
|
display_name || '''s Organization',
|
|
'mockshot-' || rn,
|
|
'personal',
|
|
person_id,
|
|
created_at + interval '2 minutes'
|
|
FROM owners;
|
|
|
|
-- The team org type (design taxonomy: personal / team / enterprise —
|
|
-- design/organization/architecture.md §2). Marker in the description.
|
|
INSERT INTO core.org_types (org_type, display_name, description, is_active)
|
|
VALUES ('team', 'Team', 'mockshot', true);
|
|
|
|
-- 38 team orgs created by long-standing members (the oldest signups), each
|
|
-- 10-70 days after its owner joined.
|
|
WITH owners AS (
|
|
SELECT person_id, created_at, row_number() OVER (ORDER BY person_id DESC) AS rn
|
|
FROM core.persons WHERE primary_email LIKE '%@mockshot.test'
|
|
ORDER BY person_id DESC
|
|
LIMIT 38
|
|
)
|
|
INSERT INTO core.organizations (name, slug, org_type, owner_person_id, created_at)
|
|
SELECT
|
|
CASE WHEN rn <= 4 THEN
|
|
(ARRAY['Cedar Print Collective','Prairie Signal Cooperative',
|
|
'Harbor Works Guild','Juniper Commons'])[rn]
|
|
ELSE
|
|
(ARRAY['Cedar','Prairie','Harbor','Juniper','Granite','Meadow','Larkspur','Alder'])[1 + ((rn - 5) % 8)]
|
|
|| ' ' ||
|
|
(ARRAY['Collective','Cooperative','Commons','Works','Guild','Studio','Assembly','Exchange'])[1 + ((rn - 5) / 8)]
|
|
END,
|
|
'mockshot-t' || rn,
|
|
'team',
|
|
person_id,
|
|
created_at + interval '10 days' + (random() * interval '60 days')
|
|
FROM owners;
|
|
|
|
-- Billing accounts: every team + the 176 most recent personal orgs
|
|
INSERT INTO core.accounts (org_id, name, metadata)
|
|
SELECT org_id, name || ' billing', '{"mockshot": true}'::jsonb
|
|
FROM core.organizations WHERE slug LIKE 'mockshot-%'
|
|
ORDER BY (slug LIKE 'mockshot-t%') DESC, created_at DESC
|
|
LIMIT 214;
|
|
|
|
-- 214 live subscriptions: teams first (all active), then individuals;
|
|
-- the last 9 are trialing.
|
|
WITH accts AS (
|
|
SELECT a.billing_account_id,
|
|
row_number() OVER (ORDER BY (o.slug LIKE 'mockshot-t%') DESC, a.billing_account_id) AS rn
|
|
FROM core.accounts a
|
|
JOIN core.organizations o ON o.org_id = a.org_id
|
|
WHERE a.metadata @> '{"mockshot": true}'::jsonb
|
|
LIMIT 214
|
|
)
|
|
INSERT INTO core.subscriptions (billing_account_id, status, current_period_start, current_period_end)
|
|
SELECT billing_account_id,
|
|
CASE WHEN rn <= 205 THEN 'active' ELSE 'trialing' END,
|
|
date_trunc('month', now()),
|
|
date_trunc('month', now()) + interval '1 month'
|
|
FROM accts;
|
|
|
|
-- Five mock products with deployment-plausible names (before the prices,
|
|
-- items, and grants that reference them). Published so grant events print
|
|
-- them; metadata carries the nuke marker.
|
|
INSERT INTO core.products (name, description, display_category, metadata, lifecycle_status)
|
|
VALUES
|
|
('Starter Hosting', 'Default plan for new members', NULL, '{"mockshot": true}'::jsonb, 'published'),
|
|
('Standard Hosting', 'Paid hosting tier', NULL, '{"mockshot": true}'::jsonb, 'published'),
|
|
('Sustainer Hosting','Supporter hosting tier', NULL, '{"mockshot": true}'::jsonb, 'published'),
|
|
('Custom Domain', 'Serve a site on your own domain', 'addon','{"mockshot": true}'::jsonb, 'published'),
|
|
('Storage Boost', 'Additional media storage', 'addon', '{"mockshot": true}'::jsonb, 'published');
|
|
|
|
-- Prices for the paid tiers: Standard $9/mo (default), Sustainer $18/mo
|
|
-- (default) with an $180/yr alternative — the yearly price exercises the
|
|
-- one-twelfth normalization on the overview's money headline.
|
|
WITH plans AS (
|
|
SELECT product_id, name FROM core.products
|
|
WHERE metadata @> '{"mockshot": true}'::jsonb
|
|
AND name IN ('Standard Hosting', 'Sustainer Hosting')
|
|
)
|
|
INSERT INTO core.prices (product_id, currency, unit_amount, recurring_interval, is_default)
|
|
SELECT product_id, 'usd',
|
|
CASE name WHEN 'Standard Hosting' THEN 900 ELSE 1800 END,
|
|
'month', true
|
|
FROM plans
|
|
UNION ALL
|
|
SELECT product_id, 'usd', 18000, 'year', false
|
|
FROM plans WHERE name = 'Sustainer Hosting';
|
|
|
|
-- Subscription items: teams ride Sustainer (every 4th on the yearly
|
|
-- price), individuals ride Standard monthly.
|
|
INSERT INTO core.subscription_items (subscription_id, product_id, price_id, quantity)
|
|
SELECT s.subscription_id, pr.product_id, pr.price_id, 1
|
|
FROM core.subscriptions s
|
|
JOIN core.accounts a ON a.billing_account_id = s.billing_account_id
|
|
AND a.metadata @> '{"mockshot": true}'::jsonb
|
|
JOIN core.organizations o ON o.org_id = a.org_id
|
|
JOIN LATERAL (
|
|
SELECT p.price_id, p.product_id
|
|
FROM core.prices p
|
|
JOIN core.products pd ON pd.product_id = p.product_id
|
|
WHERE pd.metadata @> '{"mockshot": true}'::jsonb
|
|
AND CASE
|
|
WHEN o.slug LIKE 'mockshot-t%' THEN
|
|
pd.name = 'Sustainer Hosting' AND p.recurring_interval =
|
|
CASE WHEN split_part(o.slug, '-t', 2)::int % 4 = 0 THEN 'year' ELSE 'month' END
|
|
ELSE pd.name = 'Standard Hosting' AND p.recurring_interval = 'month'
|
|
END
|
|
LIMIT 1
|
|
) pr ON true;
|
|
|
|
-- Auto-provisioning: every live mock subscription delivers its plan, so a
|
|
-- paying org is never provision-less after its free default ends below.
|
|
INSERT INTO core.pool_provisions (pool_id, subscription_id, status, entitlement_set_id, product_id)
|
|
SELECT t.pool_id, s.subscription_id, 'active', t.entitlement_set_id, si.product_id
|
|
FROM core.subscriptions s
|
|
JOIN core.accounts a ON a.billing_account_id = s.billing_account_id
|
|
AND a.metadata @> '{"mockshot": true}'::jsonb
|
|
JOIN core.subscription_items si ON si.subscription_id = s.subscription_id
|
|
CROSS JOIN (SELECT pool_id, entitlement_set_id FROM core.pool_provisions
|
|
WHERE status = 'active' AND grant_id IS NOT NULL LIMIT 1) t;
|
|
|
|
-- 9 open invoices, staggered over the last two days for the timeline
|
|
WITH accts AS (
|
|
SELECT billing_account_id, row_number() OVER (ORDER BY billing_account_id) AS rn
|
|
FROM core.accounts WHERE metadata @> '{"mockshot": true}'::jsonb
|
|
LIMIT 9
|
|
)
|
|
INSERT INTO core.invoices (billing_account_id, status, amount_due, amount_paid, currency,
|
|
period_start, period_end, due_date, created_at)
|
|
SELECT billing_account_id, 'open',
|
|
(ARRAY[2400, 4800, 1200, 9600, 2400, 7200, 4800, 1200, 3600])[rn], 0, 'usd',
|
|
now() - interval '1 month', now(), now() + interval '14 days',
|
|
now() - (random() * interval '48 hours')
|
|
FROM accts;
|
|
|
|
-- 6 recently paid invoices, each settled by a succeeded payment two
|
|
-- minutes after issue — the timeline's payment_received rows.
|
|
WITH accts AS (
|
|
SELECT billing_account_id, row_number() OVER (ORDER BY billing_account_id DESC) AS rn
|
|
FROM core.accounts WHERE metadata @> '{"mockshot": true}'::jsonb
|
|
LIMIT 6
|
|
),
|
|
timed AS (
|
|
SELECT billing_account_id, rn,
|
|
(ARRAY[2400, 4800, 2400, 9600, 4800, 2400])[rn] AS cents,
|
|
now() - (random() * interval '68 hours') AS ts
|
|
FROM accts
|
|
),
|
|
paid AS (
|
|
INSERT INTO core.invoices (billing_account_id, status, amount_due, amount_paid, currency,
|
|
period_start, period_end, due_date, paid_at, created_at)
|
|
SELECT billing_account_id, 'paid', cents, cents, 'usd',
|
|
now() - interval '1 month', now(), now() + interval '14 days',
|
|
ts + interval '2 minutes', ts
|
|
FROM timed
|
|
RETURNING invoice_id, billing_account_id, amount_paid, created_at
|
|
)
|
|
INSERT INTO core.payments (invoice_id, billing_account_id, amount, currency, status, created_at)
|
|
SELECT invoice_id, billing_account_id, amount_paid, 'usd', 'succeeded', created_at + interval '2 minutes'
|
|
FROM paid;
|
|
|
|
-- The signup default-plan grant for every mock org, stamped minutes after
|
|
-- the org itself (system-authored: reason 'default' ⟺ no granting person,
|
|
-- per chk_grants_default_iff_system_authored). Each gets its provision.
|
|
WITH tmpl AS (
|
|
SELECT pp.pool_id, pp.entitlement_set_id
|
|
FROM core.pool_provisions pp
|
|
WHERE pp.status = 'active' AND pp.grant_id IS NOT NULL
|
|
LIMIT 1
|
|
),
|
|
default_plan AS (
|
|
SELECT product_id FROM core.products
|
|
WHERE metadata @> '{"mockshot": true}'::jsonb AND name = 'Starter Hosting'
|
|
LIMIT 1
|
|
),
|
|
new_grants AS (
|
|
INSERT INTO core.grants (product_id, granted_to_org_id, granted_by_person_id,
|
|
grant_reason, description, valid_from, created_at)
|
|
SELECT dp.product_id, o.org_id, NULL, 'default', 'mockshot',
|
|
o.created_at + interval '2 minutes',
|
|
o.created_at + interval '2 minutes'
|
|
FROM core.organizations o
|
|
CROSS JOIN default_plan dp
|
|
WHERE o.slug LIKE 'mockshot-%'
|
|
RETURNING grant_id, product_id, created_at
|
|
)
|
|
INSERT INTO core.pool_provisions (pool_id, grant_id, status, entitlement_set_id, product_id, activated_at)
|
|
SELECT t.pool_id, g.grant_id, 'active', t.entitlement_set_id, g.product_id, g.created_at
|
|
FROM new_grants g CROSS JOIN tmpl t;
|
|
|
|
-- Eighteen operator-issued extras (upgrades, promos) over the last ~3 days.
|
|
WITH tmpl AS (
|
|
SELECT pp.pool_id, pp.entitlement_set_id
|
|
FROM core.pool_provisions pp
|
|
WHERE pp.status = 'active' AND pp.grant_id IS NOT NULL
|
|
LIMIT 1
|
|
),
|
|
operator_person AS (
|
|
SELECT person_id FROM core.persons
|
|
WHERE primary_email NOT LIKE '%@mockshot.test'
|
|
ORDER BY (display_name ILIKE 'alice%') DESC, created_at
|
|
LIMIT 1
|
|
),
|
|
products AS (
|
|
SELECT product_id, row_number() OVER (ORDER BY name) AS rn
|
|
FROM core.products
|
|
WHERE metadata @> '{"mockshot": true}'::jsonb AND name <> 'Starter Hosting'
|
|
),
|
|
targets AS (
|
|
SELECT org_id, row_number() OVER (ORDER BY created_at DESC) AS rn,
|
|
now() - (random() * interval '70 hours') - interval '20 minutes' AS ts
|
|
FROM core.organizations WHERE slug LIKE 'mockshot-%'
|
|
LIMIT 18
|
|
),
|
|
extra_grants AS (
|
|
INSERT INTO core.grants (product_id, granted_to_org_id, granted_by_person_id,
|
|
grant_reason, description, valid_from, created_at)
|
|
SELECT p.product_id, tg.org_id, op.person_id,
|
|
CASE WHEN tg.rn % 2 = 0 THEN 'manual' ELSE 'promotional' END,
|
|
'mockshot',
|
|
tg.ts, tg.ts
|
|
FROM targets tg
|
|
JOIN products p ON p.rn = 1 + (tg.rn % (SELECT count(*) FROM products))::int
|
|
CROSS JOIN operator_person op
|
|
RETURNING grant_id, product_id, created_at
|
|
)
|
|
INSERT INTO core.pool_provisions (pool_id, grant_id, status, entitlement_set_id, product_id, activated_at)
|
|
SELECT t.pool_id, g.grant_id, 'active', t.entitlement_set_id, g.product_id, g.created_at
|
|
FROM extra_grants g CROSS JOIN tmpl t;
|
|
|
|
-- Upgrading ends the free default's delivery: the ladder table's GiST
|
|
-- exclusion admits one active provision per pool per ladder, so each
|
|
-- subscribed org's default-grant provision ended when its paid subscription
|
|
-- provisioned. The grant itself stays 'active' in the ledger — exactly the
|
|
-- ledger-vs-delivery distinction the overview tile is built around.
|
|
UPDATE core.pool_provisions pp
|
|
SET status = 'ended',
|
|
ended_at = pp.activated_at + interval '40 minutes'
|
|
FROM core.grants g
|
|
JOIN core.accounts a ON a.org_id = g.granted_to_org_id
|
|
JOIN core.subscriptions s ON s.billing_account_id = a.billing_account_id
|
|
WHERE pp.grant_id = g.grant_id
|
|
AND g.description = 'mockshot'
|
|
AND g.grant_reason = 'default'
|
|
AND a.metadata @> '{"mockshot": true}'::jsonb;
|
|
|
|
COMMIT;
|
|
|
|
-- Post-seed tallies
|
|
SELECT (SELECT count(*) FROM core.persons WHERE status = 'active') AS people,
|
|
(SELECT count(*) FROM core.persons WHERE status = 'active'
|
|
AND created_at > now() - interval '30 days') AS joined_30d,
|
|
(SELECT count(*) FROM core.organizations o JOIN core.org_types t ON t.org_type = o.org_type
|
|
WHERE o.status = 'active' AND o.org_type <> 'personal' AND NOT t.is_reserved) AS team_orgs,
|
|
(SELECT count(*) FROM core.subscriptions WHERE status = 'active') AS active_subs,
|
|
(SELECT count(*) FROM core.subscriptions WHERE status = 'trialing') AS trialing_subs,
|
|
(SELECT sum(CASE p.recurring_interval WHEN 'month' THEN p.unit_amount * si.quantity
|
|
WHEN 'year' THEN (p.unit_amount * si.quantity) / 12 ELSE 0 END)
|
|
FROM core.subscriptions s
|
|
JOIN core.subscription_items si ON si.subscription_id = s.subscription_id
|
|
JOIN core.prices p ON p.price_id = si.price_id
|
|
WHERE s.status = 'active') AS mrr_cents,
|
|
(SELECT count(*) FROM core.invoices WHERE status = 'open') AS open_invoices,
|
|
(SELECT count(DISTINCT g.grant_id) FROM core.grants g
|
|
JOIN core.pool_provisions pp ON pp.grant_id = g.grant_id
|
|
WHERE pp.status = 'active' AND g.grant_reason <> 'default') AS operator_grants,
|
|
(SELECT count(DISTINCT g.grant_id) FROM core.grants g
|
|
JOIN core.pool_provisions pp ON pp.grant_id = g.grant_id
|
|
WHERE pp.status = 'active' AND g.grant_reason = 'default') AS default_grants;
|