[Docs]: migrations.mdx says plain `db query` wraps in a transaction, but both raw-SQL routes share one BEGIN-free path, so it steers users to the root back door for no reason

What

#1968 added a "Non-transactional statements" section to docs/core-concepts/database/migrations.mdx (and the es, zh, zh-Hant copies). One claim in it does not match the backend:

docs/core-concepts/database/migrations.mdx:62 Plain npx @insforge/cli db query (without --unrestricted) wraps your statement in a transaction, so it hits the same error — use --unrestricted for non-transactional commands.

Both raw-SQL routes call the same service method, and that method never issues BEGIN.

Why it matters

--unrestricted is not a transaction switch, it is a privilege switch. The route's own docblock calls it what it is:

// backend/src/api/routes/database/advance.routes.ts:43-45
 * POST /api/database/advance/rawsql/unrestricted
 *
 * Root back door for project-admin-only operations that need full database owner privileges.

So the doc tells a reader that the ordinary path cannot run CREATE INDEX CONCURRENTLY and that the fix is the root back door. If the ordinary path does not actually wrap in a transaction, the reader is being sent through superuser privileges to do something project_admin could have done. That is the part I would want fixed even if the wording were otherwise harmless.

Proof

Both routes, same method, differing only in asRoot:

// advance.routes.ts:48  POST /rawsql/unrestricted
: await dbAdvanceService.executeRawSQL(query, params, true);

// advance.routes.ts:~122  POST /rawsql
: await dbAdvanceService.executeRawSQL(query, params);   // asRoot defaults to false

executeRawSQL spans lines 87-153 of database-advance.service.ts. It sets the timeout, runs the statement, and never opens a transaction:

$ grep -n "'BEGIN'" backend/src/services/database/database-advance.service.ts
175:      await client.query('BEGIN');
814:      await client.query('BEGIN');
1032:      await client.query('BEGIN');

Line 175 belongs to executeExplain (declared at :156), not executeRawSQL; 814 and 1032 are the import and bulk paths. So no BEGIN falls inside 87-153.

The one boolean that looks like it might wrap is not a transaction flag either. executeRawSQL passes transactionLocal: false:

// user-context.service.ts:90-106
export async function withAdminContext<T>(
  client: PoolClient,
  fn: () => Promise<T>,
  transactionLocal: boolean = false,
  ...
  await client.query(
    transactionLocal ? 'SET LOCAL ROLE project_admin' : 'SET ROLE project_admin'
  );

It chooses SET LOCAL ROLE vs SET ROLE. executeExplain passes true because it really is inside a transaction; executeRawSQL passes false.

The section's other claims check out, so this is one sentence and not the whole block:

• Migrations really are wrapped: database-migration.service.ts:79 issues BEGIN under an advisory lock, so CREATE INDEX CONCURRENTLY genuinely fails there. The premise of the section is correct.
• The 30-second timeout at :65 is correct: both paths run SET statement_timeout = 30000 (database-advance.service.ts:99 and :172).

What I could not verify, stated plainly

@insforge/cli is not in this repo (packages/ holds only dashboard, shared-schemas, ui), so I cannot rule out that the CLI wraps non---unrestricted queries client-side before calling /rawsql. If it does, the claim is true of the CLI rather than of the API, and the doc should say so, because as written a reader concludes the API behaves that way. Either the sentence is wrong or it is attributing CLI behaviour to the backend. I did not want to guess which, so I am not proposing exact replacement wording.

Root cause

The section explains the migration transaction correctly, then reaches for --unrestricted as the escape hatch and describes it by the wrong mechanism. Privilege and transaction scope are two different axes, and the flag only moves one of them.

Proposed fix

Depends on the answer above:

1. If the CLI does not wrap: drop the :62 sentence and point readers at plain db query, mentioning --unrestricted only where object ownership actually requires root.
2. If the CLI does wrap: keep the guidance but attribute it to the CLI, so nobody reads it as an API guarantee.

Happy to write either patch across all four locale copies once a maintainer confirms which. Flagging rather than sending a diff, since picking wrong would put an incorrect sentence in four files instead of one.

Acceptance criteria

• migrations.mdx:62 either states the real mechanism or is removed, in en, es, zh and zh-Hant.
• The doc does not recommend the root back door for an operation that does not need root.
• The correct claims in the section (migrations wrapped, 30s timeout) are left intact.