@aiquants/governance-drizzle

Drizzle ORM models and schema definitions for the Enterprise Knowledge Governance platform (dbo schema on Microsoft SQL Server).

Overview

Provides strictly typed Drizzle models for the 4 governance tables:

1. TdDocumentAuthority (dbo.td_document_authority): the authority ledger. Bi-temporal document authority tiers (1-5), validity windows (datetimeoffset(7)), transaction times, the approval status, the last approval (approvedBy / approvedAt, kept when an approval is withdrawn), the last withdrawal (withdrawnBy / withdrawnAt), and the optimistic-concurrency version rowVersion (mssqlRowVersion).
2. TdPolicyConflicts (dbo.td_policy_conflicts): detected tie pairs between authority documents.
   • The detector inserts a pair with its documents (sourceMetadataId, targetMetadataId), the detection columns (policyCategory, the facts detectedRuleDiff (mssqlJson), their digest factsHash, severity, conflictSummary, lastDetectedAt, clearedAt) and resolutionStatus = "UNRESOLVED".
   • Afterwards the detector writes only the detection columns, and only a decision changes the resolution columns (resolutionStatus, prevailingMetadataId, arbitrationDecision, resolvedBy, resolvedAt, decidedFactsHash).
   • The model types the facts as Record<string, unknown>; a reader validates them with the governance core's PolicyConflictFactsSchema.
   • The decision's reason arbitrationDecision is nvarchar(max) in the table, while the governance core bounds a stored reason at POLICY_CONFLICT_DECISION_REASON_MAX_LENGTH (2000 UTF-16 code units): a reader validates it with StoredDecisionReasonSchema, which refuses a longer reason when it is read, so no writer is to store a longer one.
