DataProviderMigrate CLI: `migrate` command does not invoke SchemaDiff — never applies RLS, never alters existing schema [NAP P0 blocker]

[MelbourneDeveloper]

Filed by NimblesiteAgenticPlatform (NAP, agent NapSupport2). Tested 0.1.0-rls-preview1 from `/tmp/nap-rls-nuget` against a fresh `supabase/postgres:15.1.0.117` container with NAP's full schema.yaml (14 tables + `rowLevelSecurity` sections per #32 / #36 / #37 syntax).

Observed

• CLI: `DataProviderMigrate migrate --schema migrations/schema.yaml --output "Host=...;..." --provider postgres`
• Run 1 against empty DB: 14 tables created.
• Run 2: prints `Created table` for all 14 again (data persists, so it's `CREATE TABLE IF NOT EXISTS` semantics).
• No RLS applied. `relrowsecurity = false`, `relforcerowsecurity = false` on every table. `pg_policies` is empty. `rowLevelSecurity` sections in YAML are silently skipped.

Root cause (from reading the source)

Migration/DataProviderMigrate/Program.cs → `CreatePostgresDatabase()` calls `PostgresDdlGenerator.MigrateSchema()`.

Migration/Nimblesite.DataProvider.Migration.Postgres/PostgresDdlGenerator.cs → `MigrateSchema()` only iterates `schema.Tables` and emits `CreateTableOperation`. It does not invoke `SchemaDiff.Calculate`. Therefore:

• No `EnableRlsOperation` / `CreateRlsPolicyOperation` / `DisableForceRlsOperation` is ever produced.
• No drift handling. A column dropped from YAML stays in the live DB. A constraint dropped from YAML stays. The pre-existing cross-schema FK to `auth.users` that NAP needs DP to drop on prod will NOT be dropped.
• The `allowDestructive` flag has no effect because `SchemaDiff` is never called.

The library has all the RLS infrastructure (66 tests pass per your TMC report). The CLI just doesn't use it.

What NAP expected

The CLI is the operator-facing surface. The natural shape:

1. CLI inspects current schema (`PostgresSchemaInspector`).
2. CLI parses YAML.
3. CLI calls `SchemaDiff.Calculate(current, desired, allowDestructive: true)`.
4. CLI iterates `OperationsResult`, calls `PostgresDdlGenerator.Generate(op)` for each, executes.
5. Result includes counts of each operation type emitted, not just `Created table`.

NAP impact

Per docs/specs/migrations.md `[MIGRATIONS-PROD]`, NAP's prod path is:

```
dotnet DataProviderMigrate migrate --schema migrations/schema.yaml --provider postgres --output ""
```

Today this run does nothing useful against existing prod. NAP cannot:

• Drop the legacy cross-schema FK (`api_keys_user_id_auth_users_fk`, `tenant_members_user_id_auth_users_fk`) per `[MIGRATIONS-SCHEMA-YAML]`.
• Apply ENABLE / FORCE RLS or CREATE POLICY.
• Migrate the schema at all going forward.

This is a P0 blocker for NAP's prod operator workflow and TradiSite unblock (POST /api/v1/tenants 500 root cause is the legacy auth.users FK).

Reasonable scope to ship

Smallest CLI change that unblocks: wire `SchemaDiff.Calculate(allowDestructive: true)` + `PostgresSchemaInspector` + iterate `OperationsResult` in `CreatePostgresDatabase`. Keep the existing summary output. Add a CLI flag `--allow-destructive` defaulting to `false`, NAP would pass `--allow-destructive` for prod runs.

If this is a bigger change than expected, even shipping a separate command `DataProviderMigrate sync` or `DataProviderMigrate apply` that uses SchemaDiff would unblock NAP — we can update our spec to point at the new command.

Repro

```sh
docker run -d --rm --name dp-test -p 55432:5432 \
-e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres \
supabase/postgres:15.1.0.117

dotnet tool install --tool-path /tmp/dp DataProviderMigrate \
--add-source /tmp/nap-rls-nuget --version 0.1.0-rls-preview

curl -O https://raw.githubusercontent.com/Nimblesite/NimblesiteAgenticPlatform/main/migrations/schema.yaml

/tmp/dp/DataProviderMigrate migrate --schema schema.yaml \
--output "Host=localhost;Database=postgres;Username=postgres;Password=postgres;Port=55432" \
--provider postgres

docker exec dp-test psql -U postgres -c \
"SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class \
WHERE relkind='r' AND relnamespace='public'::regnamespace ORDER BY relname"

All rows show f / f. RLS not applied.

docker exec dp-test psql -U postgres -c \
"SELECT count(*) FROM pg_policies WHERE schemaname='public'"

0

```

[MelbourneDeveloper]

Fixed in commit b728f82 on feat/rls-migration-tier1 (PR #38). The migrate subcommand now uses the Inspect → SchemaDiff.Calculate → MigrationRunner.Apply pipeline for both SQLite and PostgreSQL, so RLS / FK / column / index changes all surface.

Smoke verified locally:

$ DataProviderMigrate migrate --schema rls.yaml --output app.db --provider sqlite
Loaded schema 'smoke' with 1 tables
Connected to SQLite: app.db
Applying 3 operation(s):
  CreateTableOperation
  EnableRlsOperation
  CreateRlsPolicyOperation
Migration completed successfully

$ DataProviderMigrate migrate --schema rls.yaml --output app.db --provider sqlite
Schema is up to date — no operations needed

Changes:

• New --allow-destructive flag (off by default; required for FK/policy/RLS drops, FORCE removal, DISABLE RLS)
• PostgresSchemaInspector and SqliteSchemaInspector flipped from internal to public so the CLI can reach them
• Re-runs against a converged DB now emit zero operations (idempotent) instead of pretending to recreate tables
• Repacked as 0.1.0-rls.preview2 at /tmp/nap-rls-nuget

PR #38 has all the changes; pre-merge CI was green at a3b287c, awaiting fresh run on b728f82.

[MelbourneDeveloper]

Closing as fixed in PR #38 / v0.9.7-beta. Local receipt on 2026-05-06: DataProviderMigrate now uses Inspect -> SchemaDiff.Calculate -> MigrationRunner.Apply with --allow-destructive support; code references are in Migration/DataProviderMigrate/Program.cs. This issue is no longer the NAP blocker; remaining overlay blockers are #33/#34/#35.