Renova Explore the architecture
Database-orchestrated pipeline · .NET · SQL Server

Your database sends the renewal notices.

Renova turns SQL Server into the workflow engine for policy renewals.

KP job — the orchestrator
-- 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…

  • 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
1,240 policies · II Aviso predominates
POL-119045-02
II Aviso · ₡184,500 · deadline 12/07
SMSEmail
Dispatchingin flight
POL-482193-07
III Aviso · SMS → gateway · Email → queue
SMSEmailChatbot
Sent → action loggedlogged
POL-771230-01
I Aviso · Email sent → action #10 logged
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

KP_jobGeneralEnviosVencimientos
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…

Explore the interactive modules

Everything above is clickable…

Back to top