3. TdPolicyConflictDetection (dbo.td_policy_conflict_detection): one row per tenant for the last detection pass: the instants (lastDetectedAt, asOf, nextTransitionAt), the counts (subjects, eligibleDocuments, tieGroups, activePairs), the groups the detection could not measure (failedGroups, a JSON array whose element shape the table's column description owns), and the operator who started it (detectedBy). The table has createdAt / updatedAt but no createdBy / updatedBy.
4. TdGovernanceGoldenBenchmark (dbo.td_governance_golden_benchmark): Evaluation benchmark queries, expected citations (mssqlJson<string[]>), confidentiality levels, and active flags.

The append-only record table dbo.td_knowledge_governance_event has no model: it has no reader yet, and its writer uses raw SQL.

Key Features

• Strict DDL Alignment: Each model declares the columns of its table file in the same order, with the same physical names, SQL types, lengths, precisions and nullabilities, plus the named PK_* primary key.
  • The one type that differs is json: the models declare those columns with mssqlJson, whose SQL type is nvarchar(max), so a comparison with the DDL maps json to nvarchar(max).
  • Indexes, unique / foreign-key / CHECK constraints and defaults belong to the DDL only, so the models declare none. The insert type therefore requires every audit column although the DDL has defaults for them (sysutcdatetime(), and N'system' for the operator columns): a writer names its operator instead of falling back to system, which only marks an insert that named none.
• Bi-temporal Timestamps: datetimeoffset(7) for legal/business validity and transaction time; datetime2(7) for UTC audit, approval, detection and decision instants.
• Instant Precision: The columns hold 100 ns, and a model's JavaScript Date keeps neither that precision nor a column's offset.
  • A model read returns a Date cut to milliseconds. A datetimeoffset read also loses the offset, because a Date carries none.
  • A model write or comparison (values, set, eq(column, date)) binds the Date as SQL datetime, node-mssql's type for a Date parameter, because drizzle names no type: the value is rounded to 1/300 s (.005 is stored as .0066667, and .999 carries into the next second), a year before 1753 is refused, and a datetimeoffset receives the offset +00:00. A Date read from a row therefore does not in general compare equal to that row.
  • The two items above describe the driver's default options.useUTC: true. A pool configured with useUTC: false (the option, or useUTC in a connection string) writes and compares a Date by the host's local clock and reads a datetime2 by it too (a datetimeoffset read is unaffected), so every instant moves by the host's UTC offset: keep the default.
  • An instant the governance core carries (a UtcInstant, or the offset ISO 8601 text OffsetIsoInstantSchema accepts) is never written or compared through a model's Date. Write it in SQL (sysutcdatetime()), or bind its text and convert it in SQL: convert(datetime2(7), @p, 121) for a UtcInstant, and convert(datetimeoffset(7), @p) for offset ISO text bound for a datetimeoffset(7). Offset ISO text bound for a UTC datetime2(7) is first turned into a UtcInstant with the governance core's utcInstantOfOffsetIso.
  • Read it in SQL as text. A UtcInstant is convert(nchar(27), <column>, 121) for a UTC datetime2(7), and convert(nchar(27), convert(datetime2(7), switchoffset(<column>, '+00:00')), 121) for a datetimeoffset(7). Offset ISO text is, for a datetimeoffset(7), a text form of the column that keeps its offset, validated with OffsetIsoInstantSchema; for a UTC datetime2(7), it is the style-121 UtcInstant above, converted with the governance core's offsetIsoOfUtcInstant.
• Single Source of Truth (SSOT): The column vocabularies are the governance core's: AuthorityTier, ApprovalStatus, PolicyConflictSeverity, PolicyConflictResolutionStatus and ConfidentialityLevel come from @aiquants/governance-core, and TenantId comes from @aiquants/enrichment-core. This package re-exports them (export type) and declares no union of its own.
• JSON Column Safety: mssqlJson custom type with zero-allocation base type and fail-fast deserialization on malformed data.
• Row Version (mssqlRowVersion): A rowversion column that code spells as 16 lowercase hexadecimal digits, the spelling of the governance core's RowVersionSchema. SQL Server writes the column, so it is absent from the insert type and from the update builder's set. A comparison with a value (eq(TdDocumentAuthority.rowVersion, value)) binds the text through convert(binary(8), @p, 2).
• SHA-256 Digests: The digest columns TdPolicyConflicts.factsHash and .decidedFactsHash are binary(32) columns that code spells as 64 lowercase hexadecimal digits, the spelling of the governance core's Sha256HexSchema. Writes and comparisons with a value go through convert(binary(32), @p, 2). Their column type is internal to the package; reach it through the columns.
• No Host Byte Type: The byte columns read any Uint8Array (the driver's buffer is one, and a read spells exactly its bytes even when it is a view into a larger buffer) and write SQL, so the package and its type declarations need no Node Buffer.

Unsupported uses of the byte columns

The row-version and digest columns convert only what passes through the column with a value. Four uses fall outside that:

• Nested relational queries: drizzle's relational queries (db._query with with) read a related row through FOR JSON, which spells binary as base64 text. mssql has only the legacy relational engine, which hands that text to the driver read, so the read throws TypeError. Read these columns with the select builder, or from the table the relational query starts at.
• Raw SQL reads: A raw SQL read (execute(sql`…`)) does not pass through the column and returns the driver's bytes. Spell them with the column's mapFromDriverValue.
• Raw SQL comparisons and writes: A value interpolated into raw SQL (sql`${TdDocumentAuthority.rowVersion} = ${value}` ) is bound as the text itself, unconverted and unchecked. A comparison then compares text with bytes and no row matches (an optimistic update reports a false conflict), and a write does not store the bytes the text spells.
  • Bind the value as sql.param(value, TdDocumentAuthority.rowVersion) (or TdPolicyConflicts.factsHash / .decidedFactsHash). It renders convert(binary(n), @p, 2) and throws TypeError for a misspelt value before anything is bound.
  • Alternatively write the conversion yourself (convert(binary(8), ${value}, 2)), after validating the value with the governance core's RowVersionSchema / Sha256HexSchema.
• Placeholders: A prepared statement's placeholder (sql.placeholder) is not supported, and it does not fail on its own.
  • In a comparison (eq(column, sql.placeholder("v"))), drizzle renders the placeholder without the conversion and binds the filled text unchecked, so SQL Server compares text with bytes and no row matches (an optimistic update reports a false conflict).
  • In set / values, the column's conversion runs when the placeholder is filled and hands the driver a drizzle SQL object as the parameter value.
  • Write the conversion around the placeholder (sql`${TdDocumentAuthority.rowVersion} = convert(binary(8), ${sql.placeholder("rv")}, 2)` ), or bind the value itself.

Compatibility

The package is built and tested against drizzle-orm 1.0.0-rc.4; the peer range admits earlier 1.0.0 pre-releases that were not tested. The package relies on these rc.4 behaviours, and a test pins each:

• Column encoders that return SQL, which eq, set / values and sql.param(value, column) render in place of the parameter (the byte columns' convert(binary(n), @p, 2)).
• The HasGenerated type, which removes a generated column from the insert type and from the update builder's set type (mssqlRowVersion is generated only in its type).
• The insert builder's default for a column the insert omits. At run time this alone keeps a model insert from binding a value into row_version, which SQL Server refuses.
• The placeholder rendering the "Placeholders" item above describes: a comparison does not pass a placeholder through the column's encoder, and set / values pass the filled value through it.

The type declarations name Uint8Array<ArrayBufferLike>, which needs TypeScript 5.7 or later when a consumer checks library declarations (skipLibCheck: false).

Installation

pnpm add @aiquants/governance-drizzle @aiquants/governance-core @aiquants/enrichment-core drizzle-orm

Usage

import {
    TdDocumentAuthority,
    TdPolicyConflictDetection,
    TdPolicyConflicts,
    TdGovernanceGoldenBenchmark,
    type ApprovalStatus,
    type TdDocumentAuthoritySelect,
    type TdPolicyConflictDetectionSelect,
    type TdPolicyConflictsSelect,
} from "@aiquants/governance-drizzle"

License

MIT