Use this reference to map Engagements screens to the external data warehouse. Some UI values are stored directly, while others must be rebuilt from related rows.
Reporting rules
- Filter inactive document and contact rows using
is_active. - Treat a null
address_contact_info_idorbilling_contact_idaccording to the “same as primary contact” behavior rather than automatically treating it as missing data. - Do not use row IDs or
created_atto infer when a signatory was added if saving signatories replaces the full set. - Treat layout ordinals as positions, not stable identifiers.
- Distinguish template-level
engagement_letter_template_layoutrows from per-letterengagement_letter_layoutrows.
Engagement details — engagement
| UI field | Column | Notes |
|---|---|---|
| Primary Client | client_id | Joins to the client record |
| Primary Contact | primary_contact_id | Required |
| Address | address_contact_info_id | Null can mean same as primary contact |
| Billing Contact | billing_contact_id | Null can mean same as primary contact |
| Automatic payments | enable_automatic_payments | Related plan rows can be created later |
| Payment-method prompt | payment_info_type | OPTIONAL, REQUIRED, or NONE |
Additional clients — engagement_additional_client
One row is created per additional client_id. The primary client remains on engagement.client_id; a complete client list must combine both sources.
Projects — engagement_job
engagement_job links the engagement to job. Project template and service-line labels are resolved through related job, job-template, and service-line records rather than stored directly on the link row.
Documents — engagement_document
| UI value | Column or derivation |
|---|---|
| Document | file_id joined to the file name |
| Type | engagement_document_type_id |
| Rules | signature_requirement_type |
| Completed | Derived from active contact rows and signed_at |
| Affirmative | Derived from active contact rows and consent_status |
| Processing status | digilence_processing_status |
| Removed | is_active = false |
Signatories — engagement_document_contact
contact_id identifies each signatory. signed_at records the signing time. Completion logic also needs the parent document’s signature_requirement_type. Saving edited signatories can replace the full active set.
Letter — engagement_letter
Dynamic letters use document_template_id; standard letters use engagement_letter_template_id. engagement_document_id resolves the generated document used by Download. Template-name reporting is incomplete where the dynamic document template cannot be resolved to a readable name.
Per-letter layout — engagement_letter_layout
Each row represents one section of one letter. Important columns include ordinal, engagement_letter_section_id, engagement_letter_section_template_id, custom_template_content, and is_template_content_custom.
Template layout — engagement_letter_template_layout
Each row represents a section in the reusable letter template. Important columns include ordinal, engagement_letter_section_id, engagement_letter_section_template_id, and is_section_template_locked.