Mantle Open the web app

The Together service: backups, restores and alerts

For the maintainer. Releasing and hosting says which Supabase project each channel reaches; this page is what to do when one of them is lost, hurt or full. It says what production's backups can bring back and how to try that without touching production, what is watched and where a failure shows, and which of Supabase's limits Mantle can reach. Dashboard steps are the owner's: Mantle's own project is ypwzfkkalpbkrhoegszw and staging is gmrvcajdizklgbxpstmz. Nothing in the repository holds a token for either, and none of the commands here change a hosted project.

Supabase's pages change, so each fact below names the page it came from. Check the ones marked (your plan) in the dashboard, since they depend on the plan production is on.

What the backups keep

What Supabase says Source
Daily backups Pro keeps 7 days, Team 14, Enterprise up to 30. The page lists none for Free, and tells Free projects to export their data themselves (supabase db dump). (your plan) Database backups
Point-in-time recovery A paid add-on for Pro, Team and Enterprise: 7, 14 or 28 days kept, to the second, at most two minutes of changes lost. Turning it on stops the daily backups. (your plan: is it on?) Database backups
Downloading one Physical backups (every project on Postgres 15.8.1.079 or newer) cannot be downloaded: the dashboard's download is for older projects, whose backups are logical. A logical one is made by hand with supabase db dump. Database backups, Restore a downloaded backup
Restoring in place Replaces the whole database with the backup's. The project is unavailable while it runs, for longer the larger the database is. Database backups
Restoring to a new project A physical backup or a point in time, into a new project of its own (paid plans with physical backups; it is billed as a project). The original is left as it is. Restore to a new project

How to find out what production has. In the dashboard, the organisation's Billing page says the plan; Database → Backups lists the daily backups it has (and a Point in time tab if the add-on is on), with their dates. Write what is there into this table once, with the date, so the next person need not look:

Fact, as of Value
Plan
Daily backups, kept for
Point in time recovery on, kept for
Oldest backup listed
Size of the database (Database → Reports)

If the plan has no backups. Then the only backup is the one made by hand (supabase db dump, below, "A logical backup by hand"), and it is only as new as the last time someone made it. The next plan is Pro, with 7 days of daily backups, and point-in-time recovery on top of it for what is lost in between; its price is on supabase.com/pricing. Which to pay for is the owner's decision. What a day of lost changes costs is a day of a person's history on the devices that did not sync it; the service also keeps 30 days of each record's earlier versions (see "History is not a backup" below), which is the recovery for a push gone wrong and costs nothing.

What a restore brings back, and what it does not

What Mantle keeps is in the database and nowhere else (no Storage buckets, no Edge Functions and no Vault: supabase/ has only migrations, the sign-in settings and the emails), so a restore of the database is nearly all of it.

