# CLAUDE.md — GoodBooks DB Schema Conventions ## SQL Server · All Modules · GB4 / GB5 This document defines the **mandatory database schema conventions** for all GoodBooks DDL. Every new table, column, constraint, and index must follow these rules without exception. When reviewing or generating DDL, validate against every section below. --- ## 1. TABLE NAMING ### Prefix Rules | Prefix | Type | Description | Example | |--------|------|-------------|---------| | `M` | Master | Configuration / setup tables. App-generated PK. Carries full standard fields. | `MPRICEELEMENT`, `MBULKICE` | | `T` | Transaction | Business transaction records. IDENTITY PK. Carries full standard fields. | `TSALESORDER`, `TPURCHASEINVOICE` | | `L` | Log / Audit | Append-only records. IDENTITY PK. No update ever. | `LUSERAUDIT`, `LPOSTINGLOG` | | `S` | Staging | Temporary import/processing tables. IDENTITY PK. | `SBULKIMPORT` | ### General Rules - All UPPERCASE — no exceptions, no mixed case - No underscores within the table name itself - Detail / child tables append the parent concept as suffix — no separator: `MBULKICEDETAIL`, `TSALESORDERDETAIL` - Table names must be singular noun phrases: `MITEM` not `MITEMS` - Maximum 30 characters for table name (SQL Server object name limit awareness) --- ## 2. COLUMN NAMING ### Rules - All UPPERCASE — always - No underscores within the column name - Fully spelled out — no arbitrary abbreviations - Maximum 30 characters ### Established Abbreviations (only these are permitted) | Abbreviation | Meaning | |---|---| | `ID` | Surrogate key / foreign key identifier | | `SLNO` | Serial / sequence number within a parent | | `NO` | Number (document number, batch number) | | `QTY` | Quantity | | `AMT` | Amount | | `DT` | Date (when `DATE` suffix conflicts with reserved words) | | `DESC` | Description (when column name would be too long) | ### Column Patterns **Surrogate / foreign key columns** — always end in `ID`: ```sql BULKICEID INT -- own PK ITEMID INT -- FK to MITEM TENANTID INT -- FK to MCLIENT CREATEDBYID INT -- FK to MUSER ``` **Enum columns** — always `TINYINT`, always have a CHECK constraint, always have an inline comment: ```sql STATUS TINYINT NOT NULL CONSTRAINT DF_MFOO_STATUS DEFAULT 1, -- 0 Pending, 1 Active ... TOTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_TOTYPE DEFAULT 0, -- 0 MM, 1 Voucher ``` **Optional FK columns** (FK that may not always apply): - Declared `NOT NULL` with `DEFAULT -1` - The referenced table always has a physical dummy row with `ID = -1` representing "not applicable" - Never leave optional FKs as `NULL` — always use the `-1` sentinel: ```sql PARTYFIXEDACCOUNTID INT NOT NULL CONSTRAINT DF_MFOO_PARTYFIXEDACCOUNTID DEFAULT -1, CONTRAFIXEDACCOUNTID INT NOT NULL CONSTRAINT DF_MFOO_CONTRAFIXEDACCOUNTID DEFAULT -1, ``` **Descriptive / free-text columns** — nullable, no default: ```sql REMARKS NVARCHAR(1000), LINKEDFIELD NVARCHAR(100), POSTINGGROUP NVARCHAR(10), ``` --- ## 3. DATA TYPES | Use Case | Type | Notes | |----------|------|-------| | Surrogate PK / FK | `INT` | Never BIGINT unless proven necessary | | Short codes | `NVARCHAR(20)` | Unique codes, document numbers | | Names / labels | `NVARCHAR(200)` | Display names | | Descriptions | `NVARCHAR(1000)` | Remarks, notes | | Long text | `NVARCHAR(MAX)` | JSON, XML, large remarks — use sparingly | | Enums / flags | `TINYINT` | Max 255 values; always CHECK constrained | | Sequence within parent | `SMALLINT` | `SLNO`, `VERSION`, `SORTORDER` | | Monetary / rates | `NUMERIC(18,4)` | Always 4 decimal places | | Quantities | `NUMERIC(18,4)` | Same as monetary | | Percentages | `NUMERIC(9,4)` | | | Date + time | `DATETIME` | `CREATEDON`, `MODIFIEDON` — always UTC in app layer | | Date only | `DATE` | For business dates (posting date, due date) | | Boolean flags | `TINYINT` | `0 = No/False`, `1 = Yes/True`. Never BIT. | | Varchar (non-Unicode) | `VARCHAR` | Only for guaranteed ASCII content (legacy stored proc names, file paths). Prefer NVARCHAR. | --- ## 4. PRIMARY KEY STRATEGY | Table Type | Strategy | Declaration | |------------|----------|-------------| | Master (`M`) | **Application-generated** | `INT NOT NULL` — no IDENTITY | | Transaction (`T`) | **Application-generated** | `INT NOT NULL` — no IDENTITY | | Detail / child | **Application-generated** | `INT NOT NULL` — no IDENTITY | | Log (`L`) / Staging (`S`) | **IDENTITY** | `INT IDENTITY(1,1) NOT NULL` — these are never referenced as FKs | **Rationale:** Application-generated IDs allow the app layer to know the ID before insert (needed for multi-row batch operations, optimistic concurrency, and client-side reference). IDENTITY is only used where the record is terminal (never FK-referenced by another table). --- ## 5. CONSTRAINT NAMING — MANDATORY FORMAT All constraint names follow this exact pattern with underscore after the type prefix: ``` {TYPE}_{TABLENAME}_{COLUMNNAME(S)} ``` ### Types | Type | Prefix | Example | |------|--------|---------| | Primary Key | `PK` | `PK_MBULKICE_BULKICEID` | | Foreign Key | `FK` | `FK_MBULKICE_TENANTID` | | Check | `CK` | `CK_MBULKICE_STATUS` | | Unique | `UK` | `UK_MBULKICE_BULKICECODE_TENANTID` | | Default | `DF` | `DF_MBULKICE_STATUS` | ### Rules - Underscore after the type prefix — always: `FK_` not `FK` - Table name in constraint name must exactly match the actual table name - For multi-column constraints (UK, composite PK), list all column names joined by underscore - For FK constraints, use the **column name** (not the referenced table name): - ✅ `FK_MPRICEELEMENT_PARTYFIXEDACCOUNTID` - ❌ `FK_MPRICEELEMENT_MACCOUNT` (ambiguous when multiple FKs reference same table) - Default constraints **must always be named** — never use anonymous defaults - All constraint names must be unique across the entire database (prefix + tablename makes this safe) ### Full Constraint Block Example ```sql CONSTRAINT PK_MBULKICE_BULKICEID PRIMARY KEY (BULKICEID), CONSTRAINT UK_MBULKICE_BULKICECODE_TENANTID UNIQUE (BULKICECODE, TENANTID), CONSTRAINT UK_MBULKICE_BULKICENAME_TENANTID UNIQUE (BULKICENAME, TENANTID), CONSTRAINT FK_MBULKICE_CREATEDBYID FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MBULKICE_MODIFIEDBYID FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MBULKICE_TENANTID FOREIGN KEY (TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MBULKICE_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_MBULKICE_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)), CONSTRAINT CK_MBULKICE_TOTYPE CHECK (TOTYPE IN (0,1)), ``` --- ## 6. STANDARD FIELDS ### Master (`M`) and Transaction (`T`) Tables — Full Standard Block These fields are **mandatory** on every master and transaction header table. They appear **at the end of the column list**, immediately before the constraint block, in this exact order: ```sql -- STANDARD FIELDS VERSION SMALLINT NOT NULL CONSTRAINT DF_{TABLE}_VERSION DEFAULT 0, STATUS TINYINT NOT NULL CONSTRAINT DF_{TABLE}_STATUS DEFAULT 1, SORTORDER SMALLINT NOT NULL CONSTRAINT DF_{TABLE}_SORTORDER DEFAULT 9999, CREATEDBYID INT NOT NULL, CREATEDON DATETIME NOT NULL CONSTRAINT DF_{TABLE}_CREATEDON DEFAULT GETDATE(), MODIFIEDBYID INT NOT NULL, MODIFIEDON DATETIME NOT NULL CONSTRAINT DF_{TABLE}_MODIFIEDON DEFAULT GETDATE(), SOURCETYPE TINYINT NOT NULL CONSTRAINT DF_{TABLE}_SOURCETYPE DEFAULT 5, TENANTID INT NOT NULL CONSTRAINT DF_{TABLE}_TENANTID DEFAULT -1, ``` And the corresponding standard constraints at the end of the constraint block: ```sql CONSTRAINT FK_{TABLE}_CREATEDBYID FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_{TABLE}_MODIFIEDBYID FOREIGN KEY (MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_{TABLE}_TENANTID FOREIGN KEY (TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_{TABLE}_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_{TABLE}_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)), ``` ### Detail / Child Tables — No Standard Fields Detail tables (e.g. `MBULKICEDETAIL`, `TSALESORDERDETAIL`) do **not** carry standard audit fields. They carry only: - Their own PK - FK to the parent header - `SLNO` (sequence within parent) - Business columns - `REMARKS` if needed Audit trail for detail records is inherited from the parent header's `CREATEDBYID` / `MODIFIEDON`. ### Log (`L`) / Staging (`S`) Tables — Minimal Only `CREATEDBYID` and `CREATEDON` — no `MODIFIEDBYID`, no `STATUS`, no `VERSION`. --- ## 7. STANDARD ENUM VALUES These enum definitions are fixed and must never be changed or extended without a schema change notice. ### STATUS (all M and T tables) ``` 0 = Pending 1 = Active ← DEFAULT 2 = Deleted 3 = Amended 4 = Inactive 5 = Archived ``` CHECK: `STATUS IN (0,1,2,3,4,5)` ### SOURCETYPE (all M and T tables) ``` 1 = Framework 2 = Devadmin 3 = Impadmin 4 = Admin 5 = User ← DEFAULT ``` CHECK: `SOURCETYPE IN (1,2,3,4,5)` ### TINYINT Boolean pattern (flags, toggles) ``` 0 = No / False / Disabled 1 = Yes / True / Enabled ``` CHECK: `COLUMNNAME IN (0,1)` --- ## 8. COLUMN ORDER WITHIN A TABLE Columns must appear in this order: 1. Own PK column(s) 2. Parent FK column(s) — for detail tables 3. `SLNO` — if present 4. Business columns (domain-specific, in logical grouping) 5. Standard fields block (VERSION → TENANTID) — master/transaction tables only 6. *(blank line)* 7. Constraint block --- ## 9. CONSTRAINT BLOCK ORDER Within the constraint block, constraints must appear in this order: 1. `PK_` — Primary Key 2. `UK_` — Unique constraints 3. `FK_` — Foreign Keys (business columns first, then standard field FKs: CREATEDBYID, MODIFIEDBYID, TENANTID last) 4. `CK_` — Check constraints (business columns first, then STATUS, SOURCETYPE last) 5. *(Default constraints are declared inline on the column — not in the constraint block)* --- ## 10. ENUM COLUMN DOCUMENTATION Every `TINYINT` enum column **must** have an inline comment listing all valid values: ```sql DETAILTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_DETAILTYPE DEFAULT 0, -- 0 Base, 1 Debit, 2 Credit TOTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_TOTYPE DEFAULT 0, -- 0 MM, 1 Voucher EDITABLE TINYINT NOT NULL CONSTRAINT DF_MFOO_EDITABLE DEFAULT 0, -- 0 Yes, 1 No ``` - Comment format: `-- {value} {label}, {value} {label}, ...` - Place comment on the same line as the column definition - If values are still being finalised, use `-- ❓ TBD` — never leave blank or use `--?` - The CHECK constraint must list exactly the values documented in the comment --- ## 11. COMPLETE DDL TEMPLATE ### Master Table Template ```sql CREATE TABLE [DBO].MFOO ( -- PK FOOID INT NOT NULL, -- BUSINESS COLUMNS FOOCODE NVARCHAR(20) NOT NULL, FOONAME NVARCHAR(200) NOT NULL, FOOTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_FOOTYPE DEFAULT 0, -- 0 TypeA, 1 TypeB PARENTID INT NOT NULL CONSTRAINT DF_MFOO_PARENTID DEFAULT -1, -- -1 = not applicable DEFAULTVALUE NUMERIC(18,4) NOT NULL CONSTRAINT DF_MFOO_DEFAULTVALUE DEFAULT 0, ISACTIVE TINYINT NOT NULL CONSTRAINT DF_MFOO_ISACTIVE DEFAULT 1, -- 0 No, 1 Yes REMARKS NVARCHAR(1000), -- STANDARD FIELDS VERSION SMALLINT NOT NULL CONSTRAINT DF_MFOO_VERSION DEFAULT 0, STATUS TINYINT NOT NULL CONSTRAINT DF_MFOO_STATUS DEFAULT 1, SORTORDER SMALLINT NOT NULL CONSTRAINT DF_MFOO_SORTORDER DEFAULT 9999, CREATEDBYID INT NOT NULL, CREATEDON DATETIME NOT NULL CONSTRAINT DF_MFOO_CREATEDON DEFAULT GETDATE(), MODIFIEDBYID INT NOT NULL, MODIFIEDON DATETIME NOT NULL CONSTRAINT DF_MFOO_MODIFIEDON DEFAULT GETDATE(), SOURCETYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_SOURCETYPE DEFAULT 5, TENANTID INT NOT NULL CONSTRAINT DF_MFOO_TENANTID DEFAULT -1, CONSTRAINT PK_MFOO_FOOID PRIMARY KEY (FOOID), CONSTRAINT UK_MFOO_FOOCODE_TENANTID UNIQUE (FOOCODE, TENANTID), CONSTRAINT FK_MFOO_PARENTID FOREIGN KEY (PARENTID) REFERENCES MPARENT(PARENTID), CONSTRAINT FK_MFOO_CREATEDBYID FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MFOO_MODIFIEDBYID FOREIGN KEY (MODIFIEDBYID)REFERENCES MUSER(USERID), CONSTRAINT FK_MFOO_TENANTID FOREIGN KEY (TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MFOO_FOOTYPE CHECK (FOOTYPE IN (0,1)), CONSTRAINT CK_MFOO_ISACTIVE CHECK (ISACTIVE IN (0,1)), CONSTRAINT CK_MFOO_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_MFOO_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)) ) GO ``` ### Detail Table Template ```sql CREATE TABLE [DBO].MFOODETAIL ( -- PK + PARENT FK FOODETAILID INT NOT NULL, FOOID INT NOT NULL, -- SEQUENCE SLNO SMALLINT NOT NULL CONSTRAINT DF_MFOODETAIL_SLNO DEFAULT 1, -- BUSINESS COLUMNS ITEMID INT NOT NULL, QTY NUMERIC(18,4) NOT NULL CONSTRAINT DF_MFOODETAIL_QTY DEFAULT 0, RATE NUMERIC(18,4) NOT NULL CONSTRAINT DF_MFOODETAIL_RATE DEFAULT 0, REMARKS NVARCHAR(200), CONSTRAINT PK_MFOODETAIL_FOODETAILID PRIMARY KEY (FOODETAILID), CONSTRAINT FK_MFOODETAIL_FOOID FOREIGN KEY (FOOID) REFERENCES MFOO(FOOID), CONSTRAINT FK_MFOODETAIL_ITEMID FOREIGN KEY (ITEMID) REFERENCES MITEM(ITEMID) ) GO ``` ### Log Table Template ```sql CREATE TABLE [DBO].LFOOAUDIT ( LFOOAUDITID INT IDENTITY(1,1) NOT NULL, FOOID INT NOT NULL, CHANGEDCOLUMN NVARCHAR(100) NOT NULL, OLDVALUE NVARCHAR(MAX), NEWVALUE NVARCHAR(MAX), CREATEDBYID INT NOT NULL, CREATEDON DATETIME NOT NULL CONSTRAINT DF_LFOOAUDIT_CREATEDON DEFAULT GETDATE(), CONSTRAINT PK_LFOOAUDIT_LFOOAUDITID PRIMARY KEY (LFOOAUDITID), CONSTRAINT FK_LFOOAUDIT_FOOID FOREIGN KEY (FOOID) REFERENCES MFOO(FOOID), CONSTRAINT FK_LFOOAUDIT_CREATEDBYID FOREIGN KEY (CREATEDBYID) REFERENCES MUSER(USERID) ) GO ``` --- ## 12. ANTI-PATTERNS — NEVER DO THESE ```sql -- ❌ Lowercase table or column names create table mfoo (fooid int, fooname nvarchar(200)) -- ❌ Underscore in table or column name CREATE TABLE M_FOO (FOO_ID INT, FOO_NAME NVARCHAR(200)) -- ❌ Anonymous default constraint SORTORDER SMALLINT NOT NULL DEFAULT 9999 -- ❌ Constraint name without type prefix underscore CONSTRAINT FKMFOO_TENANTID FOREIGN KEY ... CONSTRAINT DFMFOO_STATUS DEFAULT 1 -- ❌ Optional FK as NULL instead of -1 sentinel PARTYFIXEDACCOUNTID INT NULL -- ❌ BIT for boolean — always TINYINT ISACTIVE BIT NOT NULL DEFAULT 1 -- ❌ Enum column without CHECK constraint DETAILTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_DETAILTYPE DEFAULT 0 -- (missing: CONSTRAINT CK_MFOO_DETAILTYPE CHECK (DETAILTYPE IN (0,1,2))) -- ❌ Enum column without inline comment DETAILTYPE TINYINT NOT NULL CONSTRAINT DF_MFOO_DETAILTYPE DEFAULT 0, -- ❌ IDENTITY on a master or transaction table PK FOOID INT IDENTITY(1,1) NOT NULL -- ❌ Standard fields on a detail table -- (MBULKICEDETAIL must NOT have VERSION, STATUS, CREATEDBYID etc.) -- ❌ FK constraint named after referenced table (ambiguous) CONSTRAINT FK_MFOO_MACCOUNT FOREIGN KEY (PARTYFIXEDACCOUNTID) ... -- ❌ Unfinalised enum left as --? ELEMENTNATURE TINYINT NOT NULL CONSTRAINT DF_MFOO_ELEMENTNATURE DEFAULT 0, --? -- ❌ Missing GO terminator CREATE TABLE [DBO].MFOO ( ... ) -- (no GO) ``` --- ## 13. KNOWN ERRORS IN EXISTING DDL (to fix on next touch) | Table | Error | Fix | |-------|-------|-----| | `MPRICEELEMENT` | Double comma: `DEFAULT -1, ,` on `PARTYFIXEDACCOUNTID` line | Remove extra comma | | `MPRICEELEMENT` | `ELEMENTNATURE` comment is `--?` — values not documented | Document valid enum values | | `MPRICEELEMENT` | Constraint naming inconsistent — uses `FKMPRICEELEMENT_` style | Rename to `FK_MPRICEELEMENT_` style | | `MBULKICE` | Two UNIQUE constraints both on `(BULKICECODE, TENANTID)` | Second should be `UK_MBULKICE_BULKICENAME_TENANTID` on `(BULKICENAME, TENANTID)` | | `MBULKICEDETAIL` | `PK_MBULKICEDETAIL` constraint uses `BULKICEID` instead of `BULKICEDETAILID` | Fix to `PRIMARY KEY (BULKICEDETAILID)` | | `MBULKICEDETAIL` | `BULKICEDETAILID` and `BULKICEID` not declared `NOT NULL` | Add `NOT NULL` | | `MBULKICEDETAIL` | `TEMPTABLENAME SMALLINT` — column name implies VARCHAR, type is SMALLINT | Clarify intended type and rename to match | | `MBULKICEDETAIL` | Constraint naming inconsistent — missing `_` after `PK`, `FK`, `CK` in some | Standardise to `PK_`, `FK_`, `CK_` | --- ## 14. REVIEW CHECKLIST When generating or reviewing any DDL, verify each point: **Naming** - [ ] Table name is all UPPERCASE, no underscores, correct prefix (M/T/L/S) - [ ] All column names UPPERCASE, no underscores, no unapproved abbreviations - [ ] All constraint names follow `{TYPE}_{TABLENAME}_{COLUMN}` pattern with underscore after prefix **Structure** - [ ] Column order: PK → parent FK → SLNO → business columns → standard fields → constraints - [ ] Constraint block order: PK → UK → FK (business) → FK (standard) → CK (business) → CK (STATUS, SOURCETYPE) - [ ] Standard fields block present on M and T tables, absent on detail tables **Data Types** - [ ] Enums use `TINYINT` — not INT, not SMALLINT - [ ] Monetary/qty values use `NUMERIC(18,4)` - [ ] Boolean flags use `TINYINT` — not BIT - [ ] PK is `INT` (app-generated) or `INT IDENTITY(1,1)` (log/staging only) **Constraints** - [ ] Every column with a DEFAULT has a **named** default constraint `DF_{TABLE}_{COLUMN}` - [ ] Every `TINYINT` enum has a `CK_` check constraint listing all valid values - [ ] Every enum column has an inline comment listing values - [ ] Optional FK columns use `NOT NULL DEFAULT -1` (not NULL) - [ ] Every FK constraint is named after the **column**, not the referenced table - [ ] `GO` terminator present after each `CREATE TABLE` **Standard fields (M and T tables only)** - [ ] `VERSION`, `STATUS`, `SORTORDER`, `CREATEDBYID`, `CREATEDON`, `MODIFIEDBYID`, `MODIFIEDON`, `SOURCETYPE`, `TENANTID` all present - [ ] `STATUS DEFAULT 1`, `SORTORDER DEFAULT 9999`, `SOURCETYPE DEFAULT 5`, `TENANTID DEFAULT -1` - [ ] `CK_{TABLE}_STATUS CHECK (STATUS IN (0,1,2,3,4,5))` present - [ ] `CK_{TABLE}_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5))` present - [ ] `FK_{TABLE}_CREATEDBYID`, `FK_{TABLE}_MODIFIEDBYID`, `FK_{TABLE}_TENANTID` all present