# Database Structure Context

## Why this file exists

This is the deep-dive DB reference for migration work when schema can change and old DB knowledge may be lost.

- Scope: `EnvelopeGenerator.Domain` entities + `EnvelopeGenerator.Infrastructure/EGDbContext.cs` + live DB metadata snapshot from `DD_ECM`.
- Goal: make schema transitions predictable (what exists, how it is linked, where logic executes).
- Safety: all DB checks used read-only `SELECT` metadata queries; no data mutation.

## Snapshot metadata

- Source DB: `DD_ECM` on `SDD-VHP04-SQL19\DD_TESTING01`
- Snapshot date: `2026-10-20`
- App context: `EnvelopeGenerator.Infrastructure/EGDbContext.cs`

## Fast navigation

- High-level aggregate map: `AGENTS.md` -> `Database` section
- EF model config source: `EnvelopeGenerator.Infrastructure/EGDbContext.cs`
- Stored procedure wrappers:
  - `EnvelopeGenerator.Application/Common/SQL/EnvelopeCreateReadSQL.cs`
  - `EnvelopeGenerator.Application/Common/SQL/EnvelopeReceiverAddReadSQL.cs`
  - `EnvelopeGenerator.Application/Common/SQL/DocumentCreateReadSQL.cs`
- Direct SQL execution in controller:
  - `EnvelopeGenerator.Server/EnvelopeGenerator.Server/Controllers/EnvelopeReceiverController.cs`

## Core data model (ER-level)

- `Envelope` (`TBSIG_ENVELOPE`) is root aggregate.
- `Envelope (1) -> (N) Document` via `TBSIG_ENVELOPE_DOCUMENT.ENVELOPE_ID`.
- `Envelope (1) -> (N) History` via `TBSIG_ENVELOPE_HISTORY.ENVELOPE_ID`.
- `Envelope (N) <-> (N) Receiver` through `EnvelopeReceiver` (`TBSIG_ENVELOPE_RECEIVER`, composite PK `{ENVELOPE_ID, RECEIVER_ID}`).
- `Document (1) -> (N) DocReceiverElement` via `TBSIG_DOCUMENT_RECEIVER_ELEMENT.DOCUMENT_ID`.
- `DocReceiverElement (1) -> (N) ElementAnnotation` via `TBSIG_DOCUMENT_RECEIVER_ELEMENT_ANNOTATION.ELEMENT_ID`.
- `DocumentStatus` links `Envelope + Receiver` state in `TBSIG_DOCUMENT_STATUS`.

## Entity <-> table mapping (code-level)

| Entity | Table | PK in code | PK in DB | Important relation notes |
|---|---|---|---|---|
| `Envelope` | `TBSIG_ENVELOPE` | `Id` | `GUID` | Has `Documents`, `Histories`, `EnvelopeReceivers`; unique index on `Uuid` configured in EF |
| `EnvelopeReceiver` | `TBSIG_ENVELOPE_RECEIVER` | `{EnvelopeId, ReceiverId}` | `{ENVELOPE_ID, RECEIVER_ID}` | DB also has `GUID` identity (not modeled as entity PK) |
| `Document` | `TBSIG_ENVELOPE_DOCUMENT` | `Id` | `GUID` | FK `ENVELOPE_ID` |
| `DocReceiverElement` | `TBSIG_DOCUMENT_RECEIVER_ELEMENT` | `Id` | `GUID` | Coordinates persisted here (`POSITION_X/Y`, `WIDTH`, `HEIGHT`, `PAGE`) |
| `ElementAnnotation` | `TBSIG_DOCUMENT_RECEIVER_ELEMENT_ANNOTATION` | `Id` | `GUID` | FK `ELEMENT_ID` |
| `DocumentStatus` | `TBSIG_DOCUMENT_STATUS` | `Id` | `GUID` | FK `ENVELOPE_ID`, `RECEIVER_ID` |
| `History` | `TBSIG_ENVELOPE_HISTORY` | `Id` | `GUID` | `USER_REFERENCE` maps to `Receiver.EmailAddress` and `User.Email` when `IsMigration == false` |
| `Receiver` | `TBSIG_RECEIVER` | `Id` | `GUID` | Unique indexes in code: `Signature`, `EmailAddress` |
| `EnvelopeReceiverReadOnly` | `TBSIG_ENVELOPE_RECEIVER_READ_ONLY` | `Id` | `GUID` | `AddedWho` is FK-like mapping to `Receiver.EmailAddress` |
| `EnvelopeType` | `TBSIG_ENVELOPE_TYPE` | `Id` | `GUID` | Used as optional envelope defaults/template |
| `EmailTemplate` | `TBSIG_EMAIL_TEMPLATE` | `Id` | `GUID` | Trigger-managed `CHANGED_WHEN` |
| `EmailOut` (legacy/bridge) | `TBEMLP_EMAIL_OUT` | `Id` | `GUID` | Internal/obsolete domain class, still registered for DB access |

