← All posts

A chassis with an empty cargo bed

· 7 min read · Rick Hoek
architecturet-sqlplatform
A chassis with an empty cargo bed

Count what a new business application needs before it does the first thing the business actually asked for. Users and a login. Roles, and a way to say who may see which screen. Lists that page, sort and filter. Forms that validate. An export to Excel, because someone will ask on day two. A PDF, because an order confirmation is a document. Outgoing mail. A file drop for the supplier who only speaks SFTP. An audit trail, because one day someone will ask who changed that price.

None of that is the application. All of it has to work before the application can. And on most projects it gets built again, slightly differently, by whoever happens to be on the team that year.

TSQL.APP takes the opposite position: the plumbing is finished before you start. You deliver a chassis with the engine running and an empty cargo bed, and the only thing you build is the cargo.

What is already on the chassis

From the first login, an application built on TSQL.APP has:

  • A user interface it did not have to write. Lists, detail views, modal dialogs, forms and inputs are rendered from metadata and T-SQL, in a responsive Bootstrap interface that works on a phone as well as a desktop.
  • Documents and exports. PDF, Excel and HTML output, straight from the data.
  • Communication. Transactional mail, file transfer over SFTP, and barcode scanning screens for handheld terminals in a warehouse.
  • Security. User management, role-based access control, data encryption and audit logging.
  • A development environment. A VS Code-style editor and debugger that work against the application while it runs.

Every item on that list is a stored procedure call away. That is the part worth showing.

What "already there" looks like

Here is a complete action — a dialog where a user writes a message and sends it by mail. The input, the button, the validation and the delivery are all T-SQL:

sql
DECLARE @To         NVARCHAR(MAX);
DECLARE @Message    NVARCHAR(MAX);
DECLARE @SendButton NVARCHAR(MAX);
DECLARE @Subject    NVARCHAR(4000);
DECLARE @Html       NVARCHAR(MAX);
DECLARE @Feedback   NVARCHAR(MAX);

EXEC sp_api_modal_get_value @name = N'@To',         @value = @To OUT;
EXEC sp_api_modal_get_value @name = N'@Message',    @value = @Message OUT;
EXEC sp_api_modal_get_value @name = N'@SendButton', @value = @SendButton OUT;

EXEC sp_api_modal_text   @text = N'Send a message', @class = N'h3';
EXEC sp_api_modal_input  @name = N'@To',      @value = @To OUT,      @placeholder = N'Recipient address';
EXEC sp_api_modal_input  @name = N'@Message', @value = @Message OUT, @placeholder = N'Your message', @rows = 5;
EXEC sp_api_modal_button @name = N'@SendButton', @value = N'Send',
     @valueout = @SendButton OUT, @class = N'btn-primary';

IF @SendButton IS NULL RETURN;

IF @To IS NULL OR @To NOT LIKE N'_%@_%._%' OR LEN(TRIM(ISNULL(@Message, N''))) = 0
BEGIN
    SET @Feedback = N'Enter a valid address and a message.';
    EXEC sp_api_toast @text = @Feedback, @class = N'bg-danger text-white';
    EXEC sp_api_modal_value @name = N'@SendButton', @value = NULL;
    RETURN;
END

SET @Subject = CONCAT(N'A message from ', @user_name);
SET @Html = CONCAT(N'<p>', REPLACE((SELECT @Message FOR XML PATH('')), CHAR(10), N'<br>'), N'</p>');
EXEC sp_api_email @to = @To, @subject = @Subject, @html = @Html;

SET @Feedback = CONCAT(N'Message to ', @To, N' queued for sending.');
EXEC sp_api_toast @text = @Feedback, @class = N'bg-success text-white';
EXEC sp_api_modal_clear;

There is no SMTP client in that script, no mail server configuration and no retry loop. sp_api_email hands the message to the platform's background worker and returns at once, which is why the confirmation says queued rather than sent. @user_name is not declared anywhere either: the logged-in user arrives with every action, because knowing who is clicking is chassis, not cargo.

"Then I am stuck inside your framework"

The fair objection to any chassis is that it decides the shape of the truck. A framework that does a lot for you usually also does a lot to you.

Two things keep that from happening here. First, the cargo bed is your own database, left exactly as it is. The framework lives in a second database next to it and reaches your tables through synonyms; it does not migrate, rename or annotate your schema. Second, an action script is ordinary T-SQL. The sp_api_* procedures are there when you want a dialog, a document or a mail, and everything between those calls is your own query against your own data. The chassis carries the load; it does not tell you what to load.

The part that is easy to miss

Shared plumbing saves time. That is the obvious benefit, and not the interesting one. The interesting one is that a decision made once in the plumbing is made for every application on it — including decisions nobody would remember to make on a deadline.

Take the mail call above. Inside sp_api_email is a check against an application setting called environment. Unless that setting says prod, the message does not go to the people you addressed it to. It goes to the logged-in user instead, with Test environment in front of the subject and the original recipients listed after it, and the copies are dropped.

If the setting does not exist yet, the first mail creates it — with the value test. Going live is a deliberate act: someone switches it to prod.

Consider what that buys. The most common way a business application embarrasses its owner is a copy of production, restored onto a test server so a developer can reproduce a problem, that then cheerfully sends every real customer a reminder about an invoice they already paid. Every team knows the rule — "make sure test can't send mail" — and every team relies on someone remembering it.

On a chassis, nobody has to remember. A restored copy is a test environment until somebody says otherwise, for every application built on it, including the ones written after this post. For the rare mail that must reach its real recipient even from a test system, there is an explicit @ignore_test_environment = 1, so the exception is visible in the code rather than hidden in a config file.

That is the real argument for a chassis. Not that the brakes are already fitted, but that they were fitted by someone who was not in a hurry.

Bolt-on parts: Exact Online

Some parts are not on every chassis but are available to bolt on. The most requested one is a link to Exact Online, the accounting package.

The tedious half of any accounting integration is not the business logic. It is OAuth2, refresh tokens that expire at awkward moments, and mapping one system's idea of a customer onto another's. With the Exact Online integration that part is already done, and what is left is database logic:

  • keeping customers, suppliers and items in step between the operational database and the books;
  • pushing sales orders, purchase invoices and journal entries into Exact Online from your own screens;
  • showing outstanding balances, credit limits and invoice status from Exact Online inside your own application.

Changing the cargo while it drives

One more property follows from the cargo being metadata and stored procedures in the database: there is no release. Changing a script changes the screen for the next user who opens it — no build, no deploy, no restart. How that works, and what it does not protect you from, is a post of its own: Changing an application while it runs.

Where to go next

The mail example above is taken from the sending email guide, which also covers attachments and copies. The rest of the plumbing is in the documentation and the programming reference. And if you would rather see a chassis than read about one, book a demo.