Framework Reference Documentation

TSQL.APP
Programmer's Manual

A complete guide to building, extending, and programming applications on the TSQL.APP metadata-driven framework. Everything from action scripts and modal dialogs to card definitions, REST APIs, and background tasks.

22Chapters

54Entries

Generated May 01, 2026 · Print-to-PDF for offline use

Chapter 01

Concepts & Architecture

Overview

What is TSQL.APP?

TSQL.APP is a metadata-driven application framework built entirely on SQL Server. There is no application code to deploy — every screen, button, filter, and navigation path is a row in a metadata table. The framework reads these rows at runtime and renders the UI automatically.

Two-database architecture

Database	Contains
`{app}_proj`	All metadata: cards, fields, actions, tasks, users
`{app}` (business DB)	Business tables, views, stored procedures, triggers

The MCP / admin connection lands on {app}_proj. Always qualify cross-database objects using the @main_db placeholder in action scripts — this is resolved at compile time to the business DB name.

The golden rule

Every screen is a card. Every card is a view. Every button is an action.

Nothing in TSQL.APP requires deploying application code. The only code you write is T-SQL — either as inline sql in metadata rows, or as stored procedures called by buttons.

architectureoverviewintro

Overview

Actions — the programming model

An action is the primary unit of programmable behaviour in TSQL.APP. When a user clicks a button, presses a keyboard shortcut, or triggers a system event, an action script runs on the SQL Server.

There are two action tables:

Table	Scope
`api_card_actions`	Bound to a specific card (`card_id` FK). Only fires when that card is active.
`api_actions`	Global. Not bound to any card. Can fire from the main menu, from any card, or as a system-level handler.

Both tables share the same execution model: the framework compiles the unparsed_sql (resolving @main_db and curly-brace expressions), then executes it inside a session that has been pre-populated with context variables.

actionsapi_actionsapi_card_actionsintro

Context Variables

Implicit variables available in every action script

Every action script runs inside a pre-configured session. The following variables are available without any declaration:

Variable	Type	Content
`@id`	nvarchar	Selected record primary key
`@ids`	nvarchar	Comma-separated multi-selected IDs
`@card_name`	nvarchar	Name of the current card
`@card_id`	int	ID of the current card
`@user`	nvarchar	Email of the logged-in user
`@user_name`	nvarchar	Display name of the logged-in user
`@user_id`	int	Integer user ID
`@main_db`	nvarchar	Business database name
`@path`	nvarchar	Current URL path
`@previous_path`	nvarchar	Previous URL path
`@parent_id`	int	Selected record in the parent card
`@parent_card_name`	nvarchar	Name of the parent card
`@parent_card_id`	int	ID of the parent card
`@is_form`	bit	1 = form view, 0 = list view
`@tablename`	nvarchar	View/table name of the current card
`@basetable`	nvarchar	Writable base table
`@identity_column`	nvarchar	PK column name

T‑SQL

-- Always prepare parameter values in variables before calling a procedure
DECLARE @msg nvarchar(500) = CONCAT(N'Card: ', @card_name, N' | User: ', @user_name, N' | ID: ', @id)
EXEC sp_api_toast @msg

contextvariablessessionimplicit

Coding Conventions

Always prepare parameters in variables before calling procedures

TSQL.APP coding convention: never pass expressions, concatenations, or computed values directly as arguments to stored procedure calls. Always assign the value to a variable first, then pass the variable.

WHY THIS MATTERS:

Readability: the procedure call clearly shows what is being passed

Debuggability: you can inspect the variable before the call

Consistency: code reads the same way regardless of complexity

Prevents subtle bugs where expressions evaluate differently inline

THE RULE:

If the value is not already in a variable, declare one.

APPLIES TO:

sp_api_toast, sp_api_goto, sp_api_modal_set, sp_api_modal_text,

sp_api_statusbar, sp_api_add_sql_task, sp_api_modal_download,

sp_api_modal_csv, sp_api_modal_route, sp_api_modal_pathname,

and every other sp_api_* and custom stored procedure call.

T‑SQL

-- WRONG: expression passed directly to procedure
EXEC sp_api_toast CONCAT(N'Card: ', @card_name, N' | ID: ', @id)
EXEC sp_api_goto CONCAT(N'/orders/', @id)

-- CORRECT: always prepare the value in a variable first
DECLARE @msg nvarchar(500) = CONCAT(N'Card: ', @card_name, N' | ID: ', @id)
EXEC sp_api_toast @msg

DECLARE @nav_path nvarchar(256) = CONCAT(N'/orders/', @id)
EXEC sp_api_goto @nav_path

conventionvariablesparametersbest-practicecoding-style

Chapter 02

Action Types

The type column — controlling where an action appears

The type column controls where the action is visible in the UI.

Value	Where it appears
`list`	List view only
`form`	Form (detail) view only
`list_form`	Both list and form views
`hidden`	No visible button — keyboard shortcut only
`hidden_list`	No button, keycode active in list only
`hidden_form`	No button, keycode active in form only
`modal_only`	Only inside a modal dialog
`picklist_only`	Only when a picklist is open

Use hidden + keycode for power-user shortcuts.

Use modal_only for admin tools inside menu modals.

list_form is the most common choice for general buttons.

T‑SQL

-- A button visible in both list and form views
INSERT INTO api_card_actions (card_id, name, display_name, action, type, keycode, action_order)
VALUES (@card_id, 'sp_my_proc', 'Do Something', 'stored_procedure', 'list_form', 'Alt+D', 10)

-- A hidden keyboard shortcut (no button shown)
INSERT INTO api_actions (name, display_name, action, type, keycode, action_order, global)
VALUES ('my_shortcut', 'My Shortcut', 'stored_procedure', 'hidden', 'Ctrl+Alt+S', 100, 1)

typevisibilitylistformhiddenmodal_onlypicklist_only

Action Types

The action column — what the action does

Value	Behaviour
`stored_procedure`	Executes T-SQL in `sql`/`unparsed_sql`
`pathname`	Navigates to a URL path defined in `params` JSON
`reducer`	Applies a WHERE clause filter to the card list
`back`	Navigates back to the previous path

stored_procedure — the workhorse

The framework executes unparsed_sql directly. If unparsed_sql is just EXEC sp_my_proc, the named stored procedure is called.

pathname — client-side navigation

Used with params JSON to navigate without T-SQL:


{"pathname": "/my_card"}
{"pathname": "/my_card/@card_id/child"}

reducer — list filters

For api_card_actions only. The sql column contains a raw WHERE clause appended to the list query.

T‑SQL

-- Reducer filter
INSERT INTO api_card_actions (card_id, name, display_name, action, sql, type, action_order)
VALUES (@card_id, 'active_only', 'Active only', 'reducer', 'status = ''active''', 'list', 20)

-- Pathname navigation
INSERT INTO api_actions (name, display_name, action, type, keycode, action_order, global, params)
VALUES ('go_orders', 'Orders', 'pathname', 'list_form', 'Alt+O', 10, 1, N'{"pathname":"/orders"}')

actionstored_procedurepathnamereducerback

Action Types

The global flag — card-bound vs everywhere

On api_actions, the global bit controls whether the action applies to every card.

Value	Meaning
`1`	Active on every card
`0` / NULL	Not injected into every card

System-level global actions

Name	Keycode	Purpose
`sys_escape`	Escape	Navigate back / close form
`sys_insert`	Insert	Open new record form
`sys_delete`	Ctrl+Delete	Delete selected records
`Select All`	Ctrl+A	Select all list rows
`main_menu`	Ctrl+ArrowUp	Open the main menu modal
`#sys_quick_menu`	Ctrl+ArrowDown	Open quick-search menu
`sys_run_always`	(none)	Fires on every page load

The main menu itself is a global action: name = 'main_menu', action = 'stored_procedure', global = true, type = 'hidden'.

globalsystemmain_menuscope

Chapter 03

The params Column

params JSON

The params column — JSON configuration for actions

The params column is a JSON string passing configuration to the framework or React frontend.

1. Pathname navigation


{"pathname": "/card_name"}
{"pathname": "/card_name/@card_id/child"}

@card_id is replaced with the current card ID. {selectedId} is replaced with the selected row ID.

2. Button CSS class override


{"className": "btn-danger"}
{"className": "btn-tracy"}

Use btn-danger for destructive actions, btn-tracy for primary buttons.

3. Blacklisted targets


{"blacklistedTargets": ["INPUT"]}

Prevents the keycode from firing when the cursor is inside an input field.

4. Combined


{"className": "btn-danger", "pathname": "/my_card/@card_id/sub"}

T‑SQL

-- Zero-code navigation
INSERT INTO api_actions (name, display_name, action, type, keycode, action_order, global, params)
VALUES ('go_orders', 'Orders', 'pathname', 'list_form', 'Alt+O', 10, 1, N'{"pathname":"/orders"}')

-- Danger button with navigation
INSERT INTO api_actions (name, display_name, action, type, keycode, action_order, global, params)
VALUES ('go_admin', 'Admin', 'pathname', 'modal_only', 'A', 10, 1, N'{"className":"btn-danger","pathname":"/admin"}')

