TSQL.APP Platform Overview

Purpose and Capabilities

TSQL.APP is an innovative low-code development platform that enables database-driven web applications using T-SQL as the primary programming language. It allows developers to build complex business applications without extensive knowledge of frontend frameworks or traditional programming languages.

diagram of tsql.app platform

Core Capabilities

  • Development of enterprise-grade web applications using T-SQL
  • Automatic UI generation based on database schemas
  • Built-in authentication and authorization
  • Real-time data updates and notifications
  • Document management and file handling
  • Extensive reporting functionality
  • Excel integration and exports
  • Email functionality
  • Multi-platform deployment

Architecture

1. High-Level Overview

TSQL.APP consists of three main components:

  1. Static Content Server - Delivers the React-based client application
  2. API Server - Processes all business logic and data requests
  3. Database Server - SQL Server for data storage and business logic

2. Component Details

2.1 Frontend (Client)

  • React-based Single Page Application (SPA)
  • Modular component structure
  • State management for real-time updates
  • Responsive design
  • Progressive Web App (PWA) capabilities

2.2 API Layer

  • RESTful API endpoints
  • Versioning (/v1/, /v2/, etc.)
  • Token-based authentication
  • CORS support
  • Real-time WebSocket connections
  • File handling capabilities

2.3 Database Layer

  • SQL Server (Windows or Linux)
  • Stored Procedures for business logic
  • Table-based configuration
  • Built-in caching
  • Transaction management
  • Data versioning

3. System Tables

Important meta-tables:

api_card           - Application views/forms definition
api_card_fields    - Field configurations
api_user           - User management
api_settings       - System configuration
api_files          - Document management

4. Security Architecture

4.1 Authentication

  • Token-based authentication
  • Two-factor authentication support
  • IP-based restrictions possible
  • Session management

4.2 Authorization

  • Role-based access control (RBAC)
  • Object-level security
  • Row-level security possible via stored procedures
  • API key management

5. Data Flow

[Browser] → [Static Files] → [React App]
                             ↓
                      [API Endpoints]
                             ↓
                    [Stored Procedures]
                             ↓
                     [SQL Server DB]

6. Deployment Options

6.1 Single Server

[All Components]
- API
- Database
- Static Files

6.2 Distributed

[Static File Server]
      ↓
[API Server(s)]
      ↓
[Database Server(s)]

6.3 Cloud/Hybrid

[CDN] → [Static Files]
         [API Containers]
         [Managed Database]

7. Key Features Implementation

7.1 Card System

  • Database table/view representation
  • Automatic CRUD operations
  • Custom actions via stored procedures
  • Real-time updates
  • Conditional formatting
  • Dynamic filtering

7.2 Action Framework

-- Example of a card action
EXEC sp_api_modal_text @text='Welcome'
EXEC sp_api_modal_input @name='@input', @value=@input OUT
EXEC sp_api_modal_button @name='@button', @value='OK', @valueout=@button OUT

7.3 File Handling

  • Integrated document management
  • Automatic versioning
  • Preview capabilities
  • Secure file storage
  • Temporary file handling

8. Development Workflow

1. Database Schema Definition
2. Card Configuration
3. Custom Action Development
4. Security Configuration
5. Testing & Deployment

9. Performance Optimizations

9.1 Caching

  • Database query caching
  • API response caching
  • Static file caching
  • Memory-optimized tables support

9.2 Scaling

  • Horizontal scaling possible
  • Load balancing support
  • Database replication options
  • Distributed caching

10. Monitoring & Maintenance

10.1 Logging

  • System logs
  • User activity tracking
  • Performance metrics
  • Error logging

10.2 Backup & Recovery

  • Database backups
  • File system backups
  • Point-in-time recovery
  • Disaster recovery options

11. Integration Capabilities

  • REST API endpoints
  • WebSocket connections
  • File system integration
  • Email integration
  • External authentication providers
  • Custom protocol handlers

12. Future-Proof Design

  • Versioned API endpoints
  • Extensible architecture
  • Platform-agnostic deployment
  • Modern security standards
  • Scalable infrastructure

Conclusion

TSQL.APP offers a unique combination of low-code development and enterprise-grade functionality, combining the power of T-SQL with modern web technologies. The architecture is built with scalability, security, and maintainability as core principles, making it suitable for both small and large enterprises.

The platform-independent nature and flexible deployment options make it possible to deploy TSQL.APP in various environments, from on-premise to cloud-based solutions. The focus on T-SQL as the primary programming language makes the platform particularly accessible to database developers and administrators while simultaneously offering powerful capabilities for building modern web applications.