V.E.T.S.

Build On V.E.T.S.

Technical documentation for developers building on the V.E.T.S. platform - integrations, extensions, stored procedures, and AI features.

V.E.T.S. runs on the Omega PIMS AppFrame, a proprietary ASP.NET Web Forms framework built in 2010. It's a proven, enterprise-grade platform with 15+ years in production.

Learning Architecture

Every expert correction teaches the system. Learnings flow through a pipeline: extraction, review, promotion to the knowledge base. Your IDE conversations can feed back into V.E.T.S. via MCP tools.

MCP Integration

Model Context Protocol tools let AI assistants query PIMS directly. ask_minion routes questions to specialized experts. report_conversation captures learnings from IDE sessions.

Database Naming Conventions

Every database object follows a structured prefix pattern: [scope][type][_ModuleName_][Entity]

Where: scope = a (application) or s (system), type = tbl, tbv, viw, stp, fnc

Tables

Prefix	Meaning	Example
`atbl_`	Application table (domain business data)	`atbl_VETS_Animals`
`stbl_`	System table (cross-app infrastructure)	`stbl_TeamDoc_Inputs`

Views - Security Layer

Prefix	Meaning	Example
`atbv_`	App view with single-domain RLS	`atbv_VETS_Animals`
`stbv_`	System view with single-domain RLS	`stbv_TeamDoc_Inputs`
`atbx_`	App view with cross-domain security	`atbx_VETS_AnimalsCrossDomain`
`stbx_`	System view with cross-domain security	`stbx_System_Users`
`aviw_`	App multi-table/computed view	`aviw_VETS_AnimalsWithHerd`
`sviw_`	System multi-table/computed view	`sviw_TeamDoc_MembersWithName`
`arpt_`	Reporting view (read-only, aggregated)	`arpt_VETS_MonthlySummary`

Design Goal: Base views (atbv_/stbv_) should include WITH (NOLOCK) on all internal table references, so security AND read-consistency are handled at the view level.

Stored Procedures & Functions

Prefix	Meaning	Example
`astp_`	Application stored procedure	`astp_VETS_PatientHistory_Save`
`sstp_`	System stored procedure	`sstp_TeamDoc_Inputs_SaveInput`
`afnc_`	Application scalar function	`afnc_HTML_AuthenticatedNow1Animal0`
`sfnc_`	System scalar function	`sfnc_System_GetDomain`

Critical Rule: Never query tables (atbl_/stbl_) directly. Always use views (atbv_/stbv_) to maintain security and row-level filtering.

Core Architecture Patterns

V.E.T.S. uses three distinctive patterns that define how the platform operates.

HTML-in-SQL

UI is generated inside stored procedures and functions. HTML is constructed based on user permissions, context, and device size. Unauthorized elements simply aren't generated.

Key objects: afnc_HTML_* (24+ functions), astp_*Mobile* (229+ procedures)

View-Based Security

Six security layers enforce access control. All queries go through secured views that automatically apply Row-Level Security (RLS) based on domain, TeamDoc permissions, and group membership.

Key views: sviw_System_MyPermissions, sviw_TeamDoc_MembersWithName

AJAX-First Architecture

Zero page refreshes. All user interactions call AJAX handlers that execute stored procedures and return complete HTML fragments for DOM injection.

Key endpoint: MobileAJAX.aspx with 100+ command handlers

The Pattern: User Action → JavaScript AJAX Call → VB.NET Handler → SQL Stored Procedure → HTML String → DOM Injection

Essential Tables & Views

Core Animal Data

atbl_VETS_Animals - The center of the database. Every animal record connects here.

Key columns: PrimKey, Domain, Name, Birthday, SpeciesRef, BreedRef, HerdRef, Sire, Dam, ChipNumber

atbl_VETS_Herds - Group/herd definitions that animals belong to.

atbl_VETS_PatientHistory - Medical records linked to animals via AnimalRef.

Items & Services

atbl_Items_Items - Unified table for BOTH procedures/services AND physical products. Distinguished by TransactionType (ProfessionalServices, Rx, DEA, VA, etc.).

atbl_VETS_TreeList - Hierarchical category structure. Self-referential via ParentID. Tree values ARE items and can have descriptions and TeamDocs.

TeamDoc Collaboration