paramsjsonpathnameclassNameblacklistedTargetsnavigation

Chapter 04

Navigation

Navigation patterns overview

Method	Use when
`action = 'pathname'` + `params`	Pure navigation, no T-SQL needed
`EXEC sp_api_goto @path`	Navigate after running T-SQL logic
`EXEC sp_api_modal_route @name, @pathname`	Clickable link inside a modal
`EXEC sp_api_modal_pathname @path`	Navigate and close modal in one step

Path conventions


/card_name              list view
/card_name/42           form view of record 42
/card_name/42/child     child card filtered by record 42
/card_name/0            new record form (insert mode)

T‑SQL

-- Navigate to a card
DECLARE @target_path nvarchar(256) = N'/orders'
EXEC sp_api_goto @path = @target_path

-- Navigate to new record form
SET @path += '/0'
EXEC sp_api_goto @path

-- Navigate with filter
DECLARE @filter nvarchar(2000) = N'?filter=status.swhas(open)'
EXEC sp_api_goto N'/orders', @filter

-- Link inside modal
DECLARE @link_label nvarchar(128) = N'Go to record'
EXEC sp_api_modal_route @name = @link_label, @pathname = @path, @key = 'Enter'

-- Navigate and close modal
EXEC sp_api_modal_pathname @path, NULL

navigationgotopathnamemodal_routepath

Navigation

sp_api_goto — navigate from T-SQL

sp_api_goto pushes a new path to the browser from inside a T-SQL action script.

Signature


EXEC sp_api_goto @path [, @search] [, @referer_path] [, @referer_search]

Parameter	Purpose
`@path`	The target path
`@search`	Optional query string
`@referer_path`	Override back-navigation path
`@referer_search`	Override back-navigation query string

T‑SQL

-- Navigate to a card
DECLARE @target_path nvarchar(256) = N'/orders'
EXEC sp_api_goto @path = @target_path

-- Navigate with filter
DECLARE @search nvarchar(2000) = N'?filter=status.swhas(open)'
EXEC sp_api_goto @path = N'/orders', @search = @search

-- New record form of current card
SET @path += '/0'
EXEC sp_api_goto @path

-- Child card filtered by current record
DECLARE @child_path nvarchar(256) = CONCAT(N'/orders/', @id, N'/order_lines')
EXEC sp_api_goto @child_path

navigationsp_api_gotopathsearchfilter

Chapter 05

Modal Dialogs

Modal dialogs — the render, read, act pattern

Modal dialogs are built in T-SQL using the sp_api_modal_* procedure family.

The three phases


1. READ   — read button/input state from the previous render
2. ACT    — if a button was clicked, do the work and return
3. RENDER — render the modal for this cycle

This is a stateful loop: the script runs once per user interaction. On the first call nothing has been posted, so it falls through to RENDER. When the user clicks a button, the script runs again and ACT fires.

The READ phase must come before RENDER. Rendering first overwrites the button state.

T‑SQL

-- Standard modal pattern
DECLARE @btn_save nvarchar(50)

-- PHASE 1: READ
EXEC sp_api_modal_get_value '@btn_save', @btn_save OUT

-- PHASE 2: ACT
IF @btn_save IS NOT NULL
BEGIN
    INSERT INTO my_table (name) VALUES (@my_input)
    EXEC sp_api_modal_clear
    EXEC sp_api_toast N'Saved!'
    RETURN
END

-- PHASE 3: RENDER
EXEC sp_api_modal_modal
EXEC sp_api_modal_text N'My heading', 'h3'
EXEC sp_api_modal_input '@my_input', @my_input OUT, 'text', 'Enter value...'
EXEC sp_api_modal_button '@btn_save', N'Save', @btn_save OUT, 'btn-primary', 'Enter'

modalpatternrenderreadactsp_api_modal

Modal Dialogs

sp_api_modal_* procedure reference

Container

Procedure	Purpose
`sp_api_modal_modal [@class]`	Opens the modal container
`sp_api_modal_clear`	Closes the modal
`sp_api_modal_print`	Flush render before a slow operation

Display

Procedure	Purpose
`sp_api_modal_text @text, @class`	Text or heading (h1-h4, code, error, text-danger)
`sp_api_modal_table @tmptable`	Renders temp table as a grid
`sp_api_modal_html @html, @iframe`	Raw HTML or iframe

Inputs

Procedure	Purpose
`sp_api_modal_input @name, @value OUT, @type`	Text/number input
`sp_api_modal_date @name, @value OUT`	Date picker
`sp_api_modal_select @card_name, @value OUT`	Card-based picker
`sp_api_modal_get_value @name, @value OUT`	Read posted value
`sp_api_modal_get @name, @value OUT`	Read session value

Buttons

Procedure	Purpose
`sp_api_modal_button @name, @value, @out OUT, @class, @key`	Button
`sp_api_modal_alert @title, @subtitle`	Blocking confirmation
`sp_api_modal_route @name, @pathname, @key`	Navigation link
`sp_api_modal_download @filename, @mimetype, @base64`	File download
`sp_api_modal_csv @tmptable, @filename`	CSV download

Session

Procedure	Purpose
`sp_api_modal_set @name, @value`	Store session value
`sp_api_modal_get @name, @value OUT`	Retrieve session value
`sp_api_toast @text, @class`	Brief toast notification

modalsp_api_modalreferenceprocedures

Modal Dialogs

Modal table column display formatting

Column alias suffixes control how data displays in sp_api_modal_table:

Suffix	Effect
`[Name@]`	Right-align (numeric)
`[Name@:2]`	Right-align, 2 decimal places
`[Name*]`	Bold / section header
`[Name**]`	Bold section header (stronger)
`[Name~css-class]`	Apply Bootstrap class to cell
`[Name~col-sm-2]`	Fixed column width
`[Name~text-danger]`	Red text
`[Name~bg-warning]`	Warning background

T‑SQL

SELECT
    [Week]       = DATEPART(ww, created_date),
    [Amount@:2]  = SUM(amount),   -- right-align, 2 decimals
    [Status*]    = status         -- bold column
INTO #report
FROM my_transactions
GROUP BY DATEPART(ww, created_date), status

DECLARE @order nvarchar(128) = N'ORDER BY [Week] DESC'
EXEC sp_api_modal_table @tmptable = N'#report', @orderby = @order

modaltableformattingcolumnaliassuffix

Chapter 06

Statusbar & sys_run_always

Statusbar

The statusbar — persistent UI feedback

A persistent bar at the bottom of every screen. Driven by session state keys.

Key	Position	Use
`statusbar_1`	Left	Primary status message
`statusbar_2`	Right	Secondary message
`statusbar_all_green`	Full bar	Green override
`statusbar_all_red`	Full bar	Red override
`statusbar_all_blue`	Full bar	Blue override
`statusbar_all_yellow`	Full bar	Yellow override

The statusbar_all_* keys take priority over individual slots.

sp_api_statusbar signature


EXEC sp_api_statusbar @text, @class, @position
-- @position: 0 = left, 1 = right

T‑SQL

-- Set a full-bar warning
DECLARE @msg nvarchar(256) = N'MAINTENANCE MODE ACTIVE'
EXEC sp_api_modal_set N'statusbar_all_red', @msg

-- Clear it
EXEC sp_api_modal_set N'statusbar_all_red', NULL

-- Dynamic left-slot
DECLARE @sync_msg nvarchar(128) = CONCAT(N'Last sync: ', CONVERT(nvarchar, GETDATE(), 120))
EXEC sp_api_modal_set N'statusbar_1', @sync_msg

-- Read and display in statusbar
DECLARE @status nvarchar(100)
EXEC sp_api_modal_get N'workdate', @status OUT
EXEC sp_api_statusbar @status, N'bg-danger col-sm-1 text-truncate', 0

statusbarsessionsp_api_statusbarsp_api_modal_setfeedback

Statusbar

sys_run_always — the always-on background action

sys_run_always is a special global action with type = 'hidden' and no keycode. It fires on every page navigation automatically.

Ideal for: statusbar updates, session state checks, daily startup logic, application-wide alerts.

Priority chain pattern


1. statusbar_all_red    show red bar, RETURN
2. statusbar_all_blue   show blue bar, RETURN
3. statusbar_all_green  show green bar, RETURN
4. statusbar_all_yellow show yellow bar, RETURN
5. Show individual slot values normally

T‑SQL

-- sys_run_always: daily reset + priority chain
DECLARE @status nvarchar(256)

-- Daily reset
EXEC sp_api_modal_get N'last_access_date', @status OUT
IF @status IS NULL OR @status <> dbo.strdate(NULL)
BEGIN
    EXEC sp_api_toast N'Good morning!'
    EXEC sp_api_modal_set N'workdate', NULL
    EXEC sp_api_modal_set N'last_access_date', dbo.strdate(NULL)
END

-- Priority chain
EXEC sp_api_modal_get N'statusbar_all_red', @status OUT
IF @status IS NOT NULL
BEGIN
    EXEC sp_api_statusbar @status, N'bg-danger col-sm-3 text-truncate', 0
    RETURN
