Technical requirements for stored procedure data access and table schema.
To get today's applicable calls from the client's side (fulfilled, original non-renewal orders).
| # | Field Name | Data Type | Description |
|---|---|---|---|
| 1 | call_id | VARCHAR(100) | Unique call identifier |
| 2 | order_id | VARCHAR(100) | The fulfilled order linked to the call |
| 3 | customer_id | VARCHAR(100) | Customer identifier |
| 4 | customer_first_name | VARCHAR(100) | Customer first name |
| 5 | customer_last_name | VARCHAR(100) | Customer last name |
| 6 | shipping_address_line_1 | VARCHAR(255) | Street address |
| 7 | shipping_address_line_2 | VARCHAR(255) | Apt/Suite (nullable) |
| 8 | shipping_city | VARCHAR(100) | City |
| 9 | shipping_state | VARCHAR(50) | State |
| 10 | shipping_zip | VARCHAR(20) | Zip code |
| 11 | fulfillment_date | DATETIME2 | When the order was fulfilled |
| 12 | audio_blob_path | VARCHAR(500) | Full path/link to the call recording in Azure Blob Storage so the VM can download it directly. (Either via concat or app logic builder). |
To save transcripts and track letters back to the client's database.
| # | Column Name | Data Type | Constraints | Description |
|---|---|---|---|---|
| 1 | id | BIGINT | PK, Identity, Not Null | Unique identifier for each record |
| 2 | call_id | VARCHAR(100) | Not Null, Unique | Unique identifier for each call |
| 3 | order_id | VARCHAR(100) | Not Null | Associates the record with a specific order |
| 4 | customer_id | VARCHAR(100) | Not Null | Identifies the customer tied to the interaction |
| 5 | transcript_text | NVARCHAR(MAX) | Not Null | Stores full interaction transcript |
| 6 | letter_sent_at | DATETIME2 | Nullable | Timestamp indicating when the letter was sent (triggers 70-day follow-up) |
| 7 | letter_delivered_at | DATETIME2 | Nullable | Timestamp indicating when the letter was successfully delivered |
| 8 | status | VARCHAR(50) | Not Null, Default 'pending' | Current state: pending, processed, sent, delivered, failed |