stbl_TeamDoc_Documents - Root documents. PrimKey matches the entity's PrimKey (1:1 relationship with animals, items, etc.).

stbl_TeamDoc_Inputs - Individual sections/content within a TeamDoc. Has 12 types: Subject, Comment, WebPage, Task, Chart, FileFolder, PhotoAlbum, ContactList, File, Image, Poll, DBReport.

stbl_TeamDoc_WebPages - HTML content for WebPage-type inputs.

Security & Permissions

stbl_Security_Groups - Security group definitions.

stbl_Security_GroupsMembers - User-to-group membership.

stbl_TeamDoc_TeamDocPermissions - TeamDoc access assignments (Reader/Editor/Manager).

Stored Procedure Development

Standard Procedure Pattern

CREATE PROCEDURE [astp_Module_SecureOperation]
    @UserLogin NVARCHAR(128),
    @PrimKey UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON
    
    -- 1. Verify access via secured view (NEVER query tables directly)
    IF NOT EXISTS (
        SELECT 1 FROM atbv_Module_Items
        WHERE PrimKey = @PrimKey
    )
    BEGIN
        RAISERROR('Access denied or item not found', 16, 1)
        RETURN
    END
    
    -- 2. Generate HTML with permission-aware elements
    DECLARE @HTML nvarchar(MAX) = ''
    
    -- Only generate edit button if user is Editor/Manager
    IF EXISTS (
        SELECT 1 FROM sviw_TeamDoc_MembersWithName
        WHERE TeamDocRef = @PrimKey AND [Login] = SUSER_SNAME()
        AND AccessLevel IN ('Editor', 'Manager')
    )
        SET @HTML = @HTML + '<button onclick="edit()">Edit</button>'
    
    SELECT @HTML
END

Critical Rules:

ALWAYS use secured views (atbv_/stbv_), never raw tables
Check permissions BEFORE generating any HTML
Use SUSER_SNAME() to get current user login

NOLOCK Strategy

Goal: Base views (atbv_/stbv_) should have WITH (NOLOCK) on all table references internally, so callers don't need to add it.

Reality: Not all views have been updated yet. When querying views that may not have NOLOCK baked in, add it explicitly.

Tables: When writing views or querying tables directly (rare, justified cases only), ALWAYS use WITH (NOLOCK).

HTML-in-SQL Tips: Use @isMobileDevice parameter (0=desktop, 1-5=mobile sizes) to adjust font sizes and widths. Use CASE statements for context-aware colors based on status, ownership, or group membership.

AJAX Handlers & API

MobileAJAX.aspx

Primary endpoint for all mobile and responsive web interactions. Contains 100+ command handlers routing to stored procedures.

Pattern: AJAXCall('MobileAJAX.aspx', 'CommandName', 'params', callback)

AIAssistAJAX.aspx

Dedicated endpoint for AI content generation. Integrates with Gemini 2.5 Flash for description generation, section creation, and classification guidelines.

REST API

URL pattern: /API/v1/{resource}/{id}

Rewrite rules route to API/v1/API.aspx with parameters extracted from URL segments.

SQLAccess Data Layer

The Appframe.Web.Data.SQLAccess class provides all database connectivity:

SQLAccess.GetData(sql) - Returns DataTable
SQLAccess.GetDataSet(sql) - Returns multiple result sets
SQLAccess.ExecuteSQL(sql) - Returns rows affected
SQLAccess.ExecuteSQLScalar(sql) - Returns single value (most common for HTML)
SQLAccess.Username - Current authenticated SQL user

External Integrations: QuickBooks (accounting), VIA (breed registry), OneAll (social authentication)

Security Model - 6 Layers

V.E.T.S. implements defense-in-depth with six distinct security layers.

Layer 1: Table-Level (SUID)

Permission grant strings: S=Select, U=Update, I=Insert, D=Delete

Key view: sviw_System_MyPermissions

Layer 2: TeamDoc Permissions

Reader / Editor / Manager access levels per document.

Key view: sviw_TeamDoc_MembersWithName

Layer 3: TreeList Groups

TreeListGroupRef controls access to tree nodes and their content.

Layer 4: Animal Ownership

Animals/herds assigned to specific owners or groups via stbl_AnimalOwnership.

Layer 5: Row-Level Criteria