END

-- Normal slots
EXEC sp_api_modal_get N'workdate', @status OUT
EXEC sp_api_statusbar @status, N'col-sm-1 text-truncate', 0
EXEC sp_api_statusbar @card_name, N'col-sm-1 text-truncate text-right', 1

sys_run_alwaysstatusbarworkdatesessionalways

Chapter 07

Multi-select

Working with multiple selected records (@ids)

When a user selects multiple rows, @ids contains a comma-separated string of all selected primary key values.

The cursor loop pattern


DECLARE @cursor_ids CURSOR, @cid INT
SET @cursor_ids = CURSOR LOCAL FAST_FORWARD FOR
    SELECT value FROM STRING_SPLIT(@ids, ',')
OPEN @cursor_ids
FETCH NEXT FROM @cursor_ids INTO @cid
WHILE @@FETCH_STATUS = 0
BEGIN
    -- process @cid here
    FETCH NEXT FROM @cursor_ids INTO @cid
END
CLOSE @cursor_ids
DEALLOCATE @cursor_ids
EXEC sp_api_modal_select_all_clear

For heavy operations, queue a background task per record instead of processing inline.

T‑SQL

-- Process multiple selected records
DECLARE @cursor_ids CURSOR, @cid INT, @toast_msg nvarchar(200)
SET @cursor_ids = CURSOR LOCAL FAST_FORWARD FOR
    SELECT value FROM STRING_SPLIT(@ids, ',')
OPEN @cursor_ids
FETCH NEXT FROM @cursor_ids INTO @cid
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @toast_msg = CONCAT(N'Processing record ', @cid)
    EXEC sp_api_toast @toast_msg
    EXEC @main_db.dbo.sp_process_record @id = @cid
    FETCH NEXT FROM @cursor_ids INTO @cid
END
CLOSE @cursor_ids
DEALLOCATE @cursor_ids
EXEC sp_api_modal_select_all_clear

multi-select@idscursorloopstring_splitbulk

Chapter 08

Queuing Work from Actions

Background Tasks

Offloading work with sp_api_add_sql_task

Queues a T-SQL batch in api_sql_tasks for background execution, preventing browser timeouts.

Signature


EXEC sp_api_add_sql_task @sql, @description

Parameter	Purpose
`@sql`	The T-SQL batch to execute
`@description`	Label shown in the task monitor

When to use

Processing large numbers of records in a multi-select loop

Stored procedures that take more than a few seconds

Operations that should not block the UI

T‑SQL

-- Queue one task per selected record
DECLARE @cursor_ids CURSOR, @cid INT, @sqltask nvarchar(max), @task_desc nvarchar(200)
SET @cursor_ids = CURSOR LOCAL FAST_FORWARD FOR
    SELECT value FROM STRING_SPLIT(@ids, ',')
OPEN @cursor_ids
FETCH NEXT FROM @cursor_ids INTO @cid
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @task_desc = CONCAT(N'Processing record ', @cid)
    EXEC sp_api_toast @task_desc
    SET @sqltask = CONCAT(N'EXEC @main_db.dbo.sp_process_record @id = ', @cid)
    EXEC sp_api_add_sql_task @sql = @sqltask, @description = @task_desc
    FETCH NEXT FROM @cursor_ids INTO @cid
END
CLOSE @cursor_ids
DEALLOCATE @cursor_ids
EXEC sp_api_modal_select_all_clear

backgroundtasksp_api_add_sql_taskqueueasyncperformance

Chapter 09

Access Control

Role-based access with role and deny_role

Both api_actions and api_card_actions support role-based visibility.

Column	Behaviour
`role`	Only visible to users with this role. NULL = all.
`deny_role`	Hidden from users with this role.

Multiple roles: comma-separated 'admin,designer'

Special role values

Value	Meaning
`admin`	Administrators only
`crud_create`	Only when user has create rights
`crud_delete`	Only when user has delete rights
`disabled`	Effectively disabled for everyone

dbo.hasRole('rolename') returns 1 if current user has the role. Accepts comma-separated list.

T‑SQL

-- Admin-only button
INSERT INTO api_card_actions (card_id, name, display_name, action, type, role)
VALUES (@card_id, 'sp_admin_tool', 'Admin Tool', 'stored_procedure', 'list_form', 'admin')

-- Soft-disable without deleting
UPDATE api_card_actions SET role = 'disabled' WHERE name = 'sp_old_feature'

roledeny_roleaccesssecuritycrudadminpermissions

Chapter 10

pre_declare & Curly Braces

pre_declare — framework-injected variables

The pre_declare column lists variables the framework declares and populates from client state before the action script runs.

Format

Comma-separated variable declarations:


@button_name, @company_id int, @date nvarchar(128)

If no type is specified, nvarchar(max) is assumed.

How it works

The framework calls sp_api_modal_get_value for each declared variable, initialising it from what the client posted. Eliminates boilerplate in modal scripts.

When to use

When the action has modal inputs that need to survive across the render-read-act cycle.

T‑SQL

-- pre_declare = '@search, @button'
-- Framework automatically injects before your body:
-- DECLARE @search nvarchar(max)
-- EXEC sp_api_modal_get_value '@search', @search OUT
-- DECLARE @button nvarchar(max)
-- EXEC sp_api_modal_get_value '@button', @button OUT

-- Your script body uses them directly:
EXEC sp_api_modal_input @name = '@search', @value = @search OUT, @focus = 1
EXEC sp_api_modal_button @name = '@button', @value = 'Search', @valueout = @button OUT, @class = 'btn-primary', @key = 'Enter'
IF @button = 'Search'
BEGIN
    SELECT TOP 50 id, name INTO #results FROM my_table WHERE name LIKE @search + '%'
    EXEC sp_api_modal_table @tmptable = N'#results'
END

pre_declarevariablesframeworkmodalstate

pre_declare & Curly Braces

Curly brace syntax — reading values from other card contexts

The {[card_name].field_name} syntax reads field values from related cards in the current navigation context, resolved at runtime.

Syntax forms

Syntax	Reads
`{[card_name].field_name}`	Value from the open record on `card_name`
`{[card_name].field_name.display_col}`	Display value of a picklist field
`{field_name}`	Value from the current card

Constraints

Only works in unparsed_sql (inline scripts)

Does NOT work inside stored procedure bodies

Returns NULL if the referenced card is not in the navigation stack

T‑SQL

-- Read parent order ID from navigation context
DECLARE @order_id INT = {[orders].id}
IF @order_id IS NULL SET @order_id = {[purchase].id}
IF @order_id IS NULL SET @order_id = @id  -- fallback

-- Read a picklist display value
DECLARE @customer_name NVARCHAR(200) = {[orders].customer_id.company_name}

-- Multi-context resolution
DECLARE @item_id INT = {[order_lines].item_id}
IF @item_id IS NULL SET @item_id = {[items].id}
IF @item_id IS NULL SET @item_id = @id

curlycontextcardfieldcross-card

Chapter 11

Cookbook

Recipe: Confirmation before destructive action

sp_api_modal_alert is a blocking confirmation — the user must press Enter to proceed or Escape to cancel.

T‑SQL

-- Single confirmation
EXEC sp_api_modal_alert N'Delete this record?', N'This cannot be undone'

-- Script continues only if Enter was pressed
DELETE FROM my_table WHERE id = @id
EXEC sp_api_modal_clear
EXEC sp_api_toast N'Deleted!'

-- Double confirmation for truly destructive operations
EXEC sp_api_modal_alert N'Are you sure?', N'This will affect all records'
EXEC sp_api_modal_alert N'Really sure?', N'Last warning!'
EXEC sp_api_dangerous_operation

cookbookconfirmalertmodal_alertpattern

Cookbook

Recipe: Multi-button choice dialog

All buttons share the same @name. The framework fills @valueout with the label of the clicked button.

T‑SQL

DECLARE @button nvarchar(128)

EXEC sp_api_modal_text N'Choose an action', 'h4'
EXEC sp_api_modal_button @name='button', @value='Option A', @valueout=@button OUT, @class='btn-primary', @key='F1'
EXEC sp_api_modal_button @name='button', @value='Option B', @valueout=@button OUT, @class='btn-warning', @key='F2'
EXEC sp_api_modal_button @name='button', @value='Option C', @valueout=@button OUT, @class='btn-danger',  @key='F3'

IF @button = 'Option A' BEGIN ... EXEC sp_api_modal_clear RETURN END
IF @button = 'Option B' BEGIN ... EXEC sp_api_modal_clear RETURN END
IF @button = 'Option C' BEGIN ... EXEC sp_api_modal_clear RETURN END

cookbookbuttonchoicemodalbranchpattern

Cookbook

Recipe: Input form with server-side validation

The key: set @button = NULL to fall through to re-render on validation failure, rather than returning.

T‑SQL

DECLARE @name nvarchar(128), @amount nvarchar(20), @button nvarchar(128)

-- READ
EXEC sp_api_modal_get_value '@button', @button OUT
EXEC sp_api_modal_get_value '@name',   @name   OUT
EXEC sp_api_modal_get_value '@amount', @amount OUT