## Physical schema snapshot (table/column level)

Notes:
- Format per column: `COLUMN_NAME TYPE NULLABLE`.
- This section reflects live DB metadata at snapshot time.

### `TBSIG_ENVELOPE`

- `GUID int NOT NULL`
- `USER_ID int NOT NULL`
- `STATUS int NOT NULL`
- `ENVELOPE_UUID nvarchar(36) NOT NULL`
- `MESSAGE nvarchar(max) NOT NULL`
- `EXPIRES_WHEN datetime NULL`
- `EXPIRES_WARNING_WHEN datetime NULL`
- `VERIFICATION_TYPE tinyint NOT NULL`
- `TITLE nvarchar(128) NULL`
- `CONTRACT_TYPE int NULL`
- `LANGUAGE nvarchar(5) NULL`
- `SEND_REMINDER_EMAILS bit NULL`
- `FIRST_REMINDER_DAYS int NULL`
- `REMINDER_INTERVAL_DAYS int NULL`
- `ENVELOPE_TYPE int NULL`
- `CERTIFICATION_TYPE int NULL`
- `USE_ACCESS_CODE bit NULL`
- `FINAL_EMAIL_TO_CREATOR int NULL`
- `FINAL_EMAIL_TO_RECEIVERS int NULL`
- `EXPIRES_WHEN_DAYS int NULL`
- `EXPIRES_WARNING_WHEN_DAYS int NULL`
- `TFA_ENABLED bit NOT NULL`
- `DOC_RESULT varbinary(max) NULL`
- `LANG_CODE varchar(5) NOT NULL`
- `SOURCE varchar(10) NOT NULL`
- `COMMENT varchar(128) NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`

### `TBSIG_ENVELOPE_RECEIVER`

- `ENVELOPE_ID int NOT NULL`
- `RECEIVER_ID int NOT NULL`
- `SEQUENCE int NOT NULL`
- `NAME nvarchar(128) NULL`
- `JOB_TITLE nvarchar(128) NULL`
- `COMPANY_NAME nvarchar(128) NULL`
- `PRIVATE_MESSAGE nvarchar(max) NULL`
- `ACCESS_CODE nvarchar(64) NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`
- `GUID bigint NOT NULL`
- `PHONE_NUMBER varchar(20) NULL`
- `PARENT_ENVELOPE_RECEIVER_ID int NOT NULL`

### `TBSIG_ENVELOPE_DOCUMENT`

- `GUID int NOT NULL`
- `ENVELOPE_ID int NOT NULL`
- `FILENAME nvarchar(256) NOT NULL`
- `FILEPATH nvarchar(256) NOT NULL`
- `ADDED_WHEN datetime NOT NULL`
- `FILENAME_ORIGINAL nvarchar(256) NULL`
- `BYTE_DATA varbinary(max) NULL`

### `TBSIG_DOCUMENT_RECEIVER_ELEMENT`

- `GUID int NOT NULL`
- `DOCUMENT_ID int NOT NULL`
- `RECEIVER_ID int NOT NULL`
- `ELEMENT_TYPE int NOT NULL`
- `POSITION_X float NOT NULL`
- `POSITION_Y float NOT NULL`
- `WIDTH float NOT NULL`
- `HEIGHT float NOT NULL`
- `PAGE int NOT NULL`
- `REQUIRED bit NOT NULL`
- `TOOLTIP nvarchar(512) NULL`
- `READ_ONLY bit NOT NULL`
- `ANNOTATION_INDEX int NOT NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`
- `CHANGED_WHO nvarchar(100) NULL`
- `FULL_NAME nvarchar(100) NULL`
- `POSITION nvarchar(100) NULL`
- `PLACE nvarchar(100) NULL`
- `INK varbinary(max) NULL`

### `TBSIG_DOCUMENT_RECEIVER_ELEMENT_ANNOTATION`

