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 lets AI assistants query PIMS as your AppFrame login (same permissions as the website). Use the live setup panel below to connect.
Works well today: Claude Connectors, ChatGPT/Claude via SuperAssistant (Streamable HTTP), Cursor. Not MCP: Gemini web tab sharing, Edge Copilot.
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_Animals |
stbx_ | System view with cross-domain security | stbx_System_Domains |
aviw_ | App multi-table/computed view | aviw_VETS_AnimalswithOwnership |
sviw_ | System multi-table/computed view | sviw_TeamDoc_MembersWithName |
arpt_ | Reporting view (read-only, aggregated) | arpt_VETS_Rabies |
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_UpdateRecord |
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_* functions
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. 14 input types are in use today (Subject, Comment, WebPage, Task, Chart, FileFolder, PhotoAlbum, ContactList, File, Image, Poll, DBReport, Article, MoMItem). About 5 more are built in the platform but unused as live input types (email archives/messages, SMS, meetings, task summaries). Further types have not been invented yet and may still be needed before go-live.
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.
REST API
API Test
SQLAccess Data Layer
The Appframe.Web.Data.SQLAccess class provides all database connectivity:
SQLAccess.GetData(sql) - Returns DataTableSQLAccess.GetDataSet(sql) - Returns multiple result setsSQLAccess.ExecuteSQL(sql) - Returns rows affectedSQLAccess.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)
Writing Secure Queries
Every read passes through six permission layers — domain isolation, table-level grants, TeamDoc membership, ownership, row-level criteria and permission-aware rendering — enforced inside the views and procedures you call rather than in application code, which is why the rules below are not optional. See how each of the six permission layers is enforced →
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
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.
VETSMCP Tools (Model Context Protocol)
VETSMCP is the login-based MCP for external AI assistants (Claude Connectors, ChatGPT/Claude via SuperAssistant, Cursor, and others). You connect with your AppFrame username and password; every tool runs as your login with the same permissions as the website. Use the Connect your AI - Setup guide on this page (or Setup.aspx) to get connected.
This is separate from the privileged desktop developer MCP used only by internal tooling.
10 tools are available once connected:
vets_guide
Call this first. Returns a system map: where animals, herds, clients, billing, and medical data live; which Minion to ask; safe SQL patterns; and quick-start goals.
ask_minion
Ask a V.E.T.S. domain expert and retrieve RAG context as markdown. Use auto for routing, or name Florence (medical), Penny (billing), Lassie (herds), Otter (clients), Spot (reports), Oz (AI), Dr. Dolittle (general). Answer from the returned context and cite PrimKeys.
get_kb_document
Fetch the full knowledge-base document by PrimKey after ask_minion when an excerpt is incomplete or you need the full chunk.
query_pims
Run live SQL as the signed-in user. SELECT on views only (atbv_*, aviw_*, stbv_*, sviw_*), always WITH (NOLOCK). Center of the system: atbv_VETS_Animals.
list_tables
Discover views/tables by pattern when you do not know the object name (e.g. atbv_VETS%, %Animal%, %Herd%, %Client%). Prefer views.
get_table_info
Get columns for a view or table before writing SQL. Use after list_tables to inspect atbv_VETS_Animals and other objects.
list_stored_procedures
List procedures by pattern (e.g. astp_VETS%, astp_AI%). Most day-to-day work stays on ask_minion + query_pims views.
ping
Confirm VETSMCP is connected and return the AppFrame username plus permission level. Useful when identity is unclear.
get_developer_mode
Check whether this login can touch tables/DML/DDL or must stay on secure views only.
report_conversation
Optional: save a productive session summary into the V.E.T.S. learning pipeline for expert review. Not required for normal animal/herd/clinic questions.
Setup: Open
Connect your AI - Setup guide. Sign in with your AppFrame login, then follow the steps for your assistant (Claude Connectors, SuperAssistant Streamable HTTP, Cursor, etc.). Tools appear as
VETSMCP / AppFrame User MCP once connected.
Resources & Documentation
TeamDoc System
Overview - Collaboration and content management
Key Files
MobileAJAX.aspx.vb - Main AJAX command routerAIAssistAJAX.aspx.vb - AI integration handler
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