-- ACT
IF @button IS NOT NULL
BEGIN
    IF @name IS NULL OR LEN(TRIM(@name)) = 0
    BEGIN
        EXEC sp_api_modal_text N'Name is required', 'text-danger'
        SET @button = NULL  -- fall through to re-render
    END
    ELSE IF TRY_CAST(@amount AS decimal(10,2)) IS NULL
    BEGIN
        EXEC sp_api_modal_text N'Amount must be a number', 'text-danger'
        SET @button = NULL
    END
    ELSE
    BEGIN
        INSERT INTO my_table (name, amount) VALUES (@name, TRY_CAST(@amount AS decimal(10,2)))
        EXEC sp_api_modal_clear
        EXEC sp_api_toast N'Saved!'
        RETURN
    END
END

-- RENDER
EXEC sp_api_modal_modal
EXEC sp_api_modal_text N'New Record', 'h4'
EXEC sp_api_modal_input '@name',   @name   OUT, 'text',   'Enter name...'
EXEC sp_api_modal_input '@amount', @amount OUT, 'number', '0.00'
EXEC sp_api_modal_button '@button', N'Save', @button OUT, 'btn-primary', 'Enter'

cookbookinputformvalidationmodalpattern

Cookbook

Recipe: Instant report — search + buttons + results

A search field, multiple report type buttons, and a temp table result. The default button value auto-runs the first report on first load.

T‑SQL

DECLARE @search nvarchar(128)
DECLARE @button nvarchar(128) = N'Customers'  -- default

-- READ
EXEC sp_api_modal_get_value '@search', @search OUT
EXEC sp_api_modal_get_value '@button', @button OUT

-- RENDER controls first
EXEC sp_api_modal_input @name='@search', @value=@search OUT, @focus=1
EXEC sp_api_modal_button @name='@button', @value='Customers', @valueout=@button OUT, @class='btn-primary', @key='F1'
EXEC sp_api_modal_button @name='@button', @value='Products',  @valueout=@button OUT, @class='btn-primary', @key='F2'
EXEC sp_api_modal_print

-- ACT
IF @button = 'Customers'
BEGIN
    SELECT TOP 50 id, name INTO #r FROM customers WHERE name LIKE @search + '%'
    EXEC sp_api_modal_table @tmptable = N'#r'
END
IF @button = 'Products'
BEGIN
    SELECT TOP 50 id, name INTO #r2 FROM products WHERE name LIKE @search + '%'
    EXEC sp_api_modal_table @tmptable = N'#r2'
END

cookbookreportinstantsearchmodal_printpattern

Cookbook

Recipe: Navigate to card from global action

Two approaches: T-SQL navigation with logic, or zero-code pathname action.

T‑SQL

-- T-SQL navigation
DECLARE @target_path nvarchar(256) = N'/orders'
EXEC sp_api_goto @target_path

-- With pre-applied filter
DECLARE @search nvarchar(2000) = N'?filter=status.swhas(open)'
EXEC sp_api_goto @path = N'/orders', @search = @search

-- Zero-code (no T-SQL at all)
INSERT INTO api_actions (name, display_name, action, type, keycode, action_order, global, params)
VALUES ('go_orders', 'Orders', 'pathname', 'list_form', 'Alt+O', 10, 1, N'{"pathname":"/orders"}')

cookbooknavigationgotoglobalmenuaction

Cookbook

Recipe: Keycode conventions

Rules

Context	Rule	Examples
Top-level global action	Never single letters (captured by list search)	`Alt+D`, `F8`, `Ctrl+F4`
Inside a sub-menu	Single letters are safe	`D`, `A`, `S`
Hidden shortcuts	Use modifier keys	`Ctrl+Alt+S`, `Ctrl+Insert`

Reserved keycodes

Keycode	Framework action
`Escape`	sys_escape
`Insert`	sys_insert
`Ctrl+Delete`	sys_delete
`Ctrl+A`	Select All
`Ctrl+ArrowUp`	main_menu
`Ctrl+ArrowDown`	sys_quick_menu

cookbookkeycodekeyboardshortcutconvention

Chapter 12

Modal Select Pickers

Modal Select

sp_api_modal_select — card-based picker

sp_api_modal_select renders a search-and-select picker backed by a TSQL.APP card.

Signature


EXEC sp_api_modal_select
    @card_name, @value OUT
  [ , @focus ] [ , @reducer ] [ , @placeholder ]
  [ , @top ] [ , @no_isam ] [ , @modalRouteSql ]

Returns NULL in @value if user dismisses without selecting. Always check for NULL before using.

T‑SQL

-- Basic picker
DECLARE @selected_id nvarchar(32)
EXEC sp_api_modal_select @card_name = N'customer', @value = @selected_id OUT, @focus = 1
IF @selected_id IS NULL RETURN
EXEC sp_api_modal_clear

-- Filtered picker
DECLARE @filter nvarchar(256) = N'status = ''active'''
EXEC sp_api_modal_select @card_name = N'customer', @value = @selected_id OUT, @reducer = @filter

-- Quick-menu: pick a card and navigate
DECLARE @menu_id nvarchar(max), @placeholder nvarchar(128) = N'Search...'
EXEC sp_api_modal_select @card_name = N'menu', @value = @menu_id OUT, @focus = 1, @placeholder = @placeholder, @no_isam = 1
IF @menu_id IS NULL RETURN
EXEC sp_api_modal_clear
DECLARE @nav_path nvarchar(256)
SELECT @nav_path = CONCAT(N'/', name) FROM api_card WHERE id = @menu_id
EXEC sp_api_modal_pathname @nav_path, NULL

modalselectpickercardsp_api_modal_selectsearch

Modal Select

sp_api_modal_select_table — temp table picker

sp_api_modal_select_table backs the picker with a custom temp table instead of a card.

Requirements

id column: value returned on selection

name column: label displayed to user

Multi-step wizard pattern

Chain multiple calls to build guided selection flow.

T‑SQL

-- Step 1: pick a table
SELECT id = name, name INTO #tables FROM sys_tables ORDER BY name
EXEC sp_api_modal_select_table @tmptable = N'#tables', @value = @chosen_table OUT
IF @chosen_table IS NULL RETURN

-- Step 2: pick a column from that table
DECLARE @obj_id INT = (SELECT object_id FROM sys_tables WHERE name = @chosen_table)
SELECT id = name, name INTO #columns FROM sys_columns WHERE object_id = @obj_id
EXEC sp_api_modal_select_table @tmptable = N'#columns', @value = @chosen_column OUT

modalselect_tablepickertemp_tablewizardsp_api_modal_select_table

Modal Select

sp_api_modal_choose — quick option list

sp_api_modal_choose renders a compact button-per-option chooser from a comma-separated list.

Signature


EXEC sp_api_modal_choose @list, @value OUT [, @startkey]

Key assignment

@startkey = 1 — F1, F2, F3...

@startkey = NULL — first letter of each option (space prefix = no key)

@startkey = 3 — starts at F3

T‑SQL

DECLARE @choice nvarchar(128)
EXEC sp_api_modal_choose @list = N'Create new,Use existing,Cancel', @value = @choice OUT, @startkey = 1
IF @choice = 'Cancel' OR @choice IS NULL RETURN

EXEC sp_api_modal_choose @list = N'Predefined,Custom, Skip', @value = @choice OUT, @startkey = NULL
IF @choice = 'Predefined' BEGIN ... END
IF @choice = 'Custom'     BEGIN ... END

modalchooseoptionssp_api_modal_choosebuttonsshortcut

Chapter 13

File Handling

File handling — upload, download, preview

TSQL.APP has built-in file storage backed by the api_files table.

api_files columns


name        -- original filename
filename    -- internal storage path
mimetype    -- MIME type
filesize    -- bytes
context     -- card name
context_id  -- record id
user_name   -- uploader

System actions

Action	Purpose
`sys_file_upload`	Handle file upload
`sys_file_download`	Read and send file to browser
`sys_file_delete`	Delete record and file

fileuploaddownloadpreviewapi_filesfile_handling

File Handling

sp_api_readFile and sp_api_modal_download

Two-step pattern to deliver a file to the browser.

Step 1: Read


EXEC sp_api_readFile @filename, @base64data OUT

Step 2: Send


EXEC sp_api_modal_download @filename, @mimetype, @base64

CSV from temp table


EXEC sp_api_modal_csv @tmptable, @filename [, @ansi = 1]

@ansi = 1 for Excel-compatible Windows-1252 encoding.

T‑SQL

-- Download a stored file
DECLARE @name nvarchar(128), @mimetype nvarchar(128), @filename nvarchar(512), @b64 nvarchar(max)

SELECT TOP 1 @name = name, @mimetype = mimetype, @filename = filename
FROM api_files WHERE id = @file_id AND context = @card_name AND context_id = @id

IF @name IS NULL BEGIN EXEC sp_api_toast N'File not found' RETURN END

EXEC sp_api_readFile @filename, @b64 OUT
EXEC sp_api_modal_download @filename = @name, @mimetype = @mimetype, @base64 = @b64

