Management API Apply Migration: Scoped PAT Eligibility, Transaction Atomicity & SQL Error Contract #53225
Replies: 3 comments 2 replies
|
|
Here are the concrete architectural and implementation details for 1. Scoped PAT Eligibility & Access Control
2. Transaction Atomicity & Migration LedgerThe endpoint executes the user-submitted SQL and records the migration in Under the hood (in BEGIN;
-- 1. Apply user SQL from request body
<your submitted query>;
-- 2. Track statements in the schema_migrations ledger
INSERT INTO supabase_migrations.schema_migrations (version, name, statements)
VALUES (
to_char(current_timestamp, 'YYYYMMDDHH24MISS'),
$<dollar_quote>$<name>$<dollar_quote>$,
ARRAY[$<dollar_quote>$<query>$<dollar_quote>$]
);
COMMIT;Behavioral implications:
3. SQL Error Response ContractWhen submitted SQL causes a PostgreSQL runtime error (such as
4. Success Response Contract & Post-Validation
|
|
Adding the empirical half of this — you do not have to take the atomicity contract on faith, and the error contract is testable on staging with a sentinel you control. From running migration-safety checks, four probes answer everything you asked: Probe 1 — SQL rollback. Baseline first: Probe 2 — history-first vs data-first failure. The reverse case matters more for your ledger: a migration whose history write would fail while SQL succeeds. Hard to force directly, but you can approximate the consequence: compare the highest Probe 3 — embedded BEGIN/COMMIT. Submit a migration with an explicit Probe 4 — the error contract. For the fail-closed runner, do not classify by parsing Supabase's message text — it can change. Two things that are stable enough to build on: (a) run your 22012 case once and record the actual status/body shape into a fixture, then assert against your fixture in CI — you learn the shape empirically instead of guessing between 400/409/422/500; (b) make your own migration carry a marker: One process point for the ledger: after every successful apply, run an independent consistency check (version parity between |
Uh oh!
There was an error while loading. Please reload this page.
Hi Supabase team and community,
We're preparing a controlled PostgreSQL migration atomicity test on a dedicated Staging project. Before running any migration probes, we'd like to clarify three aspects of the Supabase Management API.
Endpoint:
POST /v1/projects/{ref}/database/migrationsPostgreSQL: 17.11
Authentication: Staging-only, project-scoped Personal Access Token (PAT)
1. Scoped PAT eligibility
The current API documentation lists
database_migrations_writeas the required permission for applying migrations.2. Transaction atomicity and migration ledger
Does the Apply Migration endpoint execute the submitted SQL and write the corresponding entry to
supabase_migrations.schema_migrationswithin the same atomic database transaction?Specifically:
BEGIN/COMMITstatements inside the submitted SQL discouraged when using this endpoint?We need to distinguish SQL execution rollback from migration-history consistency.
3. SQL error response contract
When a submitted migration fails with PostgreSQL
SQLSTATE 22012(division by zero):codecontain the PostgreSQL SQLSTATE or a Supabase-specific error code?Additional response clarification
Can HTTP 200 return an empty body or
{}? Is an independent schema and migration-history check recommended after success?We're implementing a fail-closed validation runner that does not retry ambiguous migration submissions.
We are not asking anyone to execute migrations or access our project. We have intentionally avoided speculative migration requests to discover undocumented behavior.
References:
Any clarification from Supabase maintainers, or links to authoritative implementation/documentation details, would be greatly appreciated.
Thank you!
All reactions