Thing Comes back?
Accounts (auth.users) and their sessions Yes, from a restore to a new project or in place; in a manual load of a data dump when the dump has the auth schema (check accounts in the counts below). A device whose session is gone signs in again with the account's email.
Profiles, records, shared lists, keyrings, devices, friends Yes: they are tables of public. Records come back sealed, byte for byte.
The record history (records_history, list_records_history, from #345) Yes, with the rest of the database: it is a table. A restore to a time takes the history as it was then, so the 30 days it holds end at that time.
Row-level security, functions, triggers, grants Yes (part of the database).
Realtime's publication (supabase_realtime, which has news) A publication is part of the database, so a restore of it should carry it, but Supabase's clone page lists Realtime settings as not copied (Restore to a new project). Check it in the drill (step 2.4 and the table below). A manual load into an empty project: re-enable it in Database → Publications (Restore a downloaded backup).
Sign-in settings (supabase/config.toml: the code's length, the rate limits, the before_user_created hook's setting) and the emails' templates No. They are the project's settings, not its database. For production, go run ./tools/mk backend stable puts them back (it needs SUPABASE_ACCESS_TOKEN). A new project has Supabase's defaults.
The mail service's password (Resend, MANTLE_SMTP_PASSWORD) No. go run ./tools/mk backend stable -mail sets it again.
API keys (the publishable and secret ones) No: a new project has keys of its own. The project's address and publishable key are built into every app (internal/together/service.go), so a restore to a new project, which has an address of its own, would need a release before an app reaches it; restoring in place keeps both.
Custom role passwords No (Database backups). Mantle has none.
Edge Functions, secrets, Storage objects There are none. (Supabase does not restore them: Restore a downloaded backup.)

Restore in place only when the project is already lost. The restore to a new project leaves the old one to compare with, and it is the one a drill uses.

A trial restore, without touching production

Pick one of the two ways below, or do both. Neither writes to production: the first is the dashboard's Restore to a new project, and the second reads production with supabase db dump.

1. Make a canary first

A canary is an account made for this, so there is something known to look for. It is made at production the way anyone's is, and kept:

  1. In a build that reaches production, sign in with an address made for it (an alias that is yours, not a person's), make the account, keep the recovery code, and in its profile add a few titles to a list and rate one.
  2. Note the time. In the dashboard's SQL editor, run the query that go run ./tools/mk service counts -sql prints (it only reads) for the figures of the day, and select id, created_at from auth.users where email = '<the canary's address>';.
  3. A backup from before this has no canary, and one from after has it: which one a restore gave is read from that.

2. Restore into a new project (the way Supabase's own drill goes)

Needs a plan with physical backups (Restore to a new project). It costs a project's price for the days it exists, which the dashboard shows before it starts.

  1. In production's dashboard: Database → Backups → Restore to a new project. Pick the newest backup (or a point in time, if PITR is on), start it, and write down the time it takes, with the database's size.
  2. When the new project is up, run the counts query in its SQL editor, and in production's. The two need not be equal (production has gone on since), but accounts, profiles and shared lists must be near, and none may be zero.
  3. Check the schema: select count(*) from supabase_migrations.schema_migrations; is the number of files in supabase/migrations when production is up to date (go run ./tools/mk backend stable -check says whether it is).
  4. Check that Realtime still has its table: select tablename from pg_publication_tables where pubname = 'supabase_realtime'; must list news; if not, the apps' live updates would stop quietly: add it in Database → Publications.
  5. Delete the new project (Settings → General → Delete project) when done. Nothing in the repository or the apps names it, so nothing else changes.

3. Load a backup into this computer's service

This one needs no paid project, and it opens a restored account in the app, which the new project cannot do until its sign-in settings and mail are set. It needs a container engine (as mk supabase does) and the database's password: use the one the project already has (Mantle's own code uses none: mk backend goes through the Management API with a token). Reset it (Database Settings → Reset database password) only if it is lost: that replaces the old one for anything else that holds it.

A logical backup by hand, which is also the backup to keep if the plan has no daily ones (Backup and restore using the CLI):

$env:DB = "postgresql://postgres.ypwzfkkalpbkrhoegszw:<password>@<the pooler host in Connect>:5432/postgres"
go run ./tools/mk supabase db dump --remote $env:DB -f out/data.sql --use-copy --data-only -x "storage.buckets_vectors" -x "storage.vector_indexes"

The flag is --remote in the page quoted and --db-url in older CLIs: go run ./tools/mk supabase db dump --help says. out/ is not kept in the repository. The pooler's session address (port 5432) is the one for a network without IPv6; the Connect panel says which. The data is the records, sealed; keep the file where the machine's own backups go. What a data-only dump holds of auth is unverified for the pinned CLI (2.122.0): the page quoted does not say, and the CLI was not run here. Check go run ./tools/mk supabase db dump --help for what --schema and the defaults cover, and the accounts line of the counts after the load. If it is zero, dump again with --schema public,auth; if that load then stops on auth.schema_migrations (the local service has its own rows), add -x auth.schema_migrations to the dump.

Then, here, in this order:

go run ./tools/mk supabase                    # start this computer's service, if it is not up
go run ./tools/mk supabase db reset           # its schema, from the migrations, empty
go run ./tools/mk service restore out/data.sql
go run ./tools/mk service counts

mk service restore runs the file in one transaction with triggers off, as Supabase's own restore does, and refuses a service that already has accounts or profiles in it: a restore never mixes with what is there. mk service counts prints a line for each of accounts, devices, profiles, records (live, and deletions kept), the history's rows, shared lists and their records.

The tests cover the helper's arguments and queries; the load has not been run against a running service. The first drill is its first run: read its output, and if the load fails on a statement, say so in an issue for this page.

What to check

Check Passes when
Counts Accounts, profiles and shared lists equal production's at the time of the backup (accounts and profiles seldom change in a day; records may be more or fewer). Records (live) is not zero.
The canary select id from auth.users where email = '<the canary>' finds it in a backup made after it was made, and does not in one before.
The history record history rows is not zero if a record was changed in the 30 days before the backup.
Signing in (this computer's service only) With MANTLE_TOGETHER=dev (a build from go run ./tools/mk web -serve, or the desktop's, and a data folder of its own), sign in as the canary. The code arrives at the local mail service, 127.0.0.1:54324, not at the address. Then Enter code, with the canary's recovery code: the profile and the list come back, with their titles. That proves a restore was of use: sealed records opened with the account's key.
Realtime select tablename from pg_publication_tables where pubname = 'supabase_realtime'; lists news.
Timed The time of step 2.1 is written down, with the size of the database.

How end-to-end encryption changes a restore

The service holds only what devices sealed (see Mantle Together), and a restore changes none of it.

  • A restored record is the record as it was. The body is sealed with the profile's key, and the stamp that orders versions is inside it. Nothing is read by restoring, which is why the checks above are of counts and of one account that a person opens.
  • A device is the other half of the key. The account's key is kept on its devices and, sealed with the recovery code, in keyrings. A restore does not give a device a key it lacks; a device that has it reads the restored records. A device that was signed out, or a new one, needs another device or the recovery code, exactly as before.
  • A restore to a time before the account's recovery code was replaced (Settings → Account → New code) brings back the older sealing of the key: the old code opens it and the new one, which the person has, does not. The key itself is the same, so devices that have it are not hurt. Tell whoever replaced the code, and replace it again after.
  • A restore to a time before a shared list's key changed (the owner removed a member, which re-seals the list under a new key, rekey_list) brings back records sealed with the old key and the member rows that go with them. So the removed member is a member again until the owner removes them again, and a device that learned the new key since may not open the older records. The service is consistent with itself at that time; the devices are the ones that moved on. A list's owner is to look at its members after a restore.
  • A restore to a time before a profile existed brings back a service without it: a device that has the profile has records the service does not. Read the next section.

Devices that are ahead of the restored service

A device keeps, for each book (a profile's, a list's, the household's), the last push it pulled (st.Seq) and what it has synced (together.json), and pulls "after the last push I saw". A restored service numbers its pushes from the backup's last, below what the devices that synced since have seen, so a device that was ahead would pull nothing pushed under a lower number. Since #360 the app finds this itself (foundBehind, in internal/core/sync.go; Internals has the detail). A device on such a build that is told a book's number below its own takes the service for restored, reads the book whole again, and sends back what it has that the service lacks. It is told the numbers at each whole round (every 30 seconds to 5 minutes while Mantle runs, and at once when another device pushes) and in each push's answer. It finds the restore only while the service's number is still below its own: a device ahead of the backup by n pushes of a book no longer sees it once n pushes of that book have been made since the restore. The first device to find it makes one as it sends back what was lost. The tests run it against the fake service (internal/core/syncbehind_test.go); try it in the drill on this computer's service before relying on it.

What the devices do:

  • What was written after the restored time comes back from the devices. A record a device knows and the restored service lacks is not read as deleted. Once the device has read the book whole, it pushes each record it has as it synced it, at its stamp, and each deletion it knows of a record the service has nothing of. The service then has them again, and a device signed in afterwards gets them. Of a shared list, each member's devices send what that member wrote. A change made on two devices is decided by the stamps, as any change is.
  • A mass deletion that the restore undid is held, not made again. A deletion made after the restored time, of a record the service has from before it, goes up again from the device that has it, by the stamps. When there are as many as the device's brake of #322 holds (100 or more and more than the profile has left, or 250 or more), they are held instead, since a service put back to before a mass deletion may have been put back for that. Each device that had the deletion shows "Deletions held back" in Settings, Account. The first answer goes for the household:
    • Bring them back puts the records back as of when it was pressed, so the other devices take them and stop asking, and what anyone changed after the press still wins. A list that was deleted comes back under a new id, as Restore synced data brings one back, with what a device that still showed it did to it meanwhile. A change made to the old list after that, on a device that has not synced since, is lost (the old list goes when it syncs); the person makes it again on the list brought back.
    • Delete them everywhere deletes them again.
  • Not in the restored account, and not sent back: a profile joined to the account after the restored time, and a list first shared after it. The service has no such profile or list, so each device keeps it, unsynced: the profile as This device only, the list as the profile's own. Join the profile again on one device (Sync, As a new profile); on the others, Add here brings it, and the copy kept there can be removed. Share the list again.
  • A record made and deleted after the restored time can come back. Suppose it was deleted on one device and another still had it. The device that still has it sends it back. Only if the device that deleted it also finds the restore does its deletion go up again and win by the stamps. A device on a build before this one never sends the deletion again, so the record stays.

What the app does not see:

  • A device that does not sync before the others' pushes reach its number, as above. Typically it was away, or a device ahead by only a few pushes was opened after the first device that found the restore. Closing this needs the service to say it was restored: #384.
  • Any build before #360 (0.3.1 and earlier, and the betas made before it). It never finds a restore.

Either misses what was pushed under the numbers it had passed, until that is changed again. Nothing is lost at the service by it.

What to do

  1. Before the restore, if there is time: have the household on a build with #360. Note the numbers of the service as it is now, for step 4: select id, seq from public.profiles; and select id, seq from public.lists; (the original is still there for a restore to a new project, and readable before an in-place one).
  2. Restore, and do not raise the numbers. Raised, they are no longer below the devices', so the devices on this build do not find the restore, and what was written after the restored time stays on them.
  3. Have every device of the household opened soon, online and signed in. Each one on this build finds the restore at its first whole round, reads its books whole and sends back what was lost; Settings, Account may say "Deletions held back". The sooner each is opened, the fewer pushes since the restore can hide it.
  4. Only if devices on a build before #360 are in use and cannot be updated: once every device on this build has synced since the restore (step 3), raise each book's number to the one noted in step 1: update public.profiles set seq = greatest(seq, <the noted number>) where id = '<id>';, and the same for public.lists. From then on the older builds pull what is pushed. It costs this: what was pushed between the restore and the raise is below their numbers, and they never pull it; and a device on this build that had not synced by then no longer finds the restore at all. Without the raise, an older build pulls nothing of a book until the others' pushes pass its number.
  5. A device that missed it (opened late, or updated from an older build after step 4): what it missed cannot be listed; compare with another device, or use this way out.
    • Let it sync once, so what it changed goes up.
    • Then, in Settings, Account, Profiles, for each of its synced profiles: press Stop syncing, then Sync, The same person as …, Sync as one.
    • That reads the profile's book whole. Where both have something, the account's version wins; a copy of the state from before is kept as mantle.json.before-joining-a-profile. What the account lacks goes up.
    • A profile in shared lists can do it too: each list it is in is read whole again and taken as the service has it, and nothing of this device's copy goes up, so what the service lost of a list is not sent back by it. Nor is what the household shares (plugins and their settings), which it does not read again. Edit what is missing of those again.

History is not a backup

The 30 days of record history (history_at, #294) put one profile's records back to a time in them, with the service running. It is the recovery for a push gone wrong. A backup is for the loss of the database: a bad migration, a deleted project, Supabase's own fault. They do not replace each other.

What is watched

What How Where a failure is seen
Production's and staging's auth health, the REST API without a key and with the public one (the database answering), and app.mantlevid.com. Every six hours, this is the uptime monitor: no outside one is needed. .forgejo/workflows/service.yml, every six hours and by hand: go run ./tools/mk service check The repository's Actions list, the workflow "Service": a red run named "Services are up", with lines such as ::error::stable: database through REST: answered 503… in its log. Forgejo can email the people who have failed-run notifications on (Settings → Notifications): look at whether CodeFloe sends it, and turn it on if it does.
Production's database size and monthly active users against the Free plan's limits, and whether staging is paused The same workflow's second step, go run ./tools/mk service usage, on runs of the default branch ("Usage alerts") The same red run, with ::error:: lines such as production database size: 410.0 MB of 500.0 MB (82.0%), at or past 80%… or staging is paused…. The log says only that each figure is below 80%, or its percentage when it is not: the repository's logs are public, so no raw size or count is printed. Detection takes up to six hours (the schedule), and a failure reaches the owner only with CodeFloe's failed-run email on (the owner's step 4).
Accounts that were asked to be deleted, and the job that deletes them pg_cron's job delete-due-accounts, every minute, in the database ("Deleting accounts: the job", below) Nowhere by itself: the query below, run by hand or from a scheduled check of the owner's
The record history's size, and the books that keep most of it The queries of "Record history: how much, and whose", below Nowhere by itself, as the job's

The check asks only what anyone can, with the publishable key every build carries, so it holds no secret (the usage step after it is the one that does). releases.json must also parse as JSON, so a host's own 200 page does not stand in for it. It expects: /auth/v1/health answers 200; /rest/v1/ without a key is turned away, 401 or 403; and /rest/v1/records?select=key&limit=1 with the key is 200, 401 or 403, which means PostgREST asked the database and it answered (a database that is down or out of connections, or a project that is paused, is not). Those are the answers the schema's grants and Supabase's gateway should give, but they were not asked of the hosted projects when the check was written: run it by hand once (Actions → Service → Run workflow) and read the log before trusting a quiet week, and if a probe is wrong, change probesOf in tools/mk/service.go. In particular, if records answers 404 (PostgREST code PGRST205: it does not expose a table the public has no grant on), that is the expectation being wrong, not an outage. The answer is not to widen what passes but to point the probe at a path whose answer the log shows to be certain, and which still reaches the database. A probe is tried again after 15 seconds, so a blip is not a failure.

It may keep a free project awake, which Supabase does not document. The probe that reaches the database is a request to it every six hours, and Supabase pauses a Free project with too little activity over 7 days, saying a few requests a day to the database or the API are enough (Project pausing). It does not say whether a request the database refuses (the probe's 401 or 403) counts. The status step of the same run tells (see "Usage alerts"): if staging pauses, the run goes red and says so, and the probe should ask something the public may read. A paused project is resumed from the dashboard (Resume project) for up to a year. A Pro project is not paused.

Deleting accounts: the job

An account asked to be deleted waits 7 days (public.deletions: when it is due, when its deletion began, when a step of it last failed and why). Then a job of the database's own deletes it: delete-due-accounts, scheduled by the migration *_delete_grace.sql with pg_cron, every minute, one account a run, 5 seconds of steps at most (a large account goes over some minutes). An account's own devices delete it a little further as they ask, but an account whose devices are all gone is deleted by the job alone: if the job stops (unscheduled, made inactive, or pg_cron's scheduler not running, say after a restore), nothing deletes it and nothing else says so. A step that fails is undone and kept on the account's row (failed, failure), and the job goes on with the others; an account that fails every time stays, and shows in the same query.

The query: it answers no row while all is well, and a row for each thing wrong.

-- Accounts due more than an hour ago (none should be: the job takes one a minute), with their last failure.
select 'overdue' as what, account::text as which, now() - due as late, failure
  from public.deletions where due < now() - interval '1 hour'
union all
-- The job: no run that went through in 10 minutes (or none at all), or the job gone or off.
select 'job', coalesce(max(d.end_time)::text, 'no run'), null, null
  from cron.job j left join cron.job_run_details d on d.jobid = j.jobid and d.status = 'succeeded'
  where j.jobname = 'delete-due-accounts' and j.active
  group by j.jobid
  having coalesce(max(d.end_time), '-infinity') < now() - interval '10 minutes'
union all
select 'job', 'not scheduled, or not active', null, null
  where not exists (select 1 from cron.job where jobname = 'delete-due-accounts' and active);

Run it in the dashboard's SQL editor (keep it as a saved snippet) with the weekly look at usage, after any restore, and after a release that changes the schema. The dashboard's Integrations → Cron page, where the plan's dashboard has it, lists the job and each run with its status. To have it watched: an alert needs something that runs SQL on a schedule and mails on a row, with a key that may read the database (the secret key, or a database user the owner makes for it); the repository's service check holds no secret and asks only what anyone can, so it is not that. That is the owner's to set up, or to decide against.

On a hosted project. The migration enables the extension (create extension if not exists pg_cron with schema pg_catalog) and schedules the job (cron.schedule), as the role mk backend applies migrations with (the Management API's database query). Supabase's guide to pg_cron does both from the SQL editor, which asks as the same role, and on this computer's service, as postgres, both work, while writing cron.job by hand is refused (cron.alter_job and cron.unschedule are the ways). It was not tried on a hosted project from here (nothing here holds a token for one). The first mk backend staging shows it: the service job after the merge. If either is refused, the migration fails whole (it runs as one transaction), staging is left as it was, and the job's log says why; turning pg_cron on in Database → Extensions and running the job again is the way then. Afterwards, select jobname, schedule, active from cron.job; in staging's SQL editor shows the job.

Record history: how much, and whose

The service keeps 30 days of each record's earlier versions (records_history, list_records_history; internals). A record keeps three rows an hour at most, and one a day when it is replaced more than five times in an hour (purged.history_often), as watch progress is while it plays, or when its book (a profile, or a shared list) keeps 20,000 rows or more (purged.history_budget, about 13 MB), until pruning brings it under. Nothing is refused past the budget: the book's history is thinner, and so is what a restore of it can put back. A record written often, or one of a book over its budget, keeps the first version replaced in its day and nothing more for a day, so a bad write after it cannot be undone to just before it; every write counts toward the five, a bad one among them (internals has the cases). In normal use a profile keeps about 600 rows (0.4 MB), 10 to 30% on top of its live records. A book past its budget is one rewriting its records in a loop (a sync bug flipping them between devices, or an account doing it on purpose), or one with tens of thousands of records changed at once (a bad push, a restore). What a book keeps is counted as it is kept, a row a book a day (history_kept, list_history_kept), so the queries below read the counts, never the history itself. Run them in the dashboard's SQL editor.

The books that keep most (their counts of the last 30 days, which the budget goes by):

select 'profile' as book, k.profile as id, p.account, sum(k.n) as rows
  from public.history_kept k join public.profiles p on p.id = k.profile
  where k.day >= public.history_day(now() - (select history_for from public.purged))
  group by k.profile, p.account
union all
select 'list', k.list, p.account, sum(k.n)
  from public.list_history_kept k join public.lists l on l.id = k.list join public.profiles p on p.id = l.owner
  where k.day >= public.history_day(now() - (select history_for from public.purged))
  group by k.list, p.account
order by rows desc limit 20;

The counts are an estimate within a day. One book's rows exactly: select count(*) from public.records_history where profile = '<id>'; (or public.list_records_history where list = '<id>'), which reads that book's part of its index alone.

History's share of the database, beside the live records (each table with its indexes and TOAST):

select t::text as "table", pg_size_pretty(pg_total_relation_size(t)) as size,
    round(100.0 * pg_total_relation_size(t) / pg_database_size(current_database()), 1) as "% of the database"
  from unnest(array['public.records_history', 'public.list_records_history', 'public.history_kept', 'public.list_history_kept',
    'public.records', 'public.list_records']::regclass[]) t
union all
select 'the database', pg_size_pretty(pg_database_size(current_database())), 100;

The alert: it answers no row while all is well, and a row for each thing wrong.

-- History is more than 35% of the database (in normal use it is 10 to 30% on top of the live records: a fifth of the whole or less).
select 'share' as what, round(100.0 * h / d, 1)::text || '%' as which, pg_size_pretty(h) as detail
  from (select (select sum(pg_total_relation_size(t)) from unnest(array['public.records_history', 'public.list_records_history',
      'public.history_kept', 'public.list_history_kept']::regclass[]) t) as h, pg_database_size(current_database()) as d) x
  where h > 0.35 * d
union all
-- A profile or a list that kept three budgets in 30 days: a version a day of each of its records, and still growing.
select 'profile', k.profile::text, sum(k.n)::text || ' rows'
  from public.history_kept k, public.purged p where k.day >= public.history_day(now() - p.history_for)
  group by k.profile, p.history_budget having sum(k.n) > 3 * p.history_budget
union all
select 'list', k.list::text, sum(k.n)::text || ' rows'
  from public.list_history_kept k, public.purged p where k.day >= public.history_day(now() - p.history_for)
  group by k.list, p.history_budget having sum(k.n) > 3 * p.history_budget
union all
-- Pruning is behind: versions older than history_for and a day (it runs with pushes, a batch at a time, at most once an hour).
select 'prune', min(h.replaced)::text, count(*)::text || ' rows of profiles'
  from public.records_history h where h.replaced < now() - (select history_for from public.purged) - interval '1 day'
  having count(*) > 0
union all
select 'prune', min(h.replaced)::text, count(*)::text || ' rows of lists'
  from public.list_records_history h where h.replaced < now() - (select history_for from public.purged) - interval '1 day'
  having count(*) > 0;

What a row means, and what to do:

  • share: look at the books that keep most (above). If none stands out, it is many accounts' normal use, and the database's size is what to watch ("Supabase's limits", below). history_budget, history_often and history_for are settings of the purged row and change without a deploy (update public.purged set history_budget = 10000;): a lower budget thins every book past it from its next push, a lower history_often thins records written less often (and a person's quick edits with them), and a shorter history_for shortens what a restore reaches.
  • profile or list: a book rewriting its records far more than people do. Find its account (the first query) and how many records it has (select count(*) from public.records where profile = '<id>';). Many records changed once (a restore, a bulk change) settle as the days pass; a few that keep growing are a loop. A loop in Mantle is a bug: file an issue with the counts, never the ids. An account doing it on purpose is the owner's to decide on.
  • prune: pruning runs only with pushes, so a quiet service keeps what it has. If pushes come in and the row stays, look in the database's log for prune_history: warnings.

Run them with the weekly look at usage. As with the job's query, having the alert run on a schedule needs a key that reads the database, which is the owner's to set up or to decide against.

The failure on staging: "Connection terminated due to connection timeout"

CI's job "Together's service" on main once failed with POST /database/query: 544 … Connection terminated due to connection timeout. mk backend staging asks the database through Supabase's Management API, and that API could not get a connection to the project's database in time: the database was not answering at that moment. It was paused, out of connections, or too busy (the smallest compute has 60 direct connections and 200 pooled ones: Compute and disk). What would have caught it:

  • The service check, if it ran near it: the "database through REST" probe is a 5xx or no answer while the database is down. A failed CI run next to a passing check an hour later says it was brief.
  • A paused project (the likeliest for staging, which idles between merges): the auth health probe fails, and Supabase emails a warning a week before the pause.
  • Connections: Database → Reports (database connections) and Observability → Database connections, when it happens again.

mk backend asks again after 2, 5 and 15 seconds when the API answers that its connection timed out (the query never reached the database, so nothing runs twice); a run that still fails after that is worth the reports above.

Supabase's limits that Mantle can reach

Mantle's service is a Postgres database, Auth and the REST API (with Realtime for a few tables); no Storage and no Functions. These are the limits that matter, with the Free plan's figures (Billing). On a paid plan they are larger or paid for, and Organization → Usage shows the plan's own.

Limit Free plan What reaches it in Mantle Where to see it
Database size 500 MB per project Sealed records: every title of a profile, and 30 days of history on top (a record keeps up to three rows an hour; one written more than five times in an hour, as watch progress is, and every record of a profile or list past 20,000 rows of history, one a day: "Record history: how much, and whose"). More accounts, more rows. Database → Reports; Organization → Usage
Egress 5 GB a month Pulls: a device's first pull is the whole book, 1000 records a page; later ones are what changed. A restore makes every device pull again. Organization → Usage
Monthly active users 50,000 One per account that signs in in the month; a device approved by another is a user of its own (auth.users, anonymous). Organization → Usage
Connections 60 direct, 200 pooled on the smallest compute The API's own (PostgREST) and the Management API's. Nothing in an app opens one. Database → Reports; Observability → Database connections
Auth: emails Supabase's own mail: 2 an hour for the whole project. Mantle's mail service is Resend's, with the repository's 30 an hour (auth.rate_limit.email_sent; releasing.md). A sign-in code is an email; a burst of sign-ups is the limit. Authentication → Rate limits; the mail provider's own page
Auth: requests Per IP address, in 5 minutes: 30 sign-ups or sign-ins, 150 token requests (Rate limits) A household behind one address Authentication → Rate limits; Logs → Auth

What Supabase does past a limit depends on the plan: its billing FAQ says an organisation over its quota is notified and, if it goes on, restricted, which can mean pausing, read-only or 402 answers, and that a Pro organisation's spend cap is on by default (Billing FAQ). Check both against the plan in Organization → Billing (your plan). A project answering 402 or read-only would show as failed pushes in the apps, and might not in the check above: look at the usage before that.

Usage alerts (automatic)

Supabase's billing FAQ says an organisation is notified when it goes over its quota, not at a threshold before it, so Mantle reads the figures itself. The workflow "Service" runs go run ./tools/mk service usage after the check (and when the check failed), on runs of the default branch: that step alone holds SUPABASE_ACCESS_TOKEN (and SUPABASE_STAGING_ACCESS_TOKEN when staging has its own). The workflow does not run on a pull request, and the step's if: keeps it to main, but that guards against accidents: the real protection is who may push to the repository, which is those trusted with its code (releasing.md). Through the Management API's database query it reads two totals from production, never a row: pg_database_size, and the number of distinct users that signed in (auth.users.last_sign_in_at) or had a session refreshed (auth.refresh_tokens created or updated) in the last 30 days: sign-ins and session refreshes, which is how Mantle counts a monthly active user (Supabase's own figure in Organization → Usage may differ a little). The log is public, so no figure is printed: it says each is below 80% of its limit, and a figure at 80% of the Free plan's (500 MB, 50,000) fails the run with an ::error:: line that gives the percentage. A paid plan's limits are larger: change freeDatabaseBytes and freeActiveUsers in tools/mk/usage.go when the plan changes. It also asks the Management API for staging's project status (GET /v1/projects/<ref>) and fails the run unless it is ACTIVE_HEALTHY: a paused or inactive project is said to be paused, to be resumed in the dashboard (Resume project), and a status not known is an error too. A change in progress (COMING_UP, RESTORING, UPGRADING, RESIZING) gets a ::warning:: naming it, not a failure: the next run says whether it settled.

  • Egress is not read: the Management API has no usage figure for it that Mantle reads, so the log says so. Supabase's own notice, when the organisation goes over, and Organization → Usage are what there is. For Mantle the database's size is the likelier first limit.
  • Connections and emails have no alert: Database → Reports shows the first, and the mail provider's page the second.
  • A run that cannot read the figures (a revoked or missing token, the API down) fails too and says why, so a silent check is not a quiet week.

The owner's steps

  1. Fill in the table "What the backups keep" from the dashboard (plan, daily backups, PITR). If there are none, read "If the plan has no backups".
  2. Make the canary, make the first logical backup by hand (go run ./tools/mk supabase db dump …), and try restoring it into this computer's service, with the checks. That needs no paid project. Write down the time and the size.
  3. If the plan has physical backups, do the restore to a new project once, with its checks, and delete the project.
  4. Turn on CodeFloe's emails for failed runs (Settings → Notifications), your own notification setting, so that a red "Service" run reaches an inbox. That is the owner's whole part of #297: the six-hourly check is the uptime monitor, and the usage figures and staging's status are in it, so there is no outside monitor to add, no usage alert to set and no table to keep. Without that email a red run is seen only in the Actions list. (The maintainer dispatches the workflow once on main after merging, Actions → Service → Run workflow, and reads the log for the statuses the probes get and that usage passes. If records answers 404, see "What is watched" for what to change.)
  5. Run the query of "Deleting accounts: the job" once at staging after the release that brings it, and at production after the release; decide whether to have it run on a schedule (it needs a key that reads the database).
  6. If the plan has no backups, or no PITR, decide whether to move to one that has.

This page is made from docs/dev/service-ops.md in the repository.