Renova turns SQL Server into the workflow engine for policy renewals. A scheduled job reconciles every policy against the insurer, a stored procedure builds a per-policy notice batch, and email + SMS + chatbot senders fan it out — deduped, audited, and transactional with the policy data itself. A background worker keeps the expiry dates honest before a single notice leaves.
-- 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.
An insurance agency has to tell thousands of customers, on time and across channels, that a policy is about to lapse — with the right amount, the right deadline, and the right escalation stage. But the true expiry date lives at the carrier, not in the local database, and it drifts every time a receipt is paid or a policy is renewed elsewhere. Notices that go out on a stale date erode trust and create disputes.
Bolting a scheduler and a mailer onto the app tier means brittle cron, a second state store, duplicate sends when a job fires twice, and no audit trail tying a sent notice back to the policy. Renova keeps the database as the orchestrator: it reconciles first, sends set-based, dedupes per day, and records every action next to the policy it acted on.
Four channels emit policies into one SQL Server store; a scheduled job orchestrates reconciliation and dispatch; a worker fleet reconciles against the carrier. Follow a policy from issued to notified.
The orchestrator. A scheduled KP_job* procedure gates by calendar and time window, a 37 KB report procedure builds each notice batch, and sender procedures dispatch and audit — the workflow is stored procedures, not a broker.
An always-on .NET console runs two “virtual processes” in parallel: one pulls debt from the carrier every 30 min, one reconciles each policy's real expiry date against the carrier's API every hour and writes the correction back through stored procedures.
Four apps emit policies into the same store: a public storefront, a school-enrollment / student-insurance module, a modern .NET 8 transport-insurance REST API, and the operator console. Each writes policy rows with an expiry date the pipeline later acts on.
Dispatch reuses a shared messaging platform: SMS batches hand off to a gateway procedure, email rows land in a send queue, and a chatbot channel covers WhatsApp. Each notice is a set-based insert, deduped per policy per day.
The modern .NET 8 channel that quotes, issues, and cancels policies against the carrier. Real endpoint shapes; illustrative values. Every call needs Authorization: Bearer <token>.
{ "clientId": "renova-partner-01", "clientSecret": "••••••••" }A policy is emitted by any channel; a scheduled job and a background worker keep it honest; then stored procedures build and dispatch the notice — all inside the database. Step through it.
Honest version: the slow carrier calls run in a worker, but the database is the engine that schedules, gates, builds, and dispatches.
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
Every policy is reconciled against the carrier before a notice is built; every send is deduped, gated by a fail-closed guard, and recorded next to the policy it acted on; and the whole batch fans out across SMS, email, and chatbot as set-based work on a schedule the ops team already runs. The channels stay independent, the workers stay stateless, and the database stays the single source of truth.
Everything above is clickable — try the API explorer, step the renewal pipeline, and open a notice batch.