drizzle adapter: drizzle-kit push proposes dropping the workspace_isolation RLS policies (createSchema() cannot express RLS / the chunks FK)

[riccardomerlo]

drizzle adapter: drizzle-kit push proposes dropping the workspace_isolation RLS policies (createSchema() cannot express RLS / the chunks FK)

Versions: bash-gres 3.0.0 (also reproduced on 2.10.0) · drizzle-orm 0.44/0.45 · drizzle-kit 0.31.10 · PostgreSQL 18

Context

We use the documented "Path A": the fs_* tables live in our Drizzle schema via
createSchema() from bash-gres/drizzle, migrations are generated with
drizzle-kit, and the RLS bootstrap (from generateMigrationSQL()) is applied as
a raw SQL migration. Runtime setup() is never called.

Problem

createSchema() returns the tables and indexes, but two pieces of DDL exist
only in generateMigrationSQL() output and are not represented in the Drizzle
schema objects:

1. the workspace_isolation RLS policies (+ ENABLE/FORCE ROW LEVEL SECURITY);
2. the composite FK fs_blob_chunks_blob_fkey
   (fs_blob_chunks(workspace_id, blob_hash) → fs_blobs(workspace_id, hash), v3).

drizzle-kit push diffs the TS schema against the live database, so on a
perfectly migrated database it always proposes:

ALTER TABLE "fs_entries" DISABLE ROW LEVEL SECURITY;
DROP POLICY "workspace_isolation" ON "fs_entries" CASCADE;
-- ... same for fs_version_roots, fs_versions, version_ancestors, fs_blobs, fs_blob_chunks
ALTER TABLE "fs_blob_chunks" DROP CONSTRAINT "fs_blob_chunks_blob_fkey";

One accidental confirm and the multi-tenant isolation is gone. This makes
db:push (a common dev loop for the rest of the schema) a footgun in every
Path A project.

Repro

1. New project, createSchema({ enableFullTextSearch: false, enableVectorSearch: false })
   exported from the drizzle-kit schema entry.
2. drizzle-kit generate, append the generateMigrationSQL() bootstrap as a raw
   migration, drizzle-kit migrate.
3. drizzle-kit push → proposes the statements above on a database that is
   fully in sync with the migrations.

Proposal

drizzle-orm can express both pieces natively nowadays, so createSchema() could
declare them and the drift would disappear for every Path A user:

• RLS policies: pgPolicy("workspace_isolation", { for: "all", using: sql`...`, withCheck: sql`...` })
  in the table extras callback, plus .enableRLS() on the tables
  (FORCE ROW LEVEL SECURITY has no drizzle API yet, but drizzle-kit does not
  introspect/diff it, so it can stay in generateMigrationSQL() without causing
  push drift).
• Chunks FK: foreignKey({ columns: [...], foreignColumns: [...] }) supports
  composite keys in the table extras callback.

generateMigrationSQL() would then shrink to what genuinely cannot live in the
schema (CREATE EXTENSION ltree, FORCE RLS).

Workaround we're shipping meanwhile

A dedicated drizzle-push.config.ts whose schema entry excludes fs.ts and
filters the DB side with tablesFilter: ["*", "!fs_*", "!version_ancestors"].
Note for anyone copying this: drizzle-kit does not apply tablesFilter to
sequences, so push then proposes DROP SEQUENCE fs_version_roots_id_seq / fs_versions_id_seq (they are owned by the filtered-out bigserial columns); we
had to declare those two with pgSequence() in a push-only schema file to get a
clean "No changes detected".

Happy to send a PR for the createSchema() change if you're open to it.