Deep Architectural Patterns
Where veterinary expertise meets database-driven intelligence—and AI becomes possible.
V.E.T.S. pioneered a radical approach: instead of building features in application code, the intelligence lives in SQL Server itself. Every form, every question, every answer format, every workflow sequence—stored as data, not code. This creates something remarkable: a platform where AI and humans operate through the same pathways.
The Intelligence Layer: 2,749 stored procedures | 278 functions | 2,850 secured views | 1,244 tables
Living Data
47,000+ animals tracked across 66 herds with complete lineage through Sire/Dam self-references. Multi-generation pedigrees with health history flowing through the bloodlines.
Configurable Depth
72 evaluation tables power PHDetails, Farrier, AnimalPerformance, Lessons, and SOAP with 7 answer formats: TextBox, RadioButtons, NumericValue, NumericList, CheckBoxes, List, and connection types.
Hierarchical Questions
Questions can have sub-questions via ParentRef—conditional follow-ups based on prior answers. A "Yes" to colic triggers detailed symptom questions. Species-specific questions appear only for relevant animals.
Enterprise Security
401 security groups | 23,329 row-level permission rules | 25,029 TeamDoc access assignments—all enforced at the database layer, never in application code.
HTML-in-SQL: Security by Architecture
Most platforms hide unauthorized content with CSS or JavaScript—meaning it still exists in the browser for anyone to inspect. V.E.T.S. takes a fundamentally different approach: unauthorized elements are never generated.
The Data Flow: User Action → AJAX Request → SQL Procedure → Permission Check → HTML Generation → Browser Injection
Permission-Aware Rendering
The largest UI procedure (astp_Items_TreeList_EditTreeOrItemTeamDoc2) contains 148,000 characters of SQL that generates complete edit forms—but only for users with Editor or Manager access. Others see nothing.
Device-Responsive Generation
The @isMobileDevice parameter (0-100+ values) triggers different HTML structures. A phone gets touch-friendly buttons; a desktop gets keyboard shortcuts—decided at the database level.
The Power of Database UI
Consider what this enables:
- Instant Updates: Change a stored procedure, all users see the new UI immediately—no deployment, no cache clearing
- A/B Testing: User-specific UI variations via simple CASE statements in SQL
- Audit Everything: Every UI element can be logged because generation happens server-side
- No Client Exploits: Can't hack what doesn't exist in the browser
Configurable Evaluation Systems
Four parallel systems share one architectural pattern—enabling configurable questionnaires without schema changes. Each uses identical table structures: Eval (sessions), EvalItems (questions), EvalOptions (answer choices), EvalValues (recorded answers), plus EvalFiles with specialized storage for Files, Images, and Videos.
The Eval Table Pattern
Add a question to atbl_[Module]_EvalItems—it appears instantly. Change RepositoryType from TextBox to RadioButtons—the UI transforms. Link to a parent question via ParentRef—conditional display activates. No code changes. No deployments.
PHDetails
207 configured evaluation items capturing detailed patient history. Professional service procedures trigger structured data collection beyond free-text notes—follicle sizes, lameness grades, temperature readings.
Farrier
Hoof care evaluations with 8 specialized tables. Track shoeing schedules, hoof conditions, corrective work across animals. File attachments support before/after photography.
Lessons
Training and riding lesson tracking with instructor assignments and available animal matching. Performance evaluations connect training to competition results.
AnimalPerformance
Competition and performance metrics. Bucking horse scores, barrel racing times, breeding success rates. Video analysis integration captures performance footage.
Answer Format Flexibility: 91 TextBox items | 35 RadioButton sets | 25 NumericValue fields | 23 NumericList dropdowns | 15 CheckBox groups | 13 connection lookups | 5 List selectors. Each configurable per species, discipline, and login.
Meta-Architecture: Everything is an Item
V.E.T.S. uses a revolutionary meta-architecture where a single unified table (atbl_Items_Items) contains 3,539 entries spanning physical products, service procedures, vaccinations, surgeries, lab tests, and certifications. Tree values ARE items. This radical simplification enables one codebase to handle everything identically—with profound implications for AI integration.
Unified Item Model
Whether vaccination (VA), surgical procedure (SX), prescription drug (Rx), DEA-controlled substance (DEA), or professional service (PS)—it is an Item with a TransactionType. Same permissions. Same TeamDoc. Same AI documentation. Same pricing engine.
Kit Relationships
Procedures link to follow-up actions via atbl_Items_KitRelationships. "AI: Cooled Semen" automatically suggests Uterine Flush, Pregnancy Check, Embryo Transfer. "Abscess lance and flush" triggers Hydrotherapy follow-up. Parent → Child with quantity, UOM, expiration tracking.
Self-Referential Hierarchy
TreeList uses ParentID for unlimited nesting. Animals use Sire/Dam for multi-generation pedigrees. TeamDoc Inputs use ParentRef for threaded discussions. The pattern repeats—enabling recursive structures everywhere.
Species/Sex Filtering
Items inherit applicability via SpeciesRef and SexRef. Mare-only procedures appear only for mares. Canine vaccines don't clutter equine workflows. The filter happens at the data level, not UI logic.
The One Exception: Patient History (1.4M+ records) is the ONLY place requiring hardcoded segmentation. The 88 specialized astp_VETS_PatientHistory_* procedures exist because veterinary records need domain-specific formatting—surgical notes display differently than vaccination logs. Even here, the exception proves the rule: hardcoding is the last resort.
Six-Layer Security Model
Security isn't a feature—it's the architecture. Every data access flows through secured views that automatically apply permission filters. Application code never touches tables directly. The prefix tells you everything: atbl_ = raw table (danger), atbv_ = secured view (safe).
The Six Layers
- Layer 1 - SUID Grants: Four-character permission strings (Select/Update/Insert/Delete) per table.
'SUID' = full access, 'S' = read-only, 'SI' = read and create.
- Layer 2 - TeamDoc Access: Reader/Editor/Manager levels across 25,029 active permission assignments. Readers see content; Editors modify; Managers control access.
- Layer 3 - Security Groups: 401 groups controlling access to features, tree nodes, and menu items. One person can belong to multiple groups.
- Layer 4 - Entity Ownership: Animal and herd-level access. Clients see only their animals; trainers see animals in their care.
- Layer 5 - Row-Level Filtering: 23,329 dynamic filter rules via configurable criteria fields. Different clinics see different subsets from the same tables.
- Layer 6 - HTML-in-SQL: UI elements requiring authorization are never generated. The edit button doesn't get hidden—it never exists in the HTML.
Why This Matters: A contractor can access the system to view their horses' records without ever seeing another client's data. A vet tech can enter treatments but not modify pricing. A clinic manager can run financial reports while staff cannot. All enforced at the database level—no application bugs can bypass it.
Contextual AI Integration
The database-centric architecture wasn't designed for AI—but it's perfectly suited for it. Because interfaces are data and logic is in stored procedures, AI can query, generate, and modify the system through the same pathways humans use. The key is context capture—AI knows exactly what it's documenting.
Context Gathering
astp_AI_GetItemContext retrieves comprehensive metadata—TransactionType, TreeListType, parent categories, associated items, existing TeamDoc content. AI doesn't guess; it queries the database for complete context before generating.
Transaction Type Awareness
8 expert-defined content sections with 162 transaction type mappings. Surgical procedures get "Pre-operative Considerations" and "Post-operative Care". Vaccines get "Administration Guidelines" and "Adverse Reaction Protocols". Context drives content.
Item vs Tree Value
AI distinguishes between specific procedures (Items) and organizational categories (Tree Values). "Equine Dental Float" (an item) gets detailed technique documentation. "Dentistry" (a tree node) gets classification guidance for organizing child procedures.
Safe Integration
AI generates SQL against secured sviw_* views—existing security fully applies. Report definitions, form configurations, and content all flow through validated pathways. The security model AI uses is the same security model humans use.
The Expert + AI Feedback Loop
AI generates first drafts using contextJSON embedded in prompts. Veterinary experts refine and approve via TeamDoc comments. Expert corrections feed back into prompts for future generation. This creates institutional knowledge that compounds—each correction makes the next generation smarter.
TeamDoc: Structured Unstructured Data
TeamDoc is where free-form expertise becomes queryable knowledge. 13 input types organize content that would otherwise live in scattered notes, emails, and memories. Every animal, item, herd, and contact has a TeamDoc—creating a unified collaboration layer across the entire platform.
The 13 Input Types
Subject (15,527) – Organizational folders | Comment (3,001) – Threaded discussions capturing expert knowledge | Chart (2,609) – IoT sensor visualizations | Task (2,106) – Action items with assignments, due dates, and linked procedures | FileFolder (1,901) – Organized document storage | PhotoAlbum (1,231) – Image galleries for visual documentation | WebPage (301) – Rendered HTML content | ContactList (103) – Linked directories | Poll (15) – Voting mechanisms for consensus
Tasks Link to Items
stbl_TeamDoc_Tasks.ItemRef connects action items to specific procedures. "Follow up on Colic Case" links directly to the Colic procedure with ClientRef and GroupRef for routing. Tasks become the workflow engine.
Hierarchical Threading
Comments use ParentRef for nested discussions. An expert's observation spawns replies, disagreements, and refinements—all threaded and searchable. Knowledge emerges from conversation.
AI Section Creation
AI generates structured sections via sstp_TeamDoc_Inputs_SaveInput. Comments become documentation. Charts become visualizations. The same APIs humans use become AI integration points.
Moving Actions to TeamDoc
Free-form responses in EvalValues can be promoted to TeamDoc Comments for structured capture. An observation typed in PHDetails becomes searchable, threaded, expert-validated knowledge.
Products, Assignment, and Workflow Chains
The meta-architecture extends to how products relate to procedures, how tasks chain into workflows, and how inventory connects to animals, herds, and companies. Every relationship is data—queryable, auditable, and AI-accessible.
Kit Relationships
Procedures bundle consumables via ParentRef → ChildRef with Quantity and UOM. "Abscess lance and flush" includes Hydrotherapy follow-up. "AI: Cooled Semen" chains to Pregnancy Check, Embryo Flush, Mare Vaccination—automated workflow suggestions from data.
Inventory Assignment
Products link to animals, herds, and companies through transaction records. DEA-controlled substances track chain of custody. Compounded drugs maintain ingredient relationships. Stock levels adjust through procedure execution.
Tasked Procedures
KitRelRef in TeamDoc Tasks links follow-up actions to parent procedures. Complete a breeding procedure—pregnancy check task auto-generates with calculated due date. isretaskable enables recurring task patterns.
Expiration Tracking
ExpirationDateAddNumber and ExpirationDatePart calculate expiration from administration date. Vaccine boosters, medication refills, certificate renewals—all date-driven from kit configuration.
The AI Opportunity: Suggest consumables based on procedure context. Generate follow-up tasks from kit relationships. Predict inventory needs from scheduled procedures. The data relationships are already defined—AI just needs to traverse them.
Architecture Enabling Intelligence
This isn't just a veterinary system—it's a knowledge platform where every configuration, every relationship, every expert refinement becomes accessible to both humans and AI. The 15+ year architecture investment now pays dividends: AI integration happens through existing pathways, not custom builds.