cont.table_templates

Summary

The [cont].[table_templates] table defines the structure and metadata behaviour for CDM-managed tables. It acts as a schema blueprint for dynamic table creation, upsert controllers, checksum logic, indexing, and JSON payload generation.

All metadata-driven procedures—such as [cdm].[create_table], [cdm].[patch_upsert], and [upsert].[meta]—rely on this table to align logic with the CDM design pattern.

✅ Current Column Structure

Column	Description
`table_type`	Logical group the template belongs to (e.g. `meta`, `item`, `core`, `default`)
`column_name`	The name of the column to create or process
`data_type`	The SQL data type (e.g. `varchar(256)`, `datetime`)
`is_nullable`	`'YES'` or `'NO'` for nullability
`is_pk`	Flag to denote inclusion in primary key
`is_idx`	Flag for index creation
`is_checksum`	Flag for inclusion in checksum logic
`default_value`	Static default for column, if applicable
`column_order`	Numeric order for consistent column sequencing
`parameter_name`	Corresponding stored procedure param (for payload mapping)

💡 Notable Features in Use

Defaults for CDM metadata tables are cleanly separated via 'default' type.
Checksums only include key/value fields (as expected).
parameter_name is well-used to drive payload unpacking.

🔧 Suggestions to Extend Functionality

Suggestion	Purpose
`is_trigger` (YES/NO)	Control inclusion in insert triggers for ID/DEFAULT logic
`is_required` (YES/NO)	Separate flag for data presence validation outside of SQL constraints
`control_flags` (JSON)	Future-proofed config to include arbitrary rules (e.g. masking, audit)
`label_hint` (VARCHAR)	Add a human-readable column label for UI purposes or data dictionary
`sort_order_group` (INT)	Allows grouped ordering or layout in dashboards/forms

🧪 Example – Visual Table Composition

For [meta]:

OBJECT_SEQ     (PK, IDX, CS)
META_TYPE      (PK, IDX, CS)
ATTRIBUTE      (PK, IDX, CS)
CODE_VALUE     (     -,  -,  CS)
VALUE_METADATA (     -,  -,   -)

For [default]:

CREATE_DATE    (NOT NULL, default GETDATE())
EXPIRY_DATE    (IDX, default 2099-12-31)
SOURCE_SYSTEM  (default SYSTEM)
ACTIVE_FLAG    (default 1)
CHECKSUM       (IDX, CS)

📦 Suggested Supporting Views

You might benefit from a few metadata views:

vw.table_template_columns — list of columns grouped by table_type, ordered
vw.template_column_usage — where each column is used (pk, idx, cs, trigger, etc.)
vw.table_template_errors — checks for inconsistencies (e.g. no PK in template, bad column_order)

🗂 Suggested Additions to Template Types

You could consider defining these new table_type entries:

Table Type	Intended Use
`xref`	Cross-reference mappings between objects
`audit`	Auditable trail metadata for change logging
`label`	Human-readable labels, UI-facing metadata
`group`	Logical groupings or tag sets
`owner`	Data stewardship and governance mappings