-- CSV export
SELECT id, name, amount INTO #export FROM my_table WHERE status = 'active'
DECLARE @csv_fname nvarchar(128) = N'export.csv'
EXEC sp_api_modal_csv @tmptable = N'#export', @filename = @csv_fname, @ansi = 1

filedownloadsp_api_readFilesp_api_modal_downloadcsvbinary

Chapter 14

CSV & Excel Export

Exporting data — CSV and Excel

sp_api_modal_csv


EXEC sp_api_modal_csv @tmptable, @filename [, @ansi] [, @orderby]

@ansi = 1 = Windows-1252 encoding (better for Excel).

sp_sys_excel_from_list

Global action (Alt+X) that exports whatever the user currently sees on screen to Excel.

	sp_api_modal_csv	sp_sys_excel_from_list
Data source	Your temp table	Current card list view
Format	CSV	Excel (.xlsx)
Filters	You control	Uses active list filters

T‑SQL

SELECT
    id,
    [Customer]    = customer_name,
    [Balance@:2]  = balance
INTO #export
FROM customers
WHERE status = 'active'

DECLARE @fname nvarchar(128) = N'active_customers.csv'
EXEC sp_api_modal_csv
    @tmptable = N'#export'
  , @filename = @fname
  , @ansi     = 1
  , @orderby  = N'ORDER BY [Customer]'

exportcsvexcelsp_api_modal_csvsp_sys_excel_from_listdownload

Chapter 15

Session State

sp_api_modal_get and sp_api_modal_set — user session state

Per-user key-value store that persists across page navigations for the session duration.


EXEC sp_api_modal_set @name, @value   -- store (NULL = delete)
EXEC sp_api_modal_get @name, @value OUT  -- retrieve

Common session keys

Key	Purpose
`workdate`	Current working date
`last_access_date`	Tracks daily first-access
`statusbar_1` / `statusbar_2`	Statusbar slot content
`statusbar_all_*`	Full-bar override colours
`forcedRole`	Temporarily impersonate a role
`nightmode`	UI theme toggle

get/set = persistent session state across actions. pre_declare/get_value = transient modal state within a single render cycle.

T‑SQL

-- Store and read
EXEC sp_api_modal_set N'workdate', N'2025-03-15'
DECLARE @workdate nvarchar(128)
EXEC sp_api_modal_get N'workdate', @workdate OUT
DECLARE @workdate_msg nvarchar(200) = CONCAT(N'Workdate: ', @workdate)
EXEC sp_api_toast @workdate_msg

-- Toggle pattern
DECLARE @flag nvarchar(10)
EXEC sp_api_modal_get N'my_feature_flag', @flag OUT
IF @flag IS NOT NULL EXEC sp_api_modal_set N'my_feature_flag', NULL
ELSE                 EXEC sp_api_modal_set N'my_feature_flag', N'on'

-- Daily reset
DECLARE @last nvarchar(128)
EXEC sp_api_modal_get N'last_access_date', @last OUT
IF @last IS NULL OR @last <> dbo.strdate(NULL)
BEGIN
    EXEC sp_api_modal_set N'workdate', NULL
    EXEC sp_api_modal_set N'last_access_date', dbo.strdate(NULL)
    EXEC sp_api_toast N'Good morning!'
END

sessionstatesp_api_modal_setsp_api_modal_getworkdatepersist

Session State

sp_api_modal_post and sp_api_modal_save — programmatic record creation

Create records from T-SQL using the same pipeline as a normal form save.


EXEC sp_api_modal_post @card, @column, @value, @id  -- set field (0 = new)
EXEC @new_id = sp_api_modal_save @card, @id         -- commit, returns new id

Before creating a new record, clear any unfinished draft:


DELETE FROM api_storage
WHERE card_id = (SELECT id FROM api_card WHERE name = N'orders')
  AND child_id = 0 AND user_id = @user_id

T‑SQL

-- Create a new record and navigate to it
DELETE FROM api_storage
WHERE card_id = (SELECT id FROM api_card WHERE name = N'orders')
  AND child_id = 0 AND user_id = @user_id

EXEC sp_api_modal_post N'orders', N'customer_id', @selected_id, 0
EXEC sp_api_modal_post N'orders', N'status',      N'draft',     0
EXEC sp_api_modal_post N'orders', N'created_by',  @user_name,   0

DECLARE @new_id nvarchar(32)
EXEC @new_id = sp_api_modal_save N'orders', 0

DECLARE @nav_path nvarchar(256) = CONCAT(N'/orders/', @new_id)
EXEC sp_api_goto @nav_path

sessionpostsavesp_api_modal_postsp_api_modal_savecreaterecord

Chapter 16

Common Pitfalls & Debugging

Common Pitfalls

Pitfalls — things that silently fail

1. Single-letter keycodes captured by list search

Use Alt+X, F8 at top level. Single letters only inside sub-menus.

2. @main_db only resolves inside TSQL.APP context

Running action SQL directly in SSMS leaves @main_db as a literal string.

3. RENDER before READ loses button state

Always READ — ACT — RENDER. Rendering first overwrites state.

4. Temp table name collisions in multi-step modals

Use different names per branch or DROP TABLE IF EXISTS at top of each branch.

5. sp_api_modal_clear does not stop execution

Always follow EXEC sp_api_modal_clear with RETURN.

6. SCOPE_IDENTITY() out of scope after sp_api_card_create_cache

Always re-query: SELECT id FROM api_card WHERE name = 'my_card'.

7. action_order = 0 hides the button

Use a positive integer. Zero = hidden.

8. Cache columns must never be set manually

Never write to list_select_cache, select_cache, declare_cache.

9. sp_api_modal_print needed before slow operations

Flush controls to the client before starting a slow query.

10. Curly braces only work in unparsed_sql

Not in stored procedure bodies.

pitfallsgotchaserrorsbugstipswarningscommon_mistakes

Common Pitfalls

Debugging tips

sp_api_toast — quick variable inspection


EXEC sp_api_toast @my_variable

sp_api_modal_text with code class


EXEC sp_api_modal_text @my_sql, N'code'

sys_action_help (Alt+H)

Built-in interactive reference for all sp_api_modal_* procedures.

api_errors card

Unhandled exceptions logged with full stack trace. Navigate to /api_errors?ord=25005d.

error column on api_actions

Compile errors written here. Check when an action silently does nothing.

TRY/CATCH pattern


BEGIN TRY ... END TRY
BEGIN CATCH
    DECLARE @err nvarchar(2000) = ERROR_MESSAGE()
    EXEC sp_api_toast @err, N'bg-danger text-white'
END CATCH

T‑SQL

-- Dump all context variables to a modal table
SELECT [Variable] = v, [Value] = val
INTO #debug
FROM (VALUES
    ('@id',        @id),
    ('@ids',       @ids),
    ('@card_name', @card_name),
    ('@user_name', @user_name),
    ('@parent_id', CAST(@parent_id AS nvarchar)),
    ('@is_form',   CAST(@is_form AS nvarchar)),
    ('@path',      @path)
) t(v, val)
EXEC sp_api_modal_table @tmptable = N'#debug'

debuggingtoasterrorsapi_errorstry_catchinspectdiagnosis

Chapter 17

Field Attributes

Field attributes overview

Field attributes control how fields look and behave in the form view.

Table	Purpose
`api_attributes`	Defines available attribute types with name, type, and default formula
`api_card_field_attributes`	Assigns attribute values to specific fields

The sql column is a dynamic T-SQL expression

Evaluated at render time with full access to context variables:

is_hidden = IIF(@status = 'closed', 1, 0) — conditional visibility

is_updateable = dbo.hasRole('admin') — role-gated editability

display = IIF(@type = 'A', 'Label A', 'Label B') — dynamic label

defval — fallback expression when no attribute row exists

is_updateable: IIF(name = @ref_column AND @parent_id IS NOT NULL, 0, is_updateable) — auto-locks FK fields

display: SELECT display FROM get_lang(name, source_table) — auto-translates labels

attributesapi_attributesapi_card_field_attributesfieldsoverview

Field Attributes

element — controls the field input widget

Determines what UI control renders for a field.

Value	Widget
`Select`	Picklist dropdown (requires `extra` attribute)
`Text`	Single-line input
`Textarea`	Multi-line text area
`Monaco`	Monaco code editor
`Sql`	SQL-aware Monaco editor
`Html`	Rich text editor
`Table`	Embedded child card
`Image`	Image viewer / upload
`BarChart`	Bar chart visualization

element = Select must always be paired with extra. Insert and delete them together.

Dynamic: IIF(@unbound = 1, NULL, 'Select') — returns NULL for default input.

T‑SQL

-- Insert element + extra as a pair
DECLARE @attr_element INT = (SELECT id FROM api_attributes WHERE name = 'element')
DECLARE @attr_extra   INT = (SELECT id FROM api_attributes WHERE name = 'extra')

DELETE FROM api_card_field_attributes
WHERE field_id = @my_field_id AND attr_id IN (@attr_element, @attr_extra)

