# Standard Seed Pattern — Allocating New IDs via `MAUTONUMBER`

## Why this exists

Master/metadata tables that get authored once in `uscdevsys` and synced downstream
(`uscimpsys`, then each client DB) — `MMODULE`, `MMENU`, `MWEBFORM`, `MROLE`,
`MMODULEVSMENU`, `MROLEVSMENU`, `MREPORT`, etc. — must never generate a new row's ID via
`(SELECT MAX(id) FROM Table) + 1`. That approach:

- Races under concurrent writers (two sessions can compute the same "next" value).
- Ignores this system's actual ID-allocation convention, which is a dedicated per-entity
  counter table (`MAUTONUMBER`), not "whatever the highest existing ID happens to be."
- Has no relationship to `ServerConfigOffset`-based ID-space partitioning, which is how this
  system keeps IDs generated on different tiers/servers from colliding when synced together.

`MAUTONUMBER` (`ENTITYID`, `ENTITYCODE`, `AUTOID`) already exists for exactly this purpose —
one row per entity (`'MODULE'`, `'MENU'`, `'WEBFORM'`, `'ROLE'`, `'MODULEVSMENU'`,
`'ROLEVSMENU'`, `'REPORT'`, ...), each tracking the last-issued ID for that entity. The
GB5-native equivalent, `GB5Shared.GenerateAutoNumber.AutoNumber.GetAutoNumber` (used
everywhere the app itself needs new IDs at runtime), does this via a single atomic
`UPDATE ... OUTPUT/RETURNING` — this pattern is the same thing, written as raw SQL for use in
migration/seed scripts that run outside the app (e.g. against `uscdevsys` directly).

## The pattern

Always reserve **exactly as many IDs as rows you are about to insert** — computed from
whatever idempotency guard (`WHERE NOT EXISTS (...)`) the seed already uses — never the full
length of a static seed list. Re-running an idempotent seed migration must not burn ID ranges
it doesn't end up using.

### SQL Server

```sql
DECLARE @Count INT = (
    SELECT COUNT(*) FROM #SeedTable s
    WHERE NOT EXISTS (SELECT 1 FROM TargetTable t WHERE t.NaturalKey = s.NaturalKey)
);

IF @Count > 0
BEGIN
    DECLARE @IdTable TABLE (AutoId BIGINT);

    -- Trigger-safe: MAUTONUMBER has a trigger, so OUTPUT must go INTO a table variable
    -- (bare OUTPUT is rejected with error 334) — mirrors AutoNumber.cs exactly.
    UPDATE MAUTONUMBER
    SET    AUTOID = AUTOID + @Count
    OUTPUT DELETED.AUTOID INTO @IdTable   -- value BEFORE increment
    WHERE  ENTITYCODE = 'MENU';           -- swap per table: 'WEBFORM'/'MODULE'/'ROLE'/etc.

    DECLARE @BaseId BIGINT = (SELECT AutoId FROM @IdTable);

    INSERT INTO TargetTable (Id, ...)
    SELECT @BaseId + ROW_NUMBER() OVER (ORDER BY s.SortKey), ...
    FROM #SeedTable s
    WHERE NOT EXISTS (SELECT 1 FROM TargetTable t WHERE t.NaturalKey = s.NaturalKey);
END
```

`ROW_NUMBER() OVER (...)` in the final `SELECT` is computed *after* the `WHERE NOT EXISTS`
filter is applied, so it numbers exactly the `@Count` missing rows as `1..@Count` — lining up
precisely with the `@Count` IDs just reserved. No separate "which rows are new" bookkeeping
needed beyond the guard the seed already has.

### PostgreSQL

```sql
DECLARE
    v_count   INT;
    v_base_id BIGINT;
BEGIN
    SELECT COUNT(*) INTO v_count FROM seed_table s
    WHERE NOT EXISTS (SELECT 1 FROM target_table t WHERE t.natural_key = s.natural_key);

    IF v_count > 0 THEN
        -- RETURNING gives the value AFTER increment (opposite of SQL Server's OUTPUT DELETED
        -- above) — normalize by subtracting v_count to get the same "previous" baseline.
        UPDATE mautonumber SET autoid = autoid + v_count
        WHERE entitycode = 'MENU' RETURNING autoid INTO v_base_id;
        v_base_id := v_base_id - v_count;

        INSERT INTO target_table (id, ...)
        SELECT v_base_id + ROW_NUMBER() OVER (ORDER BY s.sort_key), ...
        FROM seed_table s
        WHERE NOT EXISTS (SELECT 1 FROM target_table t WHERE t.natural_key = s.natural_key);
    END IF;
END;
```

## Entity codes confirmed to exist in `MAUTONUMBER` (checked directly against a live DB)

`MODULE`, `MENU`, `WEBFORM`, `ROLE`, `MODULEVSMENU`, `ROLEVSMENU` — confirm any other
entity code you need against the target database's own `MAUTONUMBER` table before relying on
it; don't assume every table has one without checking (`SELECT * FROM MAUTONUMBER WHERE
ENTITYCODE = '...'` — if it returns no rows, that entity has no counter yet and one must be
created, which is a separate decision, not a migration-time default).

## Where this applies

Any migration/seed script that inserts new rows into a table whose IDs originate in
`uscdevsys` and sync downstream — not just Entitlement's menu-seed migrations. See
`DB/Migrations/20260716_Entitlement_Phase1_6_MenuSeed_*.sql` and
`DB/Migrations/20260720_Entitlement_ApprovalInbox_MenuSeed_*.sql` for a worked example across
four tables (`MWEBFORM`, `MMENU`, `MMODULEVSMENU`, `MROLEVSMENU`) in one script.

## What this does NOT cover

- Actually running these scripts — they still need `@ModuleId`/`@AdminRoleId`-style
  environment-specific values resolved against the target install before execution.
- Whether a given entity's `MAUTONUMBER` row itself needs to exist first — it's assumed
  already seeded (confirmed true for the six entity codes above).
- The devsys → impsys → client sync mechanism itself (how a row inserted in `uscdevsys`
  actually reaches downstream databases) — that's a separate, existing mechanism, not something
  this pattern creates.