teardown() drops functions before tables on MSSQL — fails on scalar UDFs referenced by CHECK constraints (regression in alpha.35)

[damusix]

Bug: teardown() drops functions before tables — fails on scalar functions referenced by CHECK constraints (MSSQL)

• Package: @noormdev/sdk
• Broken in: 1.0.0-alpha.35
• Last working: 1.0.0-alpha.27
• Dialect: mssql
• Severity: High — teardown() aborts on any schema where a scalar UDF is referenced by a CHECK constraint (or computed column / schema-bound object). This is the canonical base/subtype "IsType" pattern in SQL Server, so any such schema cannot be torn down. Breaks test harnesses that rebuild the schema in globalSetup.

Summary

ctx.noorm.db.teardown() on MSSQL now drops functions before tables. SQL Server refuses to drop a scalar function while a table whose CHECK constraint references it still exists, so teardown throws:

Cannot DROP FUNCTION 'dbo.AI_Usage_IsType_fn' because it is being referenced by object 'AIU_Message_IsAIUsageType'.
(RequestError, number: 3729)

alpha.27 dropped tables before functions, so the table (and its CHECK constraint) was already gone by the time the function was dropped. alpha.35 reordered the statement generation and regressed this.

Root cause

In teardownSchema (dist/index.js), the order in which drop statements are pushed changed between versions.

alpha.27 — tables before routines:

foreign keys → views → tables → functions → procedures → types

alpha.35 — routines before tables:

foreign keys → procedures → functions → views → tables → types

The fetch step (Promise.all over tables/views/functions/procedures/types/foreignKeys) is identical. Only the order of the statements.push(...) blocks moved: procedures and functions are now emitted before views and tables.

On MSSQL, a scalar UDF referenced by a CHECK constraint cannot be dropped while the referencing table exists (error 3729). Dropping the table first removes the constraint dependency; dropping the function first fails. The same hazard applies to computed columns and WITH SCHEMABINDING views/functions that reference a UDF.

Minimal reproduction

CREATE FUNCTION dbo.IsPositive_fn(@n INT) RETURNS BIT
AS BEGIN RETURN IIF(@n > 0, 1, 0) END;
GO
CREATE TABLE dbo.Thing (
    Id INT PRIMARY KEY,
    Qty INT NOT NULL,
    CONSTRAINT Thing_IsPositive CHECK (dbo.IsPositive_fn(Qty) = 1)
);

const ctx = await createContext({ config: 'test', requireTest: true });
await ctx.connect();
await ctx.noorm.db.teardown();
// alpha.27: OK
// alpha.35: throws — Cannot DROP FUNCTION 'dbo.IsPositive_fn'
//           because it is being referenced by object 'Thing_IsPositive'. (3729)

A/B evidence (same database, same freshly-built schema)

[repro] sdk=1.0.0-alpha.27 phase=build    db=taxgentic_test  AIU_Message_IsAIUsageType present=true
[repro] sdk=1.0.0-alpha.35 phase=teardown TEARDOWN FAILED: Cannot DROP FUNCTION 'dbo.AI_Usage_IsType_fn'
                                          because it is being referenced by object 'AIU_Message_IsAIUsageType'.
[repro] sdk=1.0.0-alpha.27 phase=teardown TEARDOWN OK

Only the SDK version changed between the failing and passing teardown; the schema and connection were identical.

Expected vs actual

• Expected: teardown() drops objects in dependency-safe order, so a scalar function referenced by a CHECK constraint is dropped after the table that references it (the alpha.27 behavior).
• Actual: functions are dropped before tables, so teardown aborts with error 3729 on any schema using the function-backed CHECK pattern.

Proposed fix

Restore tables-and-views-before-routines ordering for MSSQL:

foreign keys → views → tables → functions → procedures → types

A more defensive option is to drop CHECK constraints that reference functions (and any computed columns) before dropping functions, but simply emitting table drops before function drops is sufficient and matches the previously-correct behavior.

Add a regression test: a table with a CHECK constraint calling a scalar UDF, then assert teardown() succeeds.