Enterprise-Grade Platform Built for Scale
V.E.T.S. is built on a sophisticated, production-proven architecture designed for high availability and data integrity.
With 15+ years of continuous operation, this technical foundation supports multi-tenant livestock operations, veterinary practices, and collaboration systems at scale.
Architecture Overview
Data Layer
Microsoft SQL Server with 1,244 tables, 2,850 views, and 2,749 stored procedures. The schema handles every aspect of veterinary and livestock operations.
Logic Layer
Specialized stored procedure API with 278 functions serving as the single source of truth for business logic. All data access flows through secured views, never direct table access.
Application Layer
VB.NET Web Forms with an AJAX-first architecture. HTML-in-SQL pattern generates permission-aware UI directly from the database.
Database Scale: 1,244 tables | 2,850 views | 2,749 stored procedures | 278 functions
Database Design & Naming Conventions
Every database object follows a structured naming convention that encodes purpose, ownership, and security level directly in the name.
Tables
atbl_ (847) - Application business data
stbl_ (392) - System infrastructure
ztbl_ - Temp/staging tables
Views
atbv_/stbv_ - Secured single-domain views
atbx_/stbx_ - Cross-domain security views
aviw_/sviw_ - Multi-table computed views
arpt_ - Reporting views
Procedures & Functions
astp_/sstp_ - Stored procedures
afnc_/sfnc_ - Scalar functions
sftf_ - Table-valued functions
Module Organization
Tables are organized into logical modules: VETS (82 tables) for core veterinary data, AI (64) for AI integration, TeamDoc (54) for collaboration, System (41) for framework, Items (36) for products/services.
Security & Permission Architecture
Security is built into the data access layer with a defense-in-depth model featuring 6 distinct security layers.
The 6 Security Layers
- Table-Level Permissions: SUID grant strings (Select/Update/Insert/Delete) via
sviw_System_MyPermissions
- TeamDoc Permissions: Reader/Editor/Manager access levels per document
- TreeList Group Permissions: Controls access to tree structure nodes
- Animal/Herd Ownership: Entity-level access for animals and herds
- Row-Level Permissions: Dynamic filtering via configurable criteria fields
- UI-Level Security (HTML-in-SQL): Permission-aware HTML generation - unauthorized elements simply don't exist
Core Principle: The application NEVER queries tables directly. All access flows through secured views (atbv_*, stbv_*) that automatically filter based on user permissions.
Integration Patterns
A flexible framework connecting external systems through standardized integration patterns.
Key Integrations
- Financial: QuickBooks Online with bi-directional sync for invoices, payments, and accounting
- Authentication: OneAll integration for social media login (Google, Facebook, etc.)
- Mapping: Google Maps for location tracking and geofencing
- Media: File storage, image processing, and media management
- AI: OpenAI GPT-4 integration for intelligent analysis and documentation
HTML-in-SQL Pattern
A unique architectural pattern where UI generation occurs at the database level, providing permission-aware rendering and consistent cross-platform experiences.
How It Works
SQL functions and stored procedures construct complete HTML strings with inline styles. Security decisions are made server-side before any HTML reaches the browser.
Scale
24+ afnc_HTML_* scalar functions
229+ *Mobile* procedures
100+ *Menu* procedures
Benefits
No client-side vulnerabilities. Same logic for web, mobile, and API. Permission-based elements are conditionally generated—unauthorized content never exists.
Stored Procedure API
The database layer functions as a complete API, with stored procedures serving as the single source of truth for all business logic.
Key Procedure Categories
- astp_VETS_* - Core veterinary operations (animals, herds, patient history)
- sstp_TeamDoc_* - Collaboration system (documents, inputs, permissions)
- astp_Items_* - Products, services, and inventory management
- sstp_System_* - Framework operations (users, settings, security)
- astp_AI_* - AI integration endpoints
Future Direction: Many procedures will become REST APIs, with naming conventions aligned to procedure names for dynamic discovery.
Performance Optimization
Built for high-volume operations with strategic performance patterns.
NOLOCK Pattern
All SELECT queries use WITH (NOLOCK) for read operations, preventing blocking while maintaining consistency for this read-heavy workload.
Indexed Views
Security views and frequently-accessed data are indexed for fast access. The aviw_ and sviw_ prefixes indicate indexed multi-table views.
Audit & Triggers
Comprehensive audit trails via triggers automatically track Created, CreatedBy, Updated, UpdatedBy on all tables.
Proven at Scale
V.E.T.S. isn't a prototype. It's a mature, enterprise-grade platform powering real businesses.