INSERT INTO api_card_field_attributes (attr_id, sql, field_id) VALUES
    (@attr_element, N'''Select''',                              @my_field_id),
    (@attr_extra,   N'SELECT id, name FROM my_table FOR JSON AUTO', @my_field_id)

attributeselementwidgetselectmonacosqltextareatable

Field Attributes

extra, fill_with, is_hidden, is_updateable and other attributes

extra — picklist data source

Format	Example
Inline JSON	`N'[{"id":"A","name":"Option A"}]'`
FOR JSON query	`SELECT code, caption FROM my_codes FOR JSON AUTO`
Function call	`dbo.my_options()`
HTML template	`'<b>[[content]]</b>'` (display fields)

fill_with — default value on new records

Expression	Effect
`N'open'`	Pre-fill status
`@user`	Current user email
`@parent_id`	FK to parent
`dbo.strdate(NULL)`	Today's date
`NEWID()`	New GUID

is_hidden — show/hide field (1=hidden, 0=visible)

Common patterns:

IIF(@status = 'closed', 1, 0) — hide when closed

1 - dbo.hasRole('admin') — hide from non-admins

IIF(@parent_id IS NULL, 1, 0) — show only with parent

is_updateable — editable or read-only (1=editable, 0=readonly)

Common patterns:

dbo.hasRole('admin') — only admins can edit

IIF(@is_checked = 0, 1, 0) — lock after checked

IIF(@inherited = 1, 0, 1) — read-only for inherited records

mask, class_name, display, rows, valid, link

mask: Placeholder hint text inside the input

class_name: CSS classes on field container (code, h-300px, col-sm-12). | prefix = list view only

display: Override field label. 0 = hide label

rows: Textarea visible row count

valid: Returns 1 (valid) or 0 — blocks save when 0

link: Returns a URL — makes field value a hyperlink

attributesextrafill_withis_hiddenis_updateablemaskclass_namevalidlink

Chapter 18

Cards & Screen Definitions

Cards

api_card — the screen definition

One row = one screen. Multiple cards can share the same tablename.

Key columns

Column	Purpose
`name`	Unique identifier. Used in URLs and as `@card_name`
`tablename`	View/table that supplies list data (READ source)
`basetable`	Writable table for INSERT/UPDATE/DELETE
`id_column`	PK column name. MUST be set before `sp_api_card_create_cache`
`reducer`	SQL WHERE clause appended to every list query
`in_main_menu`	Bit — show in the main menu
`menu_keycode`	Keyboard shortcut to open from menu
`role` / `deny_role`	Role-based access control
`crud_create/read/update/delete`	SQL expression (truthy=allowed, `'1=0'`=denied)
`auto_refresh_seconds`	Auto-refresh interval
`header_sql`	SQL for a header row above the list
`on_update_sql`	SQL executed after every record save
`sorting_order`	Default ORDER BY
`record_limit`	Max rows returned

NEVER set list_select_cache, select_cache, or declare_cache manually. These are generated by sp_api_card_create_cache.

T‑SQL

-- Minimal card registration
INSERT INTO api_card (name, tablename, id_column)
VALUES ('my_card', 'v_my_view', 'id')

-- Look up the id by name (NEVER use SCOPE_IDENTITY across batches)
DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = 'my_card')

-- Build the cache
EXEC sp_api_card_create_cache @card_id = @card_id, @toast = 1

api_cardcardscreentablenamebasetablereducercachecrud

Cards

api_card_fields — columns in a card

One row = one column in the list or form view.

Column	Purpose
`card_id`	FK to `api_card`
`name`	Column name in the underlying view/table
`list_order`	Position in list. NULL or 0 = hidden
`detail_order`	Position in form. NULL or 0 = hidden
`sql`	Override expression for computed columns
`picklist_card_id`	FK to `api_card` — makes this a dropdown
`seek_order`	Makes column searchable
`instant_update`	Save on change without Save button

Use even numbers for list_order (2, 4, 6...) so new columns can be inserted without renumbering.

Always run sp_api_card_create_cache after adding or modifying fields.

T‑SQL

DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = 'my_card')

INSERT INTO api_card_fields (card_id, name, list_order, detail_order) VALUES
    (@card_id, 'customer_name',  2, 2),
    (@card_id, 'status',         4, 4),
    (@card_id, 'amount',         6, 6)

-- Picklist field linked to another card
DECLARE @cust_card INT = (SELECT id FROM api_card WHERE name = 'customer')
INSERT INTO api_card_fields (card_id, name, list_order, detail_order, picklist_card_id)
VALUES (@card_id, 'customer_id', 8, 8, @cust_card)

EXEC sp_api_card_create_cache @card_id = @card_id, @toast = 1

api_card_fieldsfieldscolumnslist_orderdetail_orderpicklistseeksql

Cards

api_card_children — drill-down navigation

One row = one navigation path from parent to child card.

Column	Purpose
`parent`	FK to `api_card` — navigated FROM
`child`	FK to `api_card` — navigated TO
`keycode`	Keyboard shortcut (commonly `Enter`)
`ref`	Child column filtered by parent's selected id
`unbound`	0 = `WHERE ref = parent.id`. 1 = custom reducer
`reducer`	Custom WHERE clause when `unbound = 1`
`is_hidden`	Navigation exists but no visible button
`action_order`	Display order

URL result: /{parent_card}/{parent_id}/{child_card}

T‑SQL

DECLARE @parent_id INT = (SELECT id FROM api_card WHERE name = 'orders')
DECLARE @child_id  INT = (SELECT id FROM api_card WHERE name = 'order_lines')

-- Simple bound child (WHERE order_id = parent.id)
INSERT INTO api_card_children (parent, child, keycode, ref, action_order)
VALUES (@parent_id, @child_id, 'Enter', 'order_id', 10)

-- Unbound child with custom filter
INSERT INTO api_card_children (parent, child, keycode, unbound, reducer, action_order)
VALUES (@parent_id, @child_id, 'Alt+H', 1, 'status = ''historical''', 20)

api_card_childrenchildrennavigationparentchilddrill-downrefunbound

Cards

sp_api_card_create_cache — building the SELECT cache

Compiles the card SELECT statements and validates against the live schema. Must be run after every structural change.


EXEC sp_api_card_create_cache @card_id [, @toast]

What it does: Introspects the view, builds list_select_cache, select_cache, declare_cache, validates with WHERE 1=0, writes errors to api_card.error.

When to run: After any change to api_card_fields, field SQL, tablename, reducer, or the underlying view definition.

Critical: id_column must be set before calling. If NULL, raises: sp_api_card_create_cache NO ID!

T‑SQL

DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = 'my_card')
EXEC sp_api_card_create_cache @card_id = @card_id, @toast = 1

-- Check for errors
DECLARE @err nvarchar(max)
SELECT @err = error FROM api_card WHERE id = @card_id
IF @err IS NOT NULL
BEGIN
    DECLARE @err_msg nvarchar(max) = CONCAT(N'Cache error: ', @err)
    EXEC sp_api_toast @err_msg
END

sp_api_card_create_cachecachecardcompilevalidatelist_select_cacheselect_cache

Cards

The five-step recipe for adding a new card

What requires code vs what does not

Feature	How	Code?
New screen / list view	View + `api_card` row	No
New column	`api_card_fields` row + rebuild cache	No
Drill-down navigation	`api_card_children` row	No
Filter / reducer	`api_card_actions` row `action=reducer`	No
Scheduled job	`api_sql_tasks` row	No
Button with logic	Stored procedure in business DB	Yes
Modal dialog	Stored procedure using `sp_api_modal_*`	Yes
Computed field	`api_card_fields.sql` expression	Inline SQL
Role-based visibility	`role`/`deny_role` on card/action/child	No
Add to main menu	`in_main_menu = 1` + `menu_keycode`	No

T‑SQL

-- STEP 1: Create view in business DB (must expose an id column)
-- CREATE OR ALTER VIEW dbo.v_my_feature AS SELECT id = ..., col_a, col_b FROM ...

-- STEP 2: Register the card
INSERT INTO api_card (name, tablename, id_column)
VALUES ('my_feature', 'v_my_feature', 'id')

-- STEP 3: Look up the card id by name
DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = 'my_feature')

-- STEP 4: Define visible columns (even numbers for easy re-ordering)
INSERT INTO api_card_fields (card_id, name, list_order, detail_order) VALUES
    (@card_id, 'customer_name', 2, 2),
    (@card_id, 'status',        4, 4),
    (@card_id, 'amount',        6, 6)

-- STEP 5: Build the cache
EXEC sp_api_card_create_cache @card_id = @card_id, @toast = 1

cardnew-cardrecipeviewapi_cardapi_card_fieldscachecookbook

Cards

Sub-menu groups and translations

Sub-menu groups

A sub-menu is an api_card_actions row acting as parent container. Child actions set group_id to the parent id. When the user presses the sub-menu keycode, a modal opens with child buttons — single letter keycodes are safe inside sub-menus.

SCOPE_IDENTITY() is safe for sub-menu group_id assignment (same batch).

Translations (api_global_fields)

Button labels are looked up in api_global_fields with context = 'menu' and the user's language. Right-clicking a button in the UI opens the translation editor.

T‑SQL

DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = 'my_card')

-- Parent sub-menu
INSERT INTO api_card_actions (card_id, name, display_name, action, keycode, type, action_order)
VALUES (@card_id, 'actions_menu', 'Actions', 'stored_procedure', 'Alt+A', 'list', 50)

DECLARE @group_id INT = SCOPE_IDENTITY()  -- safe: same batch

-- Child buttons (single letters OK inside sub-menu)
INSERT INTO api_card_actions (card_id, name, display_name, action, keycode, type, action_order, group_id)
VALUES
    (@card_id, 'sp_approve', 'Approve', 'stored_procedure', 'A', 'list', 10, @group_id),
    (@card_id, 'sp_reject',  'Reject',  'stored_procedure', 'R', 'list', 20, @group_id)

-- Add translations
INSERT INTO api_global_fields (name, lang, display, context) VALUES
    ('Approve', 'en', 'Approve',    'menu'),
    ('Approve', 'nl', 'Goedkeuren', 'menu')

submenugroup_idapi_card_actionsapi_global_fieldstranslationlanguagebutton-group

Chapter 19

Scheduled Task Runner

Scheduled Tasks

api_sql_tasks — the background task runner

A background task runner built into every TSQL.APP instance. Lives in {app}_proj.

How it works

Polls for rows where enabled = 1 and start_at <= GETDATE(). Sets started on pickup, executes sql, then sets completed. Errors go to the error column.

Key columns

Column	Purpose
`sql`	T-SQL batch to execute
`description`	Label in the task monitor
`start_at`	MUST be set — NULL is never picked up
`interval`	Repeat unit: `'minute'`, `'hour'`, `'day'`
`interval_number`	Repeat frequency (e.g. `5` with `'minute'` = every 5 min)
`repeat_task_in_seconds`	LEGACY — unused. Do not use.
`dont_start_when_running`	1 = skip if prior run still active
`delete_after_success`	1 = auto-delete after success
`on_error_sql`	T-SQL to run on failure
`user_id`	NULL = system task (hidden from per-user UI)
`monday`–`sunday`	Day restrictions

Single quotes inside sql must be doubled ('').

T‑SQL

-- One-shot
INSERT INTO api_sql_tasks (description, sql, start_at)
VALUES (N'One-time fix', N'UPDATE my_table SET status = ''ok'' WHERE status = ''err''', GETDATE())

-- Repeating every 5 minutes
INSERT INTO api_sql_tasks (description, sql, start_at, interval, interval_number, dont_start_when_running)
VALUES (N'Sync prices', N'EXEC @main_db.dbo.sp_sync_prices', GETDATE(), 'minute', 5, 1)

