EnvelopeGenerator/DATABASE_STRUCTURE_CONTEXT.md

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.