# CodeDefine & CodeDefine Segment — Feature & Functionality Specification

**Module:** Framework (Cross-Cutting)
**Version:** GB5 (GoodBooks ERP Platform)
**Document Type:** Feature Specification — Technical & Functional
**Audience:** Product Owners, Business Analysts, Frontend Developers, Backend Engineers, QA, DevOps

---

## Table of Contents

1. [Executive Summary](#1-executive-summary)
2. [Business Context & Stakeholders](#2-business-context--stakeholders)
3. [Functional Overview](#3-functional-overview)
4. [Code Generation Types — Detailed Behavior](#4-code-generation-types--detailed-behavior)
5. [CodeDefine — Feature Specification](#5-codedefine--feature-specification)
6. [CodeDefine Segment — Feature Specification](#6-codedefine-segment--feature-specification)
7. [Code Generation Engine](#7-code-generation-engine)
8. [Data Model](#8-data-model)
9. [API Reference](#9-api-reference)
10. [Architecture & Layer Responsibilities](#10-architecture--layer-responsibilities)
11. [Caching Strategy](#11-caching-strategy)
12. [Security & Multi-Tenancy](#12-security--multi-tenancy)
13. [Observability & Audit](#13-observability--audit)
14. [Integration Points](#14-integration-points)
15. [Error Handling & Validations](#15-error-handling--validations)
16. [Database Design & Indexing](#16-database-design--indexing)
17. [Constraints & Known Limitations](#17-constraints--known-limitations)
18. [Glossary](#18-glossary)

---

## 1. Executive Summary

**CodeDefine** is a framework-level configuration system in GoodBooks GB5 that governs how unique identification codes are generated for every business entity in the ERP (employees, customers, products, invoices, purchase orders, etc.).

Rather than hard-coding numbering rules into each module, CodeDefine externalises the code-generation strategy into a configurable, database-driven setup. System administrators and business owners can define, per entity and per category, whether codes are:

- automatically incremented with a prefix/suffix,
- entered manually by users,
- derived from a mathematical formula,
- composed by assembling multiple configurable segments (the most flexible option).

**CodeDefine Segment** is the child configuration that applies when the generation type is **Segment-Based**: it lists, in order, each piece that makes up the final code — fixed text, entity field values, lookup tokens, sequential counters, and derived expressions.

Together, these two features eliminate the need for module-specific numbering code and provide a single, auditable, multi-tenant-safe control point for all entity codes across the platform.

---

## 2. Business Context & Stakeholders

### 2.1 Why CodeDefine Exists

Every ERP entity (Employee, Supplier, Customer, Product, Sales Order, etc.) needs a unique, human-readable identifier. Historically these are hard-coded per module, leading to:

- duplicate logic across modules,
- inability to customise without a code change,
- no audit trail for numbering rules,
- inconsistent behaviour across multi-tenant deployments.

CodeDefine solves all four problems by making numbering a data-driven concern in the framework layer.

### 2.2 Stakeholders

| Stakeholder | Role | How This Feature Affects Them |
|---|---|---|
| **System Administrator** | Configures numbering rules for each entity | Primary user of the CodeDefine UI; defines generation type, prefix, suffix, ranges, and segments |
| **Business Owner / Implementation Consultant** | Defines business naming conventions | Decides prefix patterns (e.g., "EMP", "INV", "PO"), resets sequences per financial year |
| **End User (Operator / Data Entry)** | Creates entities (employees, products, etc.) | Sees or enters the code depending on generation type; unaware of CodeDefine internals |
| **Module Developer (Backend)** | Writes new modules that need codes | Calls `CodeGeneration.AssignCode<T>()` — no per-module numbering code needed |
| **Frontend Developer** | Builds entity creation/edit forms | Needs to know whether a field is auto-filled, editable (Manual), or hidden |
| **QA Engineer** | Tests entity creation flows | Verifies codes are unique, sequential, correctly formatted, and within configured bounds |
| **DevOps / DBA** | Manages database, migrations | Owns MCODEDEFINE and MCODEDEFINESEGMENT tables; monitors CATLASTNO sequence state |
| **Product Owner** | Prioritises and owns the feature | Ensures CodeDefine covers all entity types; tracks segment-type adoption |
| **Auditor / Compliance Officer** | Reviews change history | Reads EventLog for all CodeDefine saves/changes; validates sequence continuity |

---

## 3. Functional Overview

### 3.1 What a User Can Do

| Action | Where | Who |
|---|---|---|
| Define a code rule for an entity | CodeDefine setup screen | System Administrator |
| Choose generation type (Auto / Manual / Formula / Hidden / Segment) | CodeDefine form | System Administrator |
| Set prefix, suffix, start number, end number, code length | CodeDefine form | System Administrator |
| Add, reorder, or delete segments for Segment-type codes | CodeDefine Segment sub-grid | System Administrator |
| View the list of all defined code rules | CodeDefine list / picklist | System Administrator, Consultant |
| Delete an unused code rule | CodeDefine list | System Administrator |
| See auto-generated codes appear on entity forms | Employee, Customer, Product forms, etc. | End User (read-only) |
| Enter a code manually on entity forms | Entity forms (when type = Manual) | End User |

### 3.2 What Happens Automatically

- When an entity record is created, the framework calls the code generation engine, which reads the CodeDefine row for that entity and produces a code according to its type.
- For Auto and Hidden types, the sequence counter (`CATLASTNO`) is incremented and persisted atomically.
- For Segment types, the stored procedure `PROC_GENERATE_CODE` is invoked, which assembles all configured segments in order.
- For Formula types, a formula expression is evaluated to produce the next sequential base number.

---

## 4. Code Generation Types — Detailed Behavior

### Type 0 — AUTO

The system auto-increments a counter and prepends a prefix and/or appends a suffix.

**Behaviour:**
- Reads `CATLASTNO` (current last number) from `MCODEDEFINE`.
- Increments by 1.
- Pads with leading zeros to match `CODESIZE`.
- Prepends `CATPREFIX` and appends `CATSUFFIX`.
- Validates the result does not exceed `CATENDNO`.
- Persists the new `CATLASTNO`.

**Example configuration:**
- Prefix: `EMP`, Start: 1, End: 9999, CodeSize: 7 → produces `EMP0001`, `EMP0002`, …

**Frontend behaviour:** Field is read-only; value appears after save.

---

### Type 1 — MANUAL

The user provides the code themselves.

**Behaviour:**
- No counter is incremented.
- The framework validates:
  - Code length does not exceed `CODESIZE`.
  - Code starts with `CATPREFIX` (if set).
  - Code ends with `CATSUFFIX` (if set).
- No stored procedure is called.

**Frontend behaviour:** Field is editable and required.

---

### Type 2 — FORMULA

A formula expression (stored in `MFORMULA`) is evaluated to derive the numeric base; prefix/suffix are applied on top.

**Behaviour:**
- Loads the formula record via `FORMULAID`.
- Evaluates the expression (e.g., fiscal-year-based calculation).
- Uses the result as the next number base, same as AUTO from there.

**Frontend behaviour:** Field is read-only; value appears after save.

---

### Type 3 — HIDDEN

Functionally identical to AUTO. The code is generated automatically but typically not displayed in the UI (used for internal reference codes).

**Frontend behaviour:** Field is hidden or suppressed.

---

### Type 4 — SEGMENT

The most flexible type. The code is assembled from an ordered list of segments defined in `MCODEDEFINESEGMENT`.

**Behaviour:**
- The entity DTO is serialised to JSON.
- `PROC_GENERATE_CODE` is called with `@CategoryCode` and `@InputJson`.
- The stored procedure iterates segments in `SLNO` order and builds the code piece by piece.
- Each segment is separated by the configured separator character.
- The result is returned as `@GeneratedCode`.

**Segment sub-types inside Type 4:**

| Segment Type | Value | Source | Example output |
|---|---|---|---|
| FIXED | 0 | Literal string in `SOURCEFIELD` | `INV` |
| ATTRIBUTE | 1 | JSON key from entity DTO via `SOURCEFIELD` | `2026` (from `FiscalYear` field) |
| LOOKUP | 2 | Token resolved from `MCODETOKEN` table using `LOOKUPTABLE` | `MUM` (branch code) |
| SEQUENCE | 3 | Auto-increment counter (`CATLASTNO`), padded to 5 digits | `00042` |
| DERIVED | 4 | Derived expression (future use) | — |

**Assembled example:** `INV-MUM-2026-00042`

**Frontend behaviour:** Field is read-only; value appears after save.

---

## 5. CodeDefine — Feature Specification

### 5.1 Functional Description

A **CodeDefine** record represents a complete numbering rule for one entity category. Multiple CodeDefine rows can exist for the same entity if the entity has multiple categories (e.g., a product may have different code rules for Finished Goods vs. Raw Material categories).

### 5.2 Fields & Meaning

| Field | Type | Description |
|---|---|---|
| `CodeDefineId` | int (PK) | Unique identifier; system-assigned |
| `EntityId` | int (FK → MENTITY) | The entity this rule applies to |
| `CategoryCode` | string | Short code identifying this rule's category (e.g., `EMP`, `CUST-RET`) |
| `CategoryName` | string | Human-readable name for the category |
| `GenerationType` | int (0–4) | Generation strategy (see Section 4) |
| `CatPrefix` | string | Prefix prepended to the numeric part |
| `CatSuffix` | string | Suffix appended after the numeric part |
| `FormulaId` | int? | FK to MFORMULA — used when GenerationType = 2 |
| `CatStartNo` | long | Starting number for the sequence |
| `CatEndNo` | long | Maximum allowed sequence number (overflow guard) |
| `CatLastNo` | long | Current last-used sequence number (incremented on each generation) |
| `CodeSize` | int | Maximum total length of the generated code |
| `IsActive` | bool | Whether this rule is currently in use |
| `CreatedBy` / `ModifiedBy` | int | User audit trail |
| `CreatedOn` / `ModifiedOn` | datetime | Timestamp audit trail |

### 5.3 Business Rules

1. `CategoryCode` and `CategoryName` are mandatory.
2. `CatEndNo` must be greater than `CatStartNo`.
3. `CatLastNo` is managed by the system — never set manually by users.
4. When `GenerationType = 4` (Segment), at least one segment must exist in `MCODEDEFINESEGMENT`.
5. When `GenerationType = 2` (Formula), `FormulaId` must reference a valid formula.
6. Only one active CodeDefine per `EntityId + CategoryCode` combination is allowed.
7. Deleting a CodeDefine that has been used (i.e., `CatLastNo > CatStartNo`) should be blocked by the consuming module's business rules (the framework itself performs a soft delete pattern).

### 5.4 Save Behaviour (Create vs Update)

**Create (CodeDefineId = 0):**
- IDs for the header and all child segments are reserved in a single batch call to the AutoNumber service.
- Header record and all segment records are inserted in one database transaction.
- `CreatedById` and `CreatedOn` are stamped from `LoginDTO`.

**Update (CodeDefineId > 0):**
- IDs are reserved only for any new segments (SegmentId = 0).
- Existing segments' IDs are preserved.
- The update uses a delete-and-reinsert pattern for segments: all existing segments for the CodeDefineId are deleted and then all segments (including unchanged ones) are re-inserted. This ensures clean ordering without orphan records.
- `ModifiedById` and `ModifiedOn` are updated.

---

## 6. CodeDefine Segment — Feature Specification

### 6.1 Functional Description

A **CodeDefine Segment** is one building block in a Segment-based code. Segments are ordered by their sequence number (`SLNO`) and are assembled left-to-right to form the final code.

Segments are managed as a child list beneath a CodeDefine header. They can be added, reordered, or deleted independently via their own endpoints, or saved wholesale as part of a CodeDefine save operation.

### 6.2 Fields & Meaning

| Field | Type | Description |
|---|---|---|
| `SegmentId` | int (PK) | Unique identifier; system-assigned |
| `CodeDefineId` | int (FK) | Parent CodeDefine rule |
| `SlNo` | int | Display/assembly order (1, 2, 3, …) |
| `SegmentType` | int (0–4) | Type of segment (Fixed / Attribute / Lookup / Sequence / Derived) |
| `SourceField` | string | Literal value (Fixed), JSON key (Attribute), or blank |
| `LookupTable` | string? | Table name for Lookup-type segment token resolution |
| `Prefix` | string? | Text to prepend to this segment's value |
| `Suffix` | string? | Text to append to this segment's value |
| `IsMandatory` | bool | Whether this segment must produce a value (validation) |
| `DefaultValue` | string? | Fallback value if the segment source is empty |
| `Format` | int | 0 = None, 1 = UPPER (uppercase), 2 = PADLEFT (zero-pad) |
| `Separator` | string? | Character(s) placed after this segment before the next (e.g., `-`, `/`) |
| `IsActive` | bool | Whether this segment participates in generation |

### 6.3 Business Rules

1. `SourceField` is mandatory for Attribute-type segments (it identifies the JSON key to extract from the entity DTO).
2. `SlNo` is automatically reassigned sequentially (1, 2, 3, …) by the BLL on every save, regardless of user-supplied values.
3. A Sequence-type segment (`SegmentType = 3`) is the only segment that increments `CATLASTNO` on the parent CodeDefine.
4. The separator on the last segment in the list is ignored (no trailing separator in the generated code).
5. When `IsMandatory = true` and the resolved value is empty/null, the stored procedure raises an error.
6. The Format value `PADLEFT` pads the resolved value with leading zeros up to 5 digits by default.

### 6.4 Worked Example — Segment Assembly

**Entity:** Purchase Invoice
**CodeDefine:** GenerationType = 4, CategoryCode = `PI`
**Segments:**

| SlNo | SegmentType | SourceField / Source | Separator | Example Output |
|---|---|---|---|---|
| 1 | FIXED (0) | `PI` | `-` | `PI` |
| 2 | LOOKUP (2) | Branch table | `-` | `MUM` |
| 3 | ATTRIBUTE (1) | `FiscalYear` (from DTO) | `-` | `2026` |
| 4 | SEQUENCE (3) | — (auto-counter) | _(none)_ | `00042` |

**Result:** `PI-MUM-2026-00042`

---

## 7. Code Generation Engine

### 7.1 Entry Point

All modules call the shared service `CodeGeneration.AssignCode<T>()` when creating a new entity.

```
AssignCode<EmployeeDTO>(entityName: "Employee", obj: employeeDto, loginDto: login)
```

The method works via reflection — it discovers the code property (`EmployeeCode`) on the DTO by convention (`{EntityName}Code`), determines the relevant CodeDefine record, generates the code, and assigns it back to the DTO before the DAL save call.

### 7.2 Entity-to-CodeDefine Matching

1. Load entity metadata from `MENTITY` by entity name.
2. Load all CodeDefine rows for that EntityId.
3. If multiple CodeDefines exist (multiple categories), determine the applicable one via a category member ID (e.g., `EmployeeCategoryId` property on the DTO, resolved via reflection).
4. Delegate to the generation type handler.

### 7.3 Generation Flow Diagram

```
Entity DTO created (e.g., EmployeeDTO)
        │
        ▼
CodeGeneration.AssignCode<T>()
        │
        ▼
Load MENTITY → Load MCODEDEFINE rows for entity
        │
        ├─ GenerationType = 0 (AUTO)   ──► GenerateAutoCodeAsync()
        │                                       ├─ Read CATLASTNO
        │                                       ├─ Increment + format
        │                                       ├─ Validate ≤ CATENDNO
        │                                       └─ Persist CATLASTNO
        │
        ├─ GenerationType = 1 (MANUAL) ──► ValidateManualCode()
        │                                       ├─ Check length ≤ CODESIZE
        │                                       ├─ Check prefix/suffix
        │                                       └─ Return as-is
        │
        ├─ GenerationType = 2 (FORMULA)──► GenerateFormulaBasedCodeAsync()
        │                                       ├─ Load MFORMULA expression
        │                                       ├─ Evaluate formula
        │                                       └─ Apply as AUTO from computed base
        │
        ├─ GenerationType = 3 (HIDDEN) ──► Same as AUTO
        │
        └─ GenerationType = 4 (SEGMENT)──► GenerateSegmentCodeAsync()
                                                ├─ Serialise DTO → JSON
                                                └─ EXEC PROC_GENERATE_CODE
                                                        ├─ Load segments (order by SLNO)
                                                        ├─ Per segment:
                                                        │   ├─ FIXED → literal
                                                        │   ├─ ATTRIBUTE → extract from JSON
                                                        │   ├─ LOOKUP → MCODETOKEN lookup
                                                        │   └─ SEQUENCE → increment CATLASTNO
                                                        └─ Concatenate → @GeneratedCode
```

### 7.4 Stored Procedure: PROC_GENERATE_CODE

Handles all Segment-type code assembly at the database level to ensure atomicity when updating `CATLASTNO`.

| Parameter | Direction | Description |
|---|---|---|
| `@CategoryCode` | IN | Identifies the CodeDefine row |
| `@InputJson` | IN | Entity DTO serialised as JSON string |
| `@GeneratedCode` | OUT | The assembled code string returned to C# |

The procedure is wrapped in a `TRY/CATCH` — if it raises an error, it returns `NULL` instead of propagating the exception, allowing C# to detect failure and fall back gracefully.

---

## 8. Data Model

### 8.1 MCODEDEFINE (Header table)

```sql
MCODEDEFINE (
    CODEDEFINEID     INT           PRIMARY KEY,
    ENTITYID         INT           NOT NULL,      -- FK → MENTITY
    CATEGORYCODE     VARCHAR(50)   NOT NULL,
    CATEGORYNAME     VARCHAR(200)  NOT NULL,
    GENERATIONTYPE   SMALLINT      NOT NULL,       -- 0=Auto 1=Manual 2=Formula 3=Hidden 4=Segment
    CATPREFIX        VARCHAR(20)   NULL,
    CATSUFFIX        VARCHAR(20)   NULL,
    FORMULAID        INT           NULL,           -- FK → MFORMULA (type 2 only)
    CATSTARTNO       BIGINT        NOT NULL DEFAULT 1,
    CATENDNO         BIGINT        NOT NULL DEFAULT 999999999,
    CATLASTNO        BIGINT        NOT NULL DEFAULT 0,
    CODESIZE         INT           NOT NULL DEFAULT 20,
    ISACTIVE         BIT           NOT NULL DEFAULT 1,
    CREATEDON        DATETIME      NOT NULL,
    CREATEDBYID      INT           NOT NULL,
    MODIFIEDON       DATETIME      NULL,
    MODIFIEDBYID     INT           NULL,
    DATABASENAME     VARCHAR(100)  NOT NULL        -- tenant discriminator
)
```

**Required index:** `IX_MCODEDEFINE_ENTITY (ENTITYID, CATEGORYCODE, DATABASENAME) INCLUDE (GENERATIONTYPE, CATLASTNO, CATPREFIX, CATSUFFIX, CODESIZE)`

### 8.2 MCODEDEFINESEGMENT (Detail table)

```sql
MCODEDEFINESEGMENT (
    SEGMENTID        INT           PRIMARY KEY,
    CODEDEFINEID     INT           NOT NULL,       -- FK → MCODEDEFINE
    SLNO             INT           NOT NULL,
    SEGMENTTYPE      SMALLINT      NOT NULL,       -- 0=Fixed 1=Attribute 2=Lookup 3=Sequence 4=Derived
    SOURCEFIELD      VARCHAR(200)  NULL,
    LOOKUPTABLE      VARCHAR(200)  NULL,
    PREFIX           VARCHAR(50)   NULL,
    SUFFIX           VARCHAR(50)   NULL,
    ISMANDATORY      BIT           NOT NULL DEFAULT 1,
    DEFAULTVALUE     VARCHAR(200)  NULL,
    FORMAT           SMALLINT      NOT NULL DEFAULT 0,  -- 0=None 1=Upper 2=PadLeft
    SEPARATOR        VARCHAR(10)   NULL,
    ISACTIVE         BIT           NOT NULL DEFAULT 1,
    CREATEDON        DATETIME      NOT NULL,
    CREATEDBYID      INT           NOT NULL,
    MODIFIEDON       DATETIME      NULL,
    MODIFIEDBYID     INT           NULL,
    DATABASENAME     VARCHAR(100)  NOT NULL
)
```

**Required index:** `IX_MCODEDEFINESEGMENT_CODEDEFINEID (CODEDEFINEID, SLNO) INCLUDE (SEGMENTTYPE, SOURCEFIELD, SEPARATOR, FORMAT, ISMANDATORY, DEFAULTVALUE)`

### 8.3 Entity Relationship

```
MENTITY (1) ──────── (N) MCODEDEFINE (1) ──────── (N) MCODEDEFINESEGMENT
                              │
                              └── (0..1) MFORMULA
```

---

## 9. API Reference

### 9.1 CodeDefine Endpoints

#### GET /CodeDefine/GetCodeDefine

Retrieves CodeDefine configuration for an entity, optionally filtered by category code.

| Parameter | Type | Location | Required | Description |
|---|---|---|---|---|
| `EntityCode` | string | Query | Yes | Entity name (e.g., `Employee`, `Customer`) |
| `CategoryCode` | string | Query | No | Specific category to retrieve |
| Login headers | — | Header | Yes | Multi-tenant context |

**Response:** `ResponseStandardDTO<object>` wrapping a JSON-serialised list of `CodeDefineDTO`.

**Special behaviour:** When `CategoryCode` is supplied, the DAL attempts to call `PROC_GENERATE_CODE` first. If the procedure returns a code, that code is returned directly (used for real-time code preview on entity forms). If the procedure returns null, the CodeDefine metadata is returned.

**Caching:** None — always returns a fresh code to ensure sequence continuity.

---

#### POST /CodeDefine/SaveCodeDefine

Creates or updates a CodeDefine rule along with all its segments.

| Body | Type | Description |
|---|---|---|
| `CodeDefineDTO` | JSON body | Full CodeDefine including embedded `Segments` list |

**Response:** Success message with the assigned `CodeDefineId`.

**Idempotency:** If `CodeDefineId = 0`, a new record is created. If `CodeDefineId > 0`, the existing record is updated (segments use delete-and-reinsert).

---

#### DELETE /CodeDefine/DeleteCodeDefine

Soft-deletes a CodeDefine record.

| Parameter | Type | Location | Required |
|---|---|---|---|
| `CodeDefineId` | int | Query | Yes |

**Response:** Delete success message or not-found message.

---

#### POST /CodeDefine/GetSelectListCodeDefine

Returns a paginated picklist of CodeDefine records for dropdown/search use.

| Parameter | Type | Location | Description |
|---|---|---|---|
| `FirstNumber` | int | Query | Pagination offset |
| `MaxResult` | int | Query | Page size |
| `CriteriaDTO` | JSON body | Body | Search/filter criteria |

**Response:** Paginated list of `CodeDefinePicklistDTO`.

---

### 9.2 CodeDefine Segment Endpoints

#### GET /CodeDefineSegment/GetCodeDefineSegment

Retrieves all active segments for a CodeDefine, ordered by `SLNO`.

| Parameter | Type | Location | Required |
|---|---|---|---|
| `CodeDefineId` | int | Query | Yes |

**Response:** JSON-serialised list of `CodeDefineSegmentDTO`.

**Caching:** `CLIENT_LEVEL` — segments are cached per tenant. Cache is invalidated when segments are saved or deleted.

---

#### POST /CodeDefineSegment/SaveCodeDefineSegment

Creates or updates a single segment.

| Body | Type | Description |
|---|---|---|
| `CodeDefineSegmentDTO` | JSON body | Single segment definition |

**Response:** Success message with `SegmentId`.

**Note:** `SLNO` is re-assigned sequentially by the BLL — the client-supplied value is overwritten.

---

#### DELETE /CodeDefineSegment/DeleteCodeDefineSegment

Deletes a single segment by ID.

| Parameter | Type | Location | Required |
|---|---|---|---|
| `SegmentId` | int | Query | Yes |

**Response:** Delete success message or not-found message.

---

#### POST /CodeDefineSegment/GetSelectListCodeDefineSegment

Returns a paginated list of segments for a given CodeDefine (used in admin segment selection).

| Parameter | Type | Location | Required |
|---|---|---|---|
| `FirstNumber` | int | Query | Yes |
| `MaxResult` | int | Query | Yes |
| `CodeDefineId` | int | Query | Yes |

---

## 10. Architecture & Layer Responsibilities

### 10.1 Layer Map

```
FrameworkSL (FastEndpoints)
    GetCodeDefine, SaveCodeDefine, DeleteCodeDefine, GetSelectListCodeDefine
    GetCodeDefineSegment, SaveCodeDefineSegment, DeleteCodeDefineSegment, GetSelectListCodeDefineSegment
         │
         ▼  (calls via injected interface)
FrameworkBLL
    CodeDefineBLL        (ICodeDefineBLL)
    CodeDefineSegmentBLL (ICodeDefineSegmentBLL)
         │
         ▼  (calls via injected interface)
FrameworkDAL
    CodeDefineDAL        (ICodeDefineDAL)
    CodeDefineSegmentDAL (ICodeDefineSegmentDAL)
         │
         ▼  (via IQueryExecutor + Dapper)
Database: MCODEDEFINE, MCODEDEFINESEGMENT
```

### 10.2 BLL Responsibilities

- Validate mandatory fields before any DAL call.
- Reserve IDs from AutoNumber in batch (single call for header + all segments on create).
- Reassign `SLNO` sequentially to all segments on every save.
- Stamp `CreatedById`, `CreatedOn`, `ModifiedById`, `ModifiedOn` from `LoginDTO`.
- Return `SuccessResponse` resource strings (i18n-compliant, never raw English).
- Publish audit EventLog after every successful write.
- Tag `GB5Trace.Step` and `GB5Trace.MarkFailed` for observability.

### 10.3 DAL Responsibilities

- Execute parameterised SQL via `IQueryExecutor`.
- Wrap header + segment saves in a database transaction.
- Implement delete-and-reinsert for segment updates within the transaction.
- Provide the stored-procedure wrapper (`TRY_EXECUTE_PROC_GENERATE_CODE`) with null-on-error semantics.
- Never contain business logic — only data retrieval and mutation.

### 10.4 SL Responsibilities

- Map HTTP request parameters to strongly-typed parameter objects.
- Delegate to BLL — no business logic in endpoints.
- Return `ResponseStandardDTO<object>` in all cases.
- Set `GetCacheKey()` to null for all mutation endpoints.
- Use `CacheKeyLevel.CLIENT_LEVEL` for segment read endpoints.

---

## 11. Caching Strategy

| Endpoint | Cache Level | Key factors | Notes |
|---|---|---|---|
| GetCodeDefine | None | — | Returns fresh code — caching would break sequence |
| SaveCodeDefine | None | — | Mutation |
| DeleteCodeDefine | None | — | Mutation |
| GetSelectListCodeDefine | None | — | Admin picklist; not performance-critical |
| GetCodeDefineSegment | CLIENT_LEVEL | `ClientId`, `CodeDefineId` | Segment config is stable; safe to cache per tenant |
| SaveCodeDefineSegment | None | — | Mutation; invalidates segment cache |
| DeleteCodeDefineSegment | None | — | Mutation; invalidates segment cache |
| GetSelectListCodeDefineSegment | None | — | Admin picklist |

Cache invalidation for segments is triggered immediately after any successful write to `MCODEDEFINESEGMENT`.

---

## 12. Security & Multi-Tenancy

### 12.1 Tenant Isolation

Every SQL query in `CodeDefineQB` and `CodeDefineSegmentQB` includes `DATABASENAME = @DatabaseName` in the `WHERE` clause. No cross-tenant data leakage is possible at the query level.

All endpoints extract `LoginDTO` from the verified HTTP context (JWT claims) — never from client-supplied payload.

### 12.2 Authorisation

CodeDefine configuration endpoints are restricted to users with the appropriate administrative role (configured via FastEndpoints' `Roles()` or `Policies()` in `Configure()`). End users who create entities never call CodeDefine endpoints directly — the code is generated server-side by the BLL of the owning module.

### 12.3 Input Validation

- `CategoryCode` and `CategoryName`: not-empty validation in BLL before any DAL interaction.
- `SourceField`: mandatory for Attribute-type segments.
- All SQL parameters are passed as Dapper anonymous objects — no string concatenation.

### 12.4 Sequence Integrity

`CATLASTNO` updates happen inside a database transaction alongside the entity save, preventing gaps due to failed saves. The stored procedure `PROC_GENERATE_CODE` also updates `CATLASTNO` atomically within its own transaction.

---

## 13. Observability & Audit

### 13.1 OpenTelemetry Tracing

Every BLL save method emits structured trace steps:

| Step tag | When emitted |
|---|---|
| `validate-codedefine` | Before field validation |
| `save-codedefine` | Before DAL call (includes `{ codeDefineId, isNew }` tags) |
| `event-publish` | After EventLogPublish call |
| `validate-segment` | Before segment field validation |
| `save-segment` | Before segment DAL call |

On any failure: `GB5Trace.MarkFailed(reason, ex)` is called — spans are never silently closed.

### 13.2 Audit EventLog

Every successful write to `MCODEDEFINESEGMENT` publishes an EventLog record via Dapr pub/sub:

| Field | Value |
|---|---|
| `EventTypeId` | `EventTypeConstant.SAVECODEDEFINESEGMENTEVENTTYPEID` (−1399999295) |
| `EventText` | Description of the operation |
| `UserId` | From `LoginDTO` |
| `Data` | JSON-serialised segment DTO |
| `MachineIP` | From `LoginDTO` |

This creates an immutable audit trail for all configuration changes, readable by Auditors and Compliance Officers.

### 13.3 Logging

Structured logging is used throughout:

```
_logger.LogInformation("CodeDefine {CodeDefineId} saved by {UserId}", dto.CodeDefineId, login.UserId);
_logger.LogError(ex, "SaveCodeDefineSegment failed for SegmentId {SegmentId}", dto.SegmentId);
```

Sensitive fields (`LoginDTO` credentials, connection strings) are never logged.

---

## 14. Integration Points

### 14.1 AutoNumber Service

Used to reserve IDs for new CodeDefine and Segment records in a single batch before the INSERT, avoiding round-trips.

### 14.2 Entity Registry (MENTITY)

`CodeGeneration.AssignCode<T>()` looks up entity metadata from `MENTITY` by entity name to obtain `EntityId`. This links module entities to their numbering rules without hard-coded IDs.

### 14.3 Formula Engine (MFORMULA)

For `GenerationType = 2`, the formula referenced by `FormulaId` is loaded and evaluated. The formula engine is a separate framework service.

### 14.4 Code Token Lookup (MCODETOKEN)

Lookup-type segments (`SegmentType = 2`) resolve short tokens (e.g., branch code `MUM`) from this table using `LOOKUPTABLE` and the entity's context data.

### 14.5 Module Entity BLLs (Consumers)

Every module that creates entities with codes is a consumer of `CodeGeneration`. Examples:

- `EmployeeBLL.SaveEmployee()` → calls `CodeGeneration.AssignCode<EmployeeDTO>()`
- `CustomerBLL.SaveCustomer()` → calls `CodeGeneration.AssignCode<CustomerDTO>()`
- `PurchaseInvoiceBLL.SaveInvoice()` → calls `CodeGeneration.AssignCode<PurchaseInvoiceDTO>()`

The call is a single line — no per-module code-generation logic required.

### 14.6 Cache Infrastructure (HybridCache + Redis)

Segment read results are stored in the distributed cache at `CLIENT_LEVEL`. The cache key is generated using `CacheKeyGenerator` with `EntityConstant.OBJECTCODEDEFINESEGMENT` as the object type.

---

## 15. Error Handling & Validations

### 15.1 BLL Validation Errors (before DAL)

| Condition | Error |
|---|---|
| `CategoryCode` is empty | Validation exception: CategoryCode required |
| `CategoryName` is empty | Validation exception: CategoryName required |
| `SourceField` empty for Attribute segment | Validation exception: SourceField required |
| `CatEndNo` ≤ `CatStartNo` | Validation exception: End number must exceed start number |
| Sequence exhausted (`CatLastNo` ≥ `CatEndNo`) | Code generation error: Sequence exhausted for category |

### 15.2 Database / Stored Procedure Errors

- `PROC_GENERATE_CODE` is wrapped in TRY/CATCH at the SQL level → returns `NULL` on any RAISERROR.
- C# detects `NULL` result and logs / returns a structured error rather than propagating the SQL exception to the client.

### 15.3 Transaction Rollback

Any exception inside `CodeDefineDAL.SaveCodeDefine` or `UpdateCodeDefine` triggers a `RollbackAsync()` call before re-throwing. AutoNumber IDs that were reserved but not committed are logged for DBA awareness.

### 15.4 Client Error Responses

No stack traces are returned to the client. All unhandled exceptions are logged server-side and translated to a generic error message via `ResponseHelper.SystemError()`.

---

## 16. Database Design & Indexing

### 16.1 Required Indexes

**MCODEDEFINE:**
```sql
CREATE INDEX IX_MCODEDEFINE_ENTITY
    ON MCODEDEFINE (ENTITYID, DATABASENAME)
    INCLUDE (CATEGORYCODE, GENERATIONTYPE, CATLASTNO, CATPREFIX, CATSUFFIX, CODESIZE, CATENDNO);
```

Rationale: Code generation reads MCODEDEFINE by EntityId + DatabaseName on every entity save — this is the hottest query path in the system.

**MCODEDEFINESEGMENT:**
```sql
CREATE INDEX IX_MCODEDEFINESEGMENT_CODEDEFINEID
    ON MCODEDEFINESEGMENT (CODEDEFINEID, SLNO)
    INCLUDE (SEGMENTTYPE, SOURCEFIELD, SEPARATOR, FORMAT, ISMANDATORY, DEFAULTVALUE, LOOKUPTABLE, PREFIX, SUFFIX);
```

Rationale: `PROC_GENERATE_CODE` reads segments in `SLNO` order for every Segment-type code generation — this index makes that seek O(log n) and covers all required columns.

### 16.2 Sequence Contention (High Volume)

In high-throughput scenarios, multiple concurrent entity saves may contend on the same `CATLASTNO` row. This is handled at the SQL level inside the stored procedure using `UPDATE ... OUTPUT` with row-level locking, ensuring each increment is atomic. If throughput demands exceed SQL-level limits, consider pre-allocating sequence blocks (range reservation) at the BLL level.

---

## 17. Constraints & Known Limitations

| Constraint | Detail |
|---|---|
| Segment DERIVED type | `SegmentType = 4` (Derived) is defined in the schema but not yet fully implemented in `PROC_GENERATE_CODE`. Avoid use in production until documented. |
| No partial rollback on ID reservation | If AutoNumber IDs are reserved and the subsequent DAL transaction fails, those IDs are consumed (gap in the sequence). This is by design — gaps in non-financial sequences are acceptable. |
| Formula evaluation | The formula engine is a separate service. If `FORMULAID` references a deleted or invalid formula, code generation will fail at runtime. |
| PROC_GENERATE_CODE JSON parsing | The stored procedure uses JSON_VALUE (SQL Server 2016+) for Attribute segments. Ensure the target database version supports this function. |
| Delete-and-reinsert for segments | When a CodeDefine with many segments is updated, all segments are deleted and reinserted in the same transaction. This is safe but creates write amplification for small edits. A future optimisation could diff and update only changed segments. |
| Manual type and uniqueness | When `GenerationType = 1`, uniqueness of the user-supplied code is not enforced by this framework. The owning module's DAL must add a unique constraint on the code column. |
| Multi-database (SQL Server vs PostgreSQL) | `CodeGenerationQB` produces dialect-specific SQL (`ISNULL` vs `COALESCE`). Stored procedures are SQL Server only — PostgreSQL equivalents must be written separately. |

---

## 18. Glossary

| Term | Definition |
|---|---|
| **CodeDefine** | A database record that defines the code generation rule for one entity category |
| **CodeDefine Segment** | A child record defining one piece of a Segment-type code |
| **CATLASTNO** | The last-used sequence number in a CodeDefine; incremented on each auto/segment generation |
| **CategoryCode** | A short code identifying a specific code-generation rule (e.g., `EMP`, `PI-MUM`) |
| **EntityCode** | The name of the business entity (e.g., `Employee`, `PurchaseInvoice`) |
| **GenerationType** | Numeric code indicating how codes are produced (0=Auto, 1=Manual, 2=Formula, 3=Hidden, 4=Segment) |
| **SLNO** | Sequence/order number for segments; determines assembly order in code |
| **SegmentType** | The role of a segment: Fixed (0), Attribute (1), Lookup (2), Sequence (3), Derived (4) |
| **SourceField** | For Attribute segments, the JSON key name to extract from the serialised entity DTO |
| **PROC_GENERATE_CODE** | SQL stored procedure that assembles Segment-type codes from the MCODEDEFINESEGMENT rows |
| **AutoNumber** | Shared framework service that reserves unique integer IDs before INSERT |
| **IQueryExecutor** | Framework abstraction over Dapper — provides typed query methods with multi-database support |
| **LoginDTO** | Per-request session object carrying user identity, tenant context, and database connection info |
| **MENTITY** | Master entity registry table mapping entity names to integer IDs |
| **MFORMULA** | Table storing formula expressions used in Formula-type code generation |
| **MCODETOKEN** | Lookup table used to resolve short codes (e.g., branch codes) in Lookup-type segments |
| **HybridCache** | In-process + Redis distributed cache used for segment configuration caching |
| **GB5Trace** | Framework static helper for emitting OpenTelemetry trace steps from BLL/DAL |
| **EventLogPublish** | Framework Dapr pub/sub mechanism for publishing audit events |
| **ResponseStandardDTO** | Standard HTTP response wrapper returned by all FastEndpoints in GB5 |
| **Delete-and-reinsert** | Update strategy for child records: delete all children, then reinsert from the incoming list — ensures clean ordering without orphans |