TSQL.APP
Programmer's Manual

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

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

-- 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)

-- 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"}')

-- 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"}')

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

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

-- 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'

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

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

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

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

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

-- 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'

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

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

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

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

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'

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

-- 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"}')

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

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

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

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

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]'

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

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

-- 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'

-- 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)

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

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

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)

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

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

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')

-- 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
)

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

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
)

-- 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')

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

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