TSQL.APP Knowledge: api_settings Configuration Table

Overview

The api_settings table is a central configuration repository that controls the behavior, appearance, and functionality of TSQL.APP applications. It follows a key-value pattern that allows for flexible, environment-specific configuration without requiring code changes or redeployment.

Table Structure

Column Data Type Description
id INT Primary key, auto-incrementing identifier
key NVARCHAR(128) Setting name/identifier, used for lookups
value NVARCHAR(MAX) The configured value for the setting
enabled BIT Whether the setting is active (1) or disabled (0)
for_client BIT Whether the setting is exposed to client-side code (1) or used only server-side (0)

Core Functionality

The api_settings table powers several key aspects of the TSQL.APP framework:

  1. Runtime Configuration: Settings are loaded at application startup and referenced throughout execution
  2. Environment Differentiation: Different environments (dev, test, prod) can maintain separate configurations
  3. Feature Toggles: The enabled flag allows for easy feature switching without code changes
  4. Security Boundary: The for_client flag prevents sensitive configuration from being exposed to browsers

Accessing Settings

Within TSQL.APP, settings can be accessed using the sp_api_setting_get stored procedure:

-- Example of retrieving a setting
DECLARE @DateFormat NVARCHAR(MAX);
EXEC sp_api_setting_get 
    @key = N'dateformat', 
    @value = @DateFormat OUTPUT,
    @default = N'yyyy-MM-dd',  -- Optional default if not found
    @default_enabled = 1;      -- Use default if setting is disabled

Key Setting Categories

UI Configuration

Key Example Value Purpose
containerClass container-full Controls the main application container width
customStyle .class{property:value} Custom CSS applied across the application
useHtmlNativeDatepicker 0 Controls whether to use native or custom date pickers

Localization and Formatting

Key Example Value Purpose
dateformat dd-MM-yyyy Format pattern for displaying dates
datetimeformat dd-MM-yyyy HH:mm Format pattern for date and time
timeformat HH:mm Format pattern for times
languagesUsed nl,en,de,fr Comma-separated list of supported language codes

System Configuration

Key Example Value Purpose
project_base tsql.app Base domain for the application
environment test Environment identifier (dev, test, prod)
sp_api_card_actions_version 2 Version of the card actions framework to use
disable_isam 1 Controls ISAM functionality
two_factor_auth_days 30 How long 2FA authorization remains valid

Storage and Integration

Key Example Value Purpose
fileStore /data/filestore Server-side path for file storage
googlePlacesApiKey [api-key] API key for Google Places integration
context_api_root_url https://api.example.com Backend API endpoint
origin https://app.example.com Allowed origin for CORS

Communication

Key Example Value Purpose
error_email_address errors@example.com Where error notifications are sent
email_domain example.com Domain used for emails
report_modus test Mode for report generation

Special Setting Types

CSS Customization

The customStyle setting allows for sophisticated UI customization without modifying core application code. It can contain complete CSS rules including media queries:

.shadow-wrapper{background-color: rgb(190,220,220) !important;}
.custom-element{height:90px !important}

@media print {
  img {
    max-width: 100% !important;
    height: auto !important;
    display: block;
    page-break-inside: avoid;
  }
}

Feature Toggles

Many settings function as feature toggles when combined with the enabled flag:

-- Example of a feature toggle setting
-- id  key              value  enabled  for_client
-- 12  advanced_search  1      0        1

This pattern allows features to be included in deployments but activated only when ready.

Best Practices

  1. Security Considerations:

    • Set for_client=0 for any sensitive configuration like API keys
    • Review client-exposed settings regularly to avoid security leaks

  2. Maintenance:

    • Document all custom settings with clear descriptions
    • Group related settings with naming conventions
    • Clean up unused settings to prevent confusion

  3. Performance:

    • Cache frequently accessed settings in application code
    • Minimize the number of settings needed at startup

  4. Deployment:

    • Include settings changes in deployment scripts
    • Verify environment-specific settings after promotion

Implementation in TSQL.APP Architecture

The api_settings table is used throughout the TSQL.APP framework for:

  1. Application Startup: Settings are loaded into memory
  2. UI Rendering: Client-side settings are sent to the browser
  3. Business Logic: Server-side settings influence processing
  4. Integration: External system connections are configured

Common Implementation Patterns

Environment Detection

DECLARE @Environment NVARCHAR(50);
EXEC sp_api_setting_get @key=N'environment', @value=@Environment OUTPUT;

IF @Environment = N'test'
BEGIN
    -- Execute test environment-specific logic
END

Dynamic UI Configuration

DECLARE @CustomClass NVARCHAR(128);
EXEC sp_api_setting_get @key=N'containerClass', @value=@CustomClass OUTPUT;

-- Use in dynamic UI generation
DECLARE @HtmlOutput NVARCHAR(MAX);
SET @HtmlOutput = CONCAT(N'<div class="', @CustomClass, N'">...</div>');

Feature Toggle Implementation

DECLARE @FeatureEnabled BIT = 0;
DECLARE @FeatureValue NVARCHAR(MAX);

EXEC sp_api_setting_get 
    @key=N'advanced_search', 
    @value=@FeatureValue OUTPUT,
    @default_enabled=1;

IF @FeatureValue = N'1'
BEGIN
    -- Enable advanced search functionality
    SET @FeatureEnabled = 1;
END

Extending the Settings Framework

Applications built on TSQL.APP often extend the settings framework with custom settings. These should follow the established patterns:

  1. Use descriptive keys that indicate ownership/purpose
  2. Set appropriate for_client flags based on security needs
  3. Document settings and their valid values
  4. Consider grouping related settings with prefixes (e.g., email_*)

Conclusion

The api_settings table represents a critical architectural component of the TSQL.APP framework. Understanding how to effectively use and extend this configuration system is essential for building robust, maintainable applications that can be deployed across multiple environments with minimal code changes.