Building a Japanese-First Subscription Tracker PWA (Next.js 15 + Supabase + Serwist)

I built a small PWA called subskunote to solve a very domestic problem: my family's subscriptions were a mess. Netflix was shared, Apple Music was on my kid's own account, a couple of services were under my partner's name, and one I genuinely couldn't remember who was paying for. Per month it looked small. Per year it was over ¥100,000 across 4 people, with no single place to see who pays for what.

Budgeting apps like Money Forward and Zaim already exist and they're great at full household accounting. But I only wanted subscriptions and fixed costs, grouped by person. So I built that one thing.

Here's the stack and the three parts that were actually interesting to build.

The stack

• Next.js 15 (App Router, mostly Server Components)
• Supabase (Postgres + Auth + RLS)
• Stripe (Checkout + Customer Portal, webhooks for subscription state)
• Serwist for the service worker (Web Push + offline shell)
• Vercel for hosting

Nothing exotic. The interesting parts weren't the framework choices, they were three product details: per-person quota enforcement, payment-method-aware cancel links, and getting Web Push to actually work on iOS.

Part 1: "member slots" and enforcing a freemium quota in the database

The core idea is a member slot: a person label (dad / mom / kid / shared) that subscriptions get attached to. Free users get 3 slots; Pro users get unlimited.

My first instinct was to check the count in the API route before inserting. That works until two requests race each other and you end up with 4 slots on a free plan. So I moved the rule into the database with an RLS-friendly trigger, so the count is enforced where the write actually happens.

-- Block a 4th member slot for free-plan users, atomically
create or replace function subskunote.enforce_member_slot_quota()
returns trigger
language plpgsql
security definer
set search_path = subskunote
as $$
declare
  current_count int;
  user_plan text;
begin
  select plan into user_plan
  from subskunote.profiles
  where id = new.user_id;

  if user_plan = 'pro' then
    return new; -- no limit
  end if;

  select count(*) into current_count
  from subskunote.member_slots
  where user_id = new.user_id;

  if current_count >= 3 then
    raise exception 'FREE_PLAN_SLOT_LIMIT'
      using hint = 'Upgrade to Pro for unlimited member slots';
  end if;

  return new;
end;
$$;

create trigger trg_enforce_member_slot_quota
  before insert on subskunote.member_slots
  for each row execute function subskunote.enforce_member_slot_quota();

The lesson I keep relearning: if a limit matters for billing, it belongs next to the data, not in the handler. RLS guards who can write a row; it doesn't count rows for you. The trigger fills that gap, and the race condition just disappears because the check and the insert are in the same transaction.

One gotcha: don't hardcode 3. I pull the free-tier limit from a config table so I never have the number drift between the SQL, the API, and the UI copy.

Part 2: payment-method-aware cancel links

This is the feature people seem to like most, and it's almost embarrassingly simple.

When you cancel a subscription, where you go depends on how you paid for it. Cancel Netflix that you signed up for with a card? You go to Netflix's site. Subscribed through the App Store? You go to iOS Settings, not Netflix. Google Play, carrier billing — all different.

So subskunote stores the payment method per subscription and resolves the right destination:

type PaymentMethod = "card" | "app_store" | "google_play" | "carrier";

const CANCEL_DESTINATIONS: Record<PaymentMethod, (serviceUrl: string) => string> = {
  card:        (serviceUrl) => serviceUrl,
  app_store:   () => "https://apps.apple.com/account/subscriptions",
  google_play: () => "https://play.google.com/store/account/subscriptions",
  carrier:     () => "", // resolved per carrier below
};

function resolveCancelUrl(sub: Subscription): string {
  const resolver = CANCEL_DESTINATIONS[sub.paymentMethod];
  // always fall back to the service's own page if we don't know better
  return resolver(sub.serviceCancelUrl) || sub.serviceCancelUrl;
}

Note the || sub.serviceCancelUrl fallback. Early on I had CANCEL_DESTINATIONS[method] return undefined for an unmapped method and the "Cancel" button just did nothing. A lookup against user-entered data always needs a default. Now an unknown method degrades gracefully to the service's own cancellation page instead of a dead button.

Part 3: Web Push on iOS without losing your mind

I send renewal reminders 3 days and 1 day before a subscription renews (Web Push plus an email fallback). Deliberately not on the renewal day itself — by then it's too late to act, so the reminder window is "before it renews," not "it renewed."

iOS was the hard part. A few things that cost me time:

• Web Push on iOS only works for an installed PWA (added to the Home Screen) on iOS 16.4+. In a regular Safari tab, Notification.requestPermission() won't even prompt. So the UI has to detect standalone mode and tell the user to install first, instead of silently failing.
• Subscriptions expire. You'll get 410 Gone from the push service, and if you don't delete that subscription record you'll keep trying forever.

const res = await fetch(pushSubscription.endpoint, { /* ...VAPID-signed... */ });

if (res.status === 410 || res.status === 404) {
  // subscription is dead — remove it so we stop retrying
  await supabase
    .from("push_subscriptions")
    .delete()
    .eq("endpoint", pushSubscription.endpoint);
}

Because iOS Push is unreliable by nature (and requires Home Screen install), email is a first-class fallback, not an afterthought. If push fails or the user never installed, the reminder still lands.

What I'd tell past me

• Put billing-relevant limits in the database, not the request handler.
• Every lookup against user data needs a fallback value.
• On iOS, treat Web Push as best-effort and always have an email path.
• Ship the boring 80% (CRUD + auth + Stripe) fast, and spend your real time on the 2-3 details that make the product feel different.

subskunote is live here if you want to poke at it: https://subskunote.dev-tools-hub.xyz

What do you use to keep your own subscriptions from sprawling — a spreadsheet, a budgeting app, or just vibes? Curious how other people handle the "who pays for what" problem.