Database-orchestrated pipeline · .NET · SQL Server
Your database sends the renewal notices.
Renova turns SQL Server into the workflow engine for policy renewals.
-- runs on a SQL Server Agent schedule IF EXISTS (SELECT 1 FROM Calendario -- skip weekends / holidays WHERE Fecha = CAST(GETDATE() AS DATE) AND DiaSemana IN (7,1)) RETURN; EXEC EnqueuePoliciesToValidate; -- feed the worker EXEC UpdateIssueDates; IF DATEPART(HOUR, GETDATE()) BETWEEN 8 AND 20 -- send window BEGIN EXEC SendRenewalSMS; -- each first builds the notice batch, EXEC SendRenewalEmail; -- then dispatches its channel END
Sending is a database event, not an app event.
4
Intake channels
8
Transport-API endpoints
839
Stored procedures
I·II·III
Escalating notice stages
3
Dispatch channels (SMS · Email · Chatbot)
The problem
A renewal notice on stale data is worse than no notice at all.
An insurance agency has to tell thousands of customers…
Bolting a scheduler and a mailer onto the app tier…
Constraints it had to respect
- Never send on a stale expiry date.
- Never send duplicates.
- Fail closed.
- Every notice is transactional and audited.
Architecture
Channels feed it, the database runs it, the workers keep it honest.
Four channels emit policies into one SQL Server store…
Intake channels
Site · Enroll · API · Console
Carrier API
insurer
Worker fleet
.NET
SQL Server
orchestrator
Messaging fabric
SMS · Email · Chatbot
1
2
4
3
5
6
- 1channels emit policies (with expiry)
- 2job enqueues policies to validate
- 3worker reconciles vs carrier
- 4write corrected expiry + debt
- 5report SP builds the notice batch
- 6senders fan out + audit
SQL Server engine
The orchestrator…
SQL Agent jobreport SPguard SPcross-DB EXEC
Worker fleet
An always-on .NET console…
infinite looppoll queuecarrier OAuth5-state rules
Intake channels
Four apps emit policies…
.NET 8 Web APIWebFormsDapperINS emission
Messaging fabric
Dispatch reuses a shared platform…
SMS gatewayemail queuechatbotset-based
Features
Six mechanics that make it trustworthy.
Decisions
Seven engineering decisions, and why.
What I'd harden next
REST API
The transport-insurance channel, endpoint by endpoint.
The modern .NET 8 channel…
The signature flow
Issued → Reconciled → Notified
A policy is emitted by any channel…
Operations
Inside a notice batch (lote).
Batch built (report SP)staged
Lote #4821
POL-119045-02
SMSEmail
Dispatchingin flight
POL-482193-07
SMSEmailChatbot
Sent → action loggedlogged
POL-771230-01
Email
worker run-log
[worker] validation run complete · 1,240 policies reconciled · 0 pending
[guard] preconditions OK for 02/07 → send authorized
[email] lote 4821 → 1,190 rows queued (50 already notified today)
The idea
Why let the database run the pipeline?
Honest version…
Why it's powerful
Where its limits are
CREATE PROCEDURE dbo.KP_jobGeneralEnviosVencimientos AS BEGIN -- weekend / holiday gate IF EXISTS (SELECT 1 FROM Calendario WHERE Fecha = CAST(GETDATE() AS DATE) AND DiaSemana IN (7,1)) RETURN; EXEC EnqueuePoliciesToValidate; -- feed the worker's validation queue EXEC UpdateIssueDates; -- send window IF DATEPART(HOUR, GETDATE()) BETWEEN 8 AND 20 BEGIN EXEC RefreshPolicyPlates; EXEC SendRenewalSMS; -- each sender first EXECs the report SP, EXEC SendRenewalEmail; -- builds the lote, then dispatches -- EXEC ChatbotService.dbo.SendRenewalWhatsApp; -- channel, currently gated END -- midday fallback: alert ops if today's batch never shipped IF DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13 AND NOT EXISTS (SELECT 1 FROM NoticeBatch WHERE ID_Lote = (SELECT MAX(ID_Lote) FROM NoticeBatch) AND State = 'Sent') INSERT INTO Messaging.dbo.Notifications (subject, body) VALUES ('Renewal auto-send', 'Today''s notice batch has not shipped.'); END
Stack
The stack, grouped by role.
Backend
.NET 8ASP.NET Core Web API.NET WebForms 4.8Console workerDapper
Data
SQL Server 2022stored proceduresSQL Agent jobscross-DB EXECReportViewer
Integrations
Carrier REST + SOAPOAuth bearerSMS gatewaySMTPWhatsApp chatbot
Channels
Public storefrontEnrollmentTransport APIOperator console
Practices
reconcile-before-sendidempotent dispatchfail-closed guardin-band audit
The result
A renewal pipeline the database owns end to end.
Every policy is reconciled against the carrier before a notice is built…