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.

Database Scale

1,244 tables · 2,850 views · 2,749 stored procedures · 278 functions

SQL Server 2008 R2 with strict naming conventions encoding security and purpose into every object name.

Key Technologies

ASP.NET Web Forms · SQL Server · Telerik RadControls · DevExpress · Aspose · .NET Remoting

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.

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 Integration - Knowledge curation and content generation

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




Standard UserName/Password Login:

Username:
Password:

Create Account

Anonymous (not logged in)

	Navigation Shop online Our Other Sites BluepepperValentine.com Roughstock Broker facebook V.E.T.S. on Theiss Humble Hairpin Land & Livestock About Get Started Overview Executive Summary Community & Partners How It Works How It Works Core Concepts Use Cases AI & Knowledge Management Platform Overview Platform Vision Technical Architecture Advanced Architecture The Problem We Solve Technical/Development Development Roadmap Security & Permission Core Procedures For Developers