Dynamic filtering based on configurable CField1/CField2 criteria columns.

Layer 6: UI-Level (HTML-in-SQL)

Permission checks occur BEFORE generating HTML. Unauthorized elements don't exist in the response.

Mandatory Security Pattern for TeamDoc Queries:

WHERE PrimKey IN (
    SELECT TeamDocRef 
    FROM sviw_TeamDoc_MembersWithName
    WHERE [Login] = SUSER_SNAME()
)

NOLOCK in Views - TODO

Goal: All atbv_ and stbv_ views should have WITH (NOLOCK) on their internal table references.

Status: Not all views have been updated. Requires audit and remediation.

Action Items:

Audit all base views for NOLOCK compliance
Update non-compliant views (document any justified exceptions)
Establish code review checkpoint for new views

AI Integration

V.E.T.S. integrates AI for knowledge bootstrapping - AI generates initial content, experts refine it through daily use.

Provider Architecture

The platform uses an ILLMProvider interface pattern allowing flexible provider selection:

System credentials - Default provider for all users (atbl_AI_SystemCredentials)
User credentials - Users can configure their own LLM provider/API key (atbl_AI_UserCredentials)

Swap providers (Gemini, Claude, GPT, xAI, local LLMs) without code changes.

Features

Generate Description - Initial content for items and tree values
Review Content - Multi-section generation with templates
AI Classification Guidelines - JSON-based classification rules

Key Database Objects

atbl_AI_SystemCredentials - System-wide API credentials (Provider, APIKey, Model)

atbl_AI_UserCredentials - User-specific API credentials

atbl_AI_UsageLog - Audit trail for all AI requests

atbl_AI_TeamDocSubjects - Section templates by TransactionType

astp_AI_GetItemContext - Gathers comprehensive context for prompts

astp_AI_CheckItemPermission - Validates user has edit access

Endpoint: AIAssistAJAX.aspx routes requests through the configured provider.

MCP Tools (Model Context Protocol)

External AI assistants (Cursor, Claude Desktop, etc.) can interact with V.E.T.S. directly through MCP tools. This creates a bidirectional learning loop between IDE development and the knowledge base.

ask_minion

Route questions to specialized V.E.T.S. Minions. Each Minion has domain expertise and RAG context. Use auto for intelligent routing or specify: Oz (AI), Florence (Medical), Penny (Billing), Lassie (Herds), etc.

report_conversation

Feed learnings back to V.E.T.S. at the end of productive IDE sessions. Extracts corrections, conventions, gotchas. Tracks files, tables, and procedures touched. Creates entries in the Learning Pipeline for expert review.

query_pims

Execute SQL against the PIMS database. Use views (atbv_*, stbv_*) for proper security. Returns JSON results. The foundation for all other MCP operations.

list_tables / list_stored_procedures

Discover the database structure. Filter by pattern (e.g., atbl_AI%). Essential for understanding how data is organized before querying.

The Learning Loop

Development sessions generate learnings. report_conversation captures them. The Learning Pipeline (atbl_AI_Learning) holds them for review. Promoted learnings become TeamDoc comments with lesson: tags. RAG indexes them. Future Minion responses improve. The system gets smarter through use.

Setup: Configure MCP in Cursor via mcp.json. Endpoint: https://vetslivestockmanagement.com/Application/AI/MCP/MCPHandler.ashx. See MCP Setup Guide.

Resources & Documentation

Architecture

Technical Overview - Platform architecture and design principles

Security

Permission Model - 6-layer security architecture details

TeamDoc System

Overview - Collaboration and content management

AI Features

AI & Knowledge Management - Minions, Land of Oz, and the learning pipeline

Key Files

MobileAJAX.aspx.vb - Main AJAX command router
AIAssistAJAX.aspx.vb - AI integration handler
appframe.config - Host routing and service discovery
web.config - IIS and ASP.NET configuration

Development Workflow

1. Create/modify stored procedure in SQL Server

2. Add command handler in appropriate AJAX.aspx.vb file

3. Call from JavaScript using AJAXCall()

4. Test permissions across different user roles

Developer Resources

Core Procedures
Complete procedure reference by category Deep Architecture
Advanced patterns for V.E.T.S. development AI & Knowledge Management
The AI layer you are building on




Standard UserName/Password Login:

Username:
Password:

Create Account