Advanced Architecture

Deep dive into V.E.T.S. architectural patterns and advanced system components

HTML-in-SQL Pattern

V.E.T.S. pioneers a unique architectural pattern where HTML generation occurs at the database level rather than in application code. This provides security, consistency, and flexibility benefits.

How It Works

Instead of: Application Code ? Generate HTML ? Send to Browser
V.E.T.S. uses: Application Code ? Call SQL Function ? SQL Generates Permission-Aware HTML ? Send to Browser

Key Benefits

  • Security: Unauthorized UI elements never generated (not just hidden)
  • Consistency: Single source of truth for UI logic
  • Flexibility: Mobile/desktop responsiveness at SQL level
  • Performance: Minimal client-side processing

Example: Animal Cards

The afnc_HTML_AuthenticatedNow1Animal0 function generates complete animal card HTML with permission checks, color coding based on herd membership, dynamic sizing based on device, and context-specific onclick handlers.

Total SQL Functions: 274 (many generate HTML components)

DynamicForms Framework

DynamicForms is a sophisticated metadata-driven form generation framework that enables form creation through database configuration rather than code.

Scale

  • 54 forms configured
  • 401 fields with rich metadata
  • 23 layouts (Grid, List, Gantt, etc.)
  • Active since 2008, last updated Nov 2023

Core Tables

  • stbl_DynamicForms_Forms - Form definitions
  • stbl_DynamicForms_FormsFields - Field configurations
  • stbl_DynamicForms_FormLayouts - Display layouts
  • stbl_DynamicForms_FormsFieldsStyles - Styling rules
  • stbl_DynamicForms_FormsFieldsFilters - Filter configs

Active Use Cases

  • IoVETS: Sensor management and data visualization
  • Reproduction: Embryo transfer, ultrasound tracking
  • Permissions: User/group access drilldowns
  • Animals: Custody tracking, contact relationships

Future Vision: AI-Configured Forms

DynamicForms is positioned to become an AI-configured system where users describe needs in natural language. SQL access will be restricted to developers only, with end users interacting through AI interface.

Core Procedures Deep Dive

The V.E.T.S. database contains 2,735 stored procedures, with an estimated 200+ core procedures that are frequently used and critical to operations.

Procedure Categories

  • Dashboard & Menu (~10): astp_SocialMedia_Admin_AuthenticatedNowMobile
  • Animal Management (~25): sstp_Teamdoc_WebsiteAddEditAnimal
  • Item Management (~15): astp_Items_TreeList_EditTreeOrItemTeamDoc2
  • Professional Services (~20): astp_VETS_PatientHistory_A_ProfessionalServicesParent
  • TeamDoc System (~30): sstp_TeamDoc_Inputs_SaveInput
  • DynamicForms (~15): sstp_DynamicForms_SelectMeAsSubCatJScriptArray
  • AI Integration (~10): sstp_AI_GetItemContext, sstp_AI_CheckItemPermission
  • Database Management (42): sstp_Database_* pattern
  • System Operations (41): sstp_System_* pattern

Visual Workflows

See Procedure Calling Chain Diagrams for 6 detailed workflow diagrams showing how procedures interact.

Security Considerations

Pre-Release Security Requirements

Several areas require security hardening before public deployment:

  • DynamicForms: SQL injection vulnerabilities in query execution
  • Parameter validation: GUID validation required for all URL parameters
  • Dynamic SQL: Requires parameterization or input sanitization

Context: V.E.T.S. has operated securely in controlled environments for 15+ years. SQL injection mitigation is a pre-requisite for public/external deployment.

Current Security Strengths

  • 6-layer permission system prevents unauthorized data access
  • View-based security (never direct table access)
  • Complete audit trails (Created/Updated tracking)
  • TeamDoc permission hierarchies
  • Row-level security for sensitive data
  • UI-level security (unauthorized elements never generated)
Standard UserName/Password Login:
 
Username:  
Password: