[BUG] Policy ordering: policy created before referenced table exists

[ChielTimmermans]

Describe the bug**

When generating a migration diff, a policy that references another table in its USING clause is ordered before the referenced table is created, causing the migration to fail.

Expected behavior**

The diff generator should analyze policy USING and WITH CHECK clauses for table references and order statements so that:

1. All tables referenced by a policy are created first
2. The policy is created after its dependent tables exist

Correct ordering would be:

1. CREATE TABLE tenant.projects
2. CREATE TABLE tenant.project_members
3. CREATE POLICY project_members_org_policy

To Reproduce

Given a schema with:

1. Table tenant.project_members with a row-level security policy:

CREATE POLICY project_members_org_policy ON tenant.project_members
   AS PERMISSIVE
   FOR ALL
   TO fun_fundament_api
   USING (EXISTS (
     SELECT 1 FROM projects
     WHERE projects.id = project_members.project_id
     AND projects.organization_id = current_setting('app.current_organization_id')::uuid
));

2. Table tenant.projects (referenced by the policy above)

Observed Behavior

The generated migration orders statements as:

1. CREATE TABLE tenant.project_members
2. CREATE POLICY project_members_org_policy (fails - references tenant.projects which doesn't exist yet)
3. ... other statements ...
4. CREATE TABLE tenant.projects

Context

pg-schema-diff version: v1.0.5
pg-schema-diff usage: LIBRARY
Postgres version: 18

[GeertJohan]

Analyzing this a bit as we're using this package from trek.

Specifically, this failed in assertValidPlan > setSchemaForEmptyDatabase. It will try to apply the 'currentSchema' to an empty database, which fails because the DDL's are not ordered properly.

I thought there would be a workaround by manually re-ordering the DDL in the generated .up.sql file. But this doesn't seem to fix the issue because the DDL order from .up.sql files is lost in the abstraction via SchemaSource and schema.GetSchema. Those will collapse any order of DDL in .up.sql files to a schema.Schema, which is then converted to DDL again by a call to generateMigrationStatements in setSchemaForEmptyDatabase. If somehow the original set of DDL from .up.sql files could be preserved, then they would apply correctly.

For trek I will disable validation for now, as we also apply the generated diff statements to a database as part of validation inside of trek. We setup that validation db using the .up.sql files so that order in DDLs is preserved. However, trek's checks aren't as thorough as pg-schema-diff so I would actually prefer to move fully to pg-schema-diff for validation.