- `GUID bigint NOT NULL`
- `ELEMENT_ID int NOT NULL`
- `NAME nvarchar(100) NOT NULL`
- `VALUE nvarchar(max) NOT NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`
- `CHANGED_WHO nvarchar(100) NULL`
- `TYPE nvarchar(50) NOT NULL`
- `POSITION_X float NULL`
- `POSITION_Y float NULL`
- `WIDTH float NULL`
- `HEIGHT float NULL`

### `TBSIG_DOCUMENT_STATUS`

- `GUID int NOT NULL`
- `ENVELOPE_ID int NOT NULL`
- `RECEIVER_ID int NOT NULL`
- `STATUS int NOT NULL`
- `STATUS_CHANGED_WHEN datetime NULL`
- `VALUE nvarchar(max) NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`

### `TBSIG_ENVELOPE_HISTORY`

- `GUID bigint NOT NULL`
- `ENVELOPE_ID int NOT NULL`
- `USER_REFERENCE nvarchar(128) NOT NULL`
- `STATUS int NOT NULL`
- `ADDED_WHEN datetime NOT NULL`
- `ACTION_DATE datetime NULL`
- `COMMENT nvarchar(max) NULL`

### `TBSIG_RECEIVER`

- `GUID int NOT NULL`
- `EMAIL_ADDRESS nvarchar(128) NOT NULL`
- `SIGNATURE nvarchar(64) NOT NULL`
- `ADDED_WHEN datetime NOT NULL`
- `TOTP_SECRET_KEY nvarchar(max) NULL`
- `TFA_REG_DEADLINE datetime NULL`
- `CULTURE nvarchar(10) NULL`

### `TBSIG_ENVELOPE_RECEIVER_READ_ONLY`

- `GUID bigint NOT NULL`
- `ENVELOPE_ID bigint NOT NULL`
- `RECEIVER_MAIL nvarchar(250) NOT NULL`
- `DATE_VALID date NOT NULL`
- `ADDED_WHO varchar(100) NOT NULL`
- `ADDED_WHEN datetime NULL`
- `CHANGED_WHO varchar(100) NULL`
- `CHANGED_WHEN datetime NULL`

### `TBSIG_ENVELOPE_TYPE`

- `GUID int NOT NULL`
- `TITLE nvarchar(128) NOT NULL`
- `LANGUAGE nvarchar(5) NOT NULL`
- `EXPIRES_DAYS int NULL`
- `CERTIFICATION_TYPE int NULL`
- `USE_ACCESS_CODE bit NULL`
- `FINAL_EMAIL_TO_CREATOR int NULL`
- `FINAL_EMAIL_TO_RECEIVERS int NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`
- `EXPIRES_WARNING_DAYS int NULL`
- `SEND_REMINDER_EMAILS bit NULL`
- `FIRST_REMINDER_DAYS int NULL`
- `REMINDER_INTERVAL_DAYS int NULL`
- `CONTRACT_TYPE int NULL`

### `TBSIG_EMAIL_TEMPLATE`

- `GUID int NOT NULL`
- `NAME nvarchar(64) NULL`
- `BODY nvarchar(max) NULL`
- `SUBJECT nvarchar(512) NULL`
- `ADDED_WHEN datetime NOT NULL`
- `CHANGED_WHEN datetime NULL`
- `LANG_CODE varchar(5) NOT NULL`

### `TBEMLP_EMAIL_OUT`

- `GUID int NOT NULL`
- `REMINDER_TYPE_ID int NOT NULL`
- `SENDING_PROFILE int NOT NULL`
- `REFERENCE_ID int NOT NULL`
- `REFERENCE_STRING varchar(200) NULL`
- `ENTITY_ID int NULL`
- `WF_ID int NOT NULL`
- `WF_REFERENCE varchar(200) NULL`
- `EMAIL_ADRESS varchar(1000) NOT NULL`
- `EMAIL_SUBJ varchar(500) NOT NULL`
- `EMAIL_BODY varchar(max) NOT NULL`
- `EMAIL_ATTMT1 varchar(512) NULL`
- `EMAIL_SENT datetime NULL`
- `COMMENT varchar(500) NULL`
- `ADDED_WHO varchar(50) NOT NULL`
- `ADDED_WHEN datetime NULL`
- `CHANGED_WHO varchar(50) NULL`
- `CHANGED_WHEN datetime NULL`
- `ERROR_TIMESTAMP datetime NULL`
- `ERROR_MSG varchar(900) NULL`
- `ATTACHMENT1_BYTES varbinary(max) NULL`
- `ATT1_RELATED_ID bigint NULL`
- `ATT1_REL_TYPE varchar(20) NULL`