-- Self-disabling
INSERT INTO api_sql_tasks (description, sql, start_at, interval, interval_number, dont_start_when_running)
VALUES (
    N'Migrate records',
    N'DELETE TOP (1000) FROM my_table WHERE migrated = 0
IF @@ROWCOUNT = 0
    UPDATE api_sql_tasks SET enabled = 0 WHERE description = ''Migrate records''',
    GETDATE(), 'minute', 1, 1
)

api_sql_tasksbackgroundtaskscheduledrunnerintervalenabled

Scheduled Tasks

Monitoring, debugging, and re-triggering tasks

T‑SQL

-- Status dashboard
SELECT
    id, description, enabled, start_at, started, completed,
    [running_min] = CASE WHEN started IS NOT NULL AND completed IS NULL
                   THEN DATEDIFF(minute, started, GETDATE()) END,
    [status] = CASE
        WHEN error IS NOT NULL    THEN 'ERROR'
        WHEN completed IS NOT NULL THEN 'Done'
        WHEN started IS NOT NULL  THEN 'Running'
        WHEN enabled = 0          THEN 'Disabled'
        ELSE 'Waiting' END,
    LEFT(error, 200) AS error_preview
FROM api_sql_tasks
WHERE enabled = 1 OR completed > DATEADD(hour, -1, GETDATE())
ORDER BY COALESCE(started, start_at) DESC

-- Re-trigger a stalled task
UPDATE api_sql_tasks
SET start_at = GETDATE(), started = NULL, completed = NULL
WHERE id = @task_id

-- Re-enable and reschedule
UPDATE api_sql_tasks
SET enabled = 1, start_at = GETDATE(), started = NULL, completed = NULL, error = NULL
WHERE id = @task_id

api_sql_tasksmonitoringdebugstalledre-triggercancelerror

Chapter 20

REST API Endpoints

REST API

api_data_set — REST GET endpoints

Defines REST GET endpoints at https://apide2.tsql.app/{tenant}/v1/{name}. Lives in {app}_proj.

Key columns

Column	Purpose
`name`	URL path segment
`sql`	SELECT statement (no ORDER BY)
`orderby`	ORDER BY applied outside pagination wrapper
`sql_post`	SQL for POST requests
`is_public`	1 = no auth, cross-origin
`max_results` / `default_results`	Pagination caps
`use_transaction_log`	1 = log all requests

URL parameters

Use @param_name in sql — auto-binds from query string.

Reserved: max, offset (pagination). Special: @apikey_owner, @main_db.

Response


{"data":[...], "total":42, "offset":0, "max":100}

Changes are immediate — no restart needed.

T‑SQL

INSERT INTO api_data_set (name, sql, orderby, is_public, use_transaction_log)
VALUES (
    'customer_search',
    N'SELECT TOP 20 id AS customer_id, name AS customer_name, status
    FROM customers WHERE name LIKE CONCAT(''%'', @q, ''%'')',
    'customer_name ASC',
    1, 1
)

api_data_setrestapiendpointsqlorderbypaginationjson

REST API

api_data_set — critical rules and pitfalls

Rule 1: NEVER put ORDER BY in the sql column

Causes HTTP 400. Use the orderby column only.

Rule 2: orderby must use SELECT column aliases

Table aliases are out of scope outside the derived table.


WRONG:   orderby = 'wl.created_date DESC'
CORRECT: orderby = 'created_date DESC'

Error: The multi-part identifier X could not be bound

Rule 3: Avoid JOIN fan-out when aggregating

Joining multiple one-to-many tables causes SUM() to multiply. Pre-aggregate each dimension in a separate subquery, then LEFT JOIN.

Rule 4: Use OUTER APPLY TOP 1 for single-row lookups

When a join can return multiple rows but you only want one, use OUTER APPLY (SELECT TOP 1 ... ORDER BY id).

Rule 5: CORS for public endpoints

is_public = 1 allows cross-origin. Do NOT use credentials: 'include' in fetch — forces strict CORS preflight.

Testing workflow

1. Simulate with MCP execute_query (substitute literal values for @params)

2. Verify row count and column names

3. Insert the row — live immediately

4. Test via browser

T‑SQL

-- WRONG
INSERT INTO api_data_set (name, sql, orderby) VALUES
('bad', N'SELECT id, amount FROM orders ORDER BY amount DESC', 'o.amount DESC')

-- CORRECT
INSERT INTO api_data_set (name, sql, orderby) VALUES
('good', N'SELECT id, amount AS order_amount FROM orders WHERE status = @status', 'order_amount DESC')

api_data_setpitfallsorderbycorsjoinfan-outhttp-400could-not-be-bound

Chapter 21

URL Anatomy & Metadata Queries

URL & Navigation

URL anatomy — reading and building TSQL.APP URLs

Every URL is a complete deep link representing exact application state.

Pattern


https://{domain}/{card}[/{parent_id}/{child_card}]?[ord={field_id}[d]][&red={filter}][&id={record_id}]

Segments

Part	Meaning
`{card}`	Card name — the primary screen
`{parent_id}`	Parent record ID (child context only)
`{child_card}`	Child card name (child context only)

Query parameters

Parameter	Meaning
`ord={field_id}d`	Sort by field ID, `d` = descending
`ord=id1,id2`	Multi-column sort
`red={filter_name}`	Active reducer filter
`id={record_id}`	Currently selected record

Examples


/orders                    list view
/orders/42/order_lines     child card, filtered by order 42
/orders/0                  new record form
/orders?ord=18377d         sorted descending by field 18377
/orders?red=Open           active filter

SELECT dbo.main_db() returns the business database name.

T‑SQL

-- Look up the card
DECLARE @card_name nvarchar(128) = 'orders'
DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = @card_name)

-- Look up the sort field from an ord parameter
DECLARE @sort_field_name nvarchar(128)
SELECT @sort_field_name = name FROM api_card_fields WHERE id = 18377

-- Look up the active filter SQL
DECLARE @filter_sql nvarchar(max)
SELECT @filter_sql = sql
FROM api_card_actions
WHERE card_id = @card_id AND display_name = 'Open' AND action = 'reducer'

-- Build a child navigation URL
DECLARE @child_card_name nvarchar(128)
SELECT @child_card_name = c.name
FROM api_card_children acc
JOIN api_card c ON acc.child = c.id
WHERE acc.parent = @card_id AND acc.keycode = 'Enter'

DECLARE @child_url nvarchar(256) = CONCAT(N'/', @card_name, N'/', @id, N'/', @child_card_name)
EXEC sp_api_goto @child_url

urlnavigationordredcard_nameparent_idchild_cardfiltersort

URL & Navigation

Standard metadata queries for inspecting a card

T‑SQL

-- Full card inspection in one batch
DECLARE @card_name nvarchar(128) = 'orders'
DECLARE @card_id INT = (SELECT id FROM api_card WHERE name = @card_name)

-- Card definition
SELECT id, name, tablename, basetable, reducer, id_column, role, deny_role
FROM api_card WHERE id = @card_id

-- List columns
SELECT name, list_order, detail_order, seek_order
FROM api_card_fields WHERE card_id = @card_id AND list_order > 0
ORDER BY list_order

-- All actions with keyboard hierarchy
SELECT
    CASE WHEN g.keycode IS NOT NULL
         THEN CONCAT(g.keycode, ' > ', ISNULL(a.keycode, '-'))
         ELSE ISNULL(a.keycode, '-')
    END AS shortcut,
    a.display_name, a.action, a.type, a.disabled
FROM api_card_actions a
LEFT JOIN api_card_actions g ON a.group_id = g.id
WHERE a.card_id = @card_id
ORDER BY COALESCE(a.group_id, 0), a.action_order

-- Child navigations
SELECT c.name AS child, acc.keycode, acc.ref, acc.is_hidden
FROM api_card_children acc
JOIN api_card c ON acc.child = c.id
WHERE acc.parent = @card_id

metadataqueriesapi_cardapi_card_fieldsapi_card_actionsapi_card_childreninspect

Chapter 22

Document & PDF Generation

Document Generation

The report model — registry, template and data contract

A document report is one row in api_carbone_report plus a Word .docx template uploaded to that row. Rendering is performed outside SQL Server by the dispatcher; the database holds only the definition.

api_carbone_report

Column	Meaning
`name`	Unique lookup key used in every procedure call
`template_name`	Descriptive label only — it does not locate the file
`data_sql`	Query producing the JSON payload (see contract below)
`source_card_id`	Build the payload from card metadata instead, when `data_sql` is NULL
`report_type`	`generic` (default) or `invoice`
`action_sql`	Query returning the delivery instruction
`is_active`	Set to 0 to disable without deleting

The template

Upload the .docx to the report record itself — it is an ordinary file attachment, stored like any other upload. The newest non-deleted .docx on the record wins, so re-uploading supersedes the previous template rather than overwriting it. Placeholders use Carbone syntax: {d.customer.name} for a field, {d.lines[i].amount} for a row in an array.

data_sql contract

Must return one row with one column named data, containing a JSON string. @context_id is available as a parameter.

T‑SQL

SELECT data = (
    SELECT
        invoice = JSON_QUERY((
            SELECT [number], [date], [total]
            FROM   Invoice AS this
            WHERE  this.id = @context_id
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )),
        lines = JSON_QUERY((
            SELECT [description], [amount]
            FROM   InvoiceLine AS this
            WHERE  this.invoice_id = @context_id
            FOR JSON PATH
        ))
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)

Wrap nested sets in JSON_QUERY(...), or SQL Server escapes them and Carbone receives a string where it expects an object. Use WITHOUT_ARRAY_WRAPPER for single objects only — an array needs the wrapper or the template cannot loop it.

action_sql contract

Must return one row describing delivery: action (email or download), value (the recipient), subject, from_email. It may also reference @context_id, so the recipient can be looked up per record.

Inspecting reports

v_api_carbone_reports lists every report with the template actually resolved: has_template, template_file, template_path, and data_source (data_sql, card or none). A report with has_template = 0 or data_source = 'none' cannot render.

api_carbone_reportdocxpdftemplatedata_sqlv_api_carbone_reports

Document Generation

sp_api_carbone_report_generate — render and route

Renders a named report for one record and routes the result.

Parameter	Type	Meaning
`@report_name`	`nvarchar(200)`	Key in `api_carbone_report`
`@context_id`	`int`	The record to render
`@fixed_email`	`nvarchar(512)`	Override the recipient, ignoring `action_sql`
`@fixed_printer_id`	`int`	Send to a printer
`@download_only`	`bit`	Return the bytes instead of delivering
`@dataout`	`nvarchar(MAX) OUTPUT`	The dispatcher result, when `@download_only = 1`

Download from a card action

T‑SQL

DECLARE @out NVARCHAR(MAX);
DECLARE @b64 NVARCHAR(MAX);

EXEC dbo.sp_api_carbone_report_generate
    @report_name   = N'invoice',
    @context_id    = @id,
    @download_only = 1,
    @dataout       = @out OUTPUT;

SELECT @b64 = value FROM OPENJSON(@out) WHERE [key] = 'base64data';

EXEC sp_api_modal_download @filename = N'invoice.pdf',
                           @mimetype = N'application/pdf',
                           @base64   = @b64;

Read the result with OPENJSON, never JSON_VALUE. JSON_VALUE returns nvarchar(4000) and silently truncates the base64 payload; the symptom is a download or attachment that arrives empty, with nothing in the T-SQL looking wrong.

Delivery

Without @download_only, the result is routed by action_sql — emailed as a PDF attachment, or handed to a printer. @fixed_email overrides the recipient for a one-off send.

Running it in the background

Rendering waits on the dispatcher, so a batch should not run inside a user's request. sp_api_carbone_report_task @report_name, @context_id, @fixed_email queues the same call as an api_sql_tasks row and returns immediately; see also sp_api_add_sql_task.

sp_api_carbone_report_generatesp_api_modal_downloadpdfdownloademailOPENJSON

Document Generation

sp_api_carbone_scaffold — derive a report from a card

Generates a complete report definition from a card you have already built, including a first-draft .docx template, and attaches it to the new record.

T‑SQL

DECLARE @new_id INT;
EXEC dbo.sp_api_carbone_scaffold @card_name = N'invoice',
                                 @new_id    = @new_id OUTPUT;

The card is already a data contract: api_card_fields (those with a list_order) become the parent object's properties, and each api_card_children entry becomes an array keyed by its button name, filtered on its foreign key. The generated data_sql is stored on the report and is yours to edit — the framework does not regenerate over it.

What the generator handles for you

The source table is aliased AS this, so a field written as this.id keeps its correlation even inside a nested sub-query.
Child keys are made unique and restricted to letters, digits and underscore, because the key is also the Carbone tag the template loops on.
A field whose SQL depends on framework context unavailable outside a rendered card is emitted as NULL with a comment, rather than failing at render time.
The report name is <card>, then <card>-<yyyymmdd>, then a numeric suffix, so scaffolding the same card repeatedly is safe.

Check before you render

sp_api_carbone_data_preview @report_name, @context_id runs data_sql and action_sql without producing a document. It returns the resolved template, the full JSON that would be sent, and the delivery row — the fastest way to confirm the payload keys match the placeholders in the template.

Writing one by hand

sp_api_carbone_report_save @name, @template, @query, @report_type, @email_to, @email_subject, @email_from, @description takes a plain SELECT and wraps it into the data_sql and action_sql forms, so no JSON syntax is needed for a simple report.

sp_api_carbone_scaffoldsp_api_carbone_data_previewsp_api_carbone_report_saveapi_card_fieldsapi_card_childrenscaffold

TSQL.APPProgrammer's Manual

Concepts & Architecture

Two-database architecture

The golden rule

Action Types

stored_procedure — the workhorse

pathname — client-side navigation

reducer — list filters

System-level global actions

The params Column

1. Pathname navigation

2. Button CSS class override

3. Blacklisted targets

4. Combined

Navigation

Path conventions

Signature

Modal Dialogs

The three phases

Container

Display

Inputs

Buttons

Session

Statusbar & sys_run_always

sp_api_statusbar signature

Priority chain pattern

Multi-select

The cursor loop pattern

Queuing Work from Actions

Signature

When to use

Access Control

Special role values

pre_declare & Curly Braces

Format

How it works

When to use

Syntax forms

Constraints

Cookbook

Rules

Reserved keycodes

Modal Select Pickers

Signature

Requirements

Multi-step wizard pattern

Signature

Key assignment

File Handling

api_files columns

System actions

Step 1: Read

Step 2: Send

CSV from temp table

CSV & Excel Export

sp_api_modal_csv

sp_sys_excel_from_list

Session State

Common session keys

Common Pitfalls & Debugging

1. Single-letter keycodes captured by list search

2. @main_db only resolves inside TSQL.APP context

3. RENDER before READ loses button state

4. Temp table name collisions in multi-step modals

5. sp_api_modal_clear does not stop execution

6. SCOPE_IDENTITY() out of scope after sp_api_card_create_cache

7. action_order = 0 hides the button

8. Cache columns must never be set manually

9. sp_api_modal_print needed before slow operations

10. Curly braces only work in unparsed_sql

sp_api_toast — quick variable inspection

sp_api_modal_text with code class

sys_action_help (Alt+H)

api_errors card

error column on api_actions

TRY/CATCH pattern

Field Attributes

The sql column is a dynamic T-SQL expression

defval — fallback expression when no attribute row exists

TSQL.APP
Programmer's Manual