## Constraints and indexes (relevant)

From EF + DB metadata snapshot:

- `TBSIG_ENVELOPE`: PK `PK_TBSIG_ENVELOPE (GUID)`.
- `TBSIG_ENVELOPE_RECEIVER`: composite PK `PK_TBSIG_ENVELOPE_RECEIVER (ENVELOPE_ID, RECEIVER_ID)`.
- `TBSIG_DOCUMENT_RECEIVER_ELEMENT`: PK `GUID`, plus unique key `UQTBSIG_DOCUMENT_RECEIVER_ELEMENT_DocID_PosX_PosY (DOCUMENT_ID, POSITION_X, POSITION_Y)`.
- `TBSIG_RECEIVER`: PK `GUID`, unique key `UQTBSIG_RECEIVER (EMAIL_ADDRESS)`.
- `TBEMLP_EMAIL_OUT`: PK `GUID`, FK `FK_TBEMLP_EMAIL_OUT_SENDING_PROFILE`.

## Stored procedures: behavior and code call sites

### Active in .NET code path

1) `PRSIG_API_CREATE_ENVELOPE`
- Purpose: creates envelope row (`STATUS=1002`, `SOURCE='API'`, defaults) and returns `ENVELOPE_UUID` via output.
- DB writes: `TBSIG_ENVELOPE`.
- Called by:
  - SQL template `EnvelopeGenerator.Application/Common/SQL/EnvelopeCreateReadSQL.cs`
  - executor `EnvelopeGenerator.Infrastructure/Executor/EnvelopeExecutor.cs`
  - used in `EnvelopeReceiverController.CreateAsync` and `CreateEnvelopeReceiverCommandHandler`.

2) `PRSIG_API_CREATE_RECEIVER`
- Purpose: upserts `TBSIG_RECEIVER` by email, adds envelope assignment row with generated access code.
- DB writes: `TBSIG_RECEIVER`, `TBSIG_ENVELOPE_RECEIVER`.
- Called by:
  - SQL template `EnvelopeGenerator.Application/Common/SQL/EnvelopeReceiverAddReadSQL.cs`
  - executor `EnvelopeGenerator.Infrastructure/Executor/EnvelopeReceiverExecutor.cs`
  - used in `EnvelopeReceiverController.CreateAsync` and `CreateEnvelopeReceiverCommandHandler`.

3) `PRSIG_API_ADD_DOC`
- Purpose: stores one document for envelope if absent; returns doc id.
- DB writes: `TBSIG_ENVELOPE_DOCUMENT`.
- Called by:
  - SQL template `EnvelopeGenerator.Application/Common/SQL/DocumentCreateReadSQL.cs`
  - executor `EnvelopeGenerator.Infrastructure/Executor/DocumentExecutor.cs`
  - used in `EnvelopeReceiverController.CreateAsync`.

4) `PRSIG_API_ADD_DOC_RECEIVER_ELEM`
- Purpose: inserts signature element record if `(DOC_ID, RECEIVER_ID)` pair does not exist.
- DB writes: `TBSIG_DOCUMENT_RECEIVER_ELEMENT`.
- Called directly by:
  - `EnvelopeGenerator.Server/EnvelopeGenerator.Server/Controllers/EnvelopeReceiverController.cs`

5) `PRSIG_API_ADD_HISTORY_STATE`
- Purpose: appends history row with status and user reference.
- DB writes: `TBSIG_ENVELOPE_HISTORY`.
- Called directly by:
  - `EnvelopeGenerator.Server/EnvelopeGenerator.Server/Controllers/EnvelopeReceiverController.cs`

### Legacy/other path references in repo

- `PRSIG_GET_ENVELOPES_FOR_USER` and `PRSIG_GET_CLOSED_ENVELOPES_FOR_USER` are called in legacy VB path:
  - `EnvelopeGenerator.CommonServices/Models/EnvelopeModel.vb`

- `PRSIG_API_WITHDRAW_ENVELOPE` exists in DB snapshot but no active `.cs` call site found in current server flow.

## Trigger behavior and where they are wired

## How trigger names are wired in app

- Config source: `EnvelopeGenerator.Server/EnvelopeGenerator.Server/appsettings.json` -> `DbTriggerParams`.
- DI binding: `EnvelopeGenerator.Infrastructure/DependencyInjection.cs` (`AddDbTriggerParams`).
- Model binding: `EnvelopeGenerator.Infrastructure/EGDbContext.cs` -> `AddTrigger<T>()` -> `ToTable(tb => tb.HasTrigger(triggerName))`.

### Trigger definitions observed in DB snapshot

1) `TBSIG_DOCUMENT_RECEIVER_ELEMENT_AFT_INS`
- Fires on insert of `TBSIG_DOCUMENT_RECEIVER_ELEMENT`.
- Normalizes/rewrites `POSITION_X`, `POSITION_Y` and sets `CHANGED_WHO='TBSIG_DOC_REC_EL_AFT_INS'`.

2) `TBSIG_DOCUMENT_RECEIVER_ELEMENT_AFT_UPD`
- Fires on update of `TBSIG_DOCUMENT_RECEIVER_ELEMENT`.
- Rewrites coordinates and `CHANGED_WHO='TBSIG_DOC_REC_EL_AFT_UPD'` except when `CHANGED_WHO='OFFSET_Y_POS'`.

3) `TBSIG_ENVELOPE_HISTORY_AFT_INS`
- Fires on history insert.
- Central state machine side-effects:
  - syncs `TBSIG_ENVELOPE.STATUS` for selected `100x` states,
  - on `1003` applies Y-offset update to receiver elements,
  - on `2005` calculates partial/fully signed and may insert follow-up `1006` history,
  - on `2007` (type-dependent reject path) writes rejection emails into `TBEMLP_EMAIL_OUT` and updates envelope rejected state,
  - on `3001` invitation-message counting can move envelope to `1004`.

4) `TBSIG_EMAIL_TEMPLATE_AFT_UPD`
- Fires on update of `TBSIG_EMAIL_TEMPLATE`.
- Sets `CHANGED_WHEN = GETDATE()`.

5) `TBSIG_ENVELOPE_RECEIVER_READ_ONLY_UPD`
- Fires on update of `TBSIG_ENVELOPE_RECEIVER_READ_ONLY`.
- Updates config timestamp row (`TBSIG_CONFIG.GUID = 1`).

6) `TBEMLP_EMAIL_OUT_AFT_UPD`
- Fires on update of `TBEMLP_EMAIL_OUT`.
- When `EMAIL_SENT` changes and `WF_ID in (3001..3005)`, writes corresponding history entries to `TBSIG_ENVELOPE_HISTORY`.

### Trigger mismatch alert (important)

- `DbTriggerParams` contains `TBEMLP_EMAIL_OUT_AFT_INS`, but this trigger was not found in the snapshot DB (`ExistsInDb = 0`).
- `TBEMLP_EMAIL_OUT_AFT_UPD` exists and is active.

This should be treated as environment drift and validated before migrations.

## Known model-vs-DB drift risks

These are not necessarily bugs, but they are migration risk hotspots:

- `Receiver.EmailAddress` is `[StringLength(250)]` in entity, DB column is `nvarchar(128)`.
- `EnvelopeReceiverReadOnly.AddedWho` entity expects `nvarchar(250)`, DB column is `varchar(100)`.
- `EnvelopeReceiver` entity does not model DB columns `GUID` and `PARENT_ENVELOPE_RECEIVER_ID`.
- `Envelope` entity does not explicitly model some DB columns (`VERIFICATION_TYPE`, `LANG_CODE`, `SOURCE`).
- `EmailOut` entity has `[NotMapped] AddedWhen`, while DB has `ADDED_WHEN` column.

Before schema changes, reconcile these deltas intentionally (either mapping update or documented accepted divergence).

## Migration-safe operating rules

- Do not rely on implicit trigger behavior; document expected side effects in feature code touching history/status flows.
- Any change to `TBSIG_ENVELOPE_HISTORY` semantics requires trigger impact review first.
- Keep `DbTriggerParams` synchronized with actual DB trigger inventory per environment.
- For stored procedure changes, update both:
  - SQL wrapper templates (`Application/Common/SQL/*`)
  - direct SQL callers in controllers/executors.

## Refresh checklist (read-only)

Run these metadata checks before/after DB migration:

1. Column inventory per table (`INFORMATION_SCHEMA.COLUMNS`).
2. Constraint inventory (`INFORMATION_SCHEMA.TABLE_CONSTRAINTS` + `KEY_COLUMN_USAGE`).
3. Trigger inventory (`sys.triggers`) and definitions (`OBJECT_DEFINITION`).
4. Stored procedure inventory (`sys.procedures`) and definitions (`OBJECT_DEFINITION`).
5. Repo call-site grep for `PRSIG_` names to detect dead or missing integration points.