Tenancy is a column, not a database

Every tenant shares one database and one schema. Separation comes from a Domain column, carried by 450 of the 1,113 user tables.

Layer 0 is that boundary. sfnc_System_GetDomain resolves a login to its working domain by reading CurrentDomain from stbl_System_Users, and the generated views call it. Nothing else here matters if this check fails.

Isolation by column is cheap to operate, but the boundary must then be correct in every view — and a view that omits the domain predicate does not fail loudly, it returns too much. Hence the generator.

See how the multi-tenant schema is laid out →

The secured views are generated, not written

Of 2,283 views, aviw_ (764) and sviw_ (350) compose data. Security is enforced by a different set: atbv_ (600), stbv_ (318), atbx_ (76) and stbx_ (34).

sstp_Database_CreateView writes them. It takes a table name and one flag, @IsTBX, and that flag is the entire difference between the secured families: tbx_ views point at sviw_System_MyPermissions, which spans every domain you hold a grant in; tbv_ views point at sviw_System_MyPermissionsCurrentDomain, filtered to the domain you are in now.

If the table carries a Domain column, the generator adds Domain = <table>.Domain to the predicate. The tenant check is not a convention people follow; it is compiled into the view:

CREATE VIEW [dbo].[atbv_Items_Items]
AS
SELECT *
    FROM atbl_Items_Items WITH (NOLOCK)
    WHERE EXISTS (SELECT *
                    FROM dbo.sviw_System_MyPermissionsCurrentDomain WITH (NOLOCK)
                    WHERE TableID = 'atbl_Items_Items')

Of 918 tbv_ views, 646 still carry that generated permission reference. The rest were hand-edited to express something the generator cannot: atbv_VETS_PatientHistory keeps the domain check and adds reach-through, so a medical record is visible if you can see the animal, the client or the herd. A hand-edited view is one somebody has to review.

Layers 1 and 5 are the same table

Table-level permission and row-level filtering are normally described as two mechanisms. Here they are two readings of one row.

sviw_System_MyPermissions joins stbl_System_TableRowsPermissions to stbl_System_GroupsMembers on GroupRef, keeping rows where Login = SUSER_SNAME(). A matching row carries PGrant — a string of letters, S for select, U for update, I for insert, D for delete — and it also carries Criteria1, Criteria2, Criteria1Revoke and Criteria2Revoke.

Layer 1 is PGrant: what you may do to the table. Layer 5 is the criteria columns: which rows you may do it to. One row, one lookup, two questions answered.

The generator compiles the criteria in alongside the rest: Criteria1 becomes a LIKE against a field named per table in stbl_Database_Objects, Criteria1Revoke a NOT LIKE. There are exactly two such fields, CField1 and CField2: two columns, pattern matching, no boolean logic. Note what is missing from that join: your login never holds the permission. Grants attach to a group and you reach them by membership, which is why the grant procedure takes a group reference rather than a login.

Read the procedures that grant and revoke access →

Membership, category and ownership

Layer 2 — the document you belong to

TeamDoc permissions live in stbl_TeamDoc_TeamDocPermissions, read through sviw_TeamDoc_MembersWithName, which resolves them through group membership to a login. Three levels exist: Reader, Editor and Manager. An entity and its TeamDoc share a primary key, so a secured view can ask whether you are a member of the record itself — which is why this layer carries most of the daily work.

Layer 3 — the category above it

TreeList nodes are not merely labels. 103 of them carry their own TeamDoc, so a grant made on a category reaches every record filed beneath it, and stays correct as new ones arrive.

Layer 4 — who owns the animal

Ownership gets its own layer because it is genuinely complicated. atbl_VETS_AnimalOwnership and atbl_VETS_HerdsOwnership record it with a Percentage, an isCustodian flag, purchase and sale dates, and an isHistorical marker. The reason is in those columns: an animal can be owned in fractions by three people, kept by a fourth who owns none of it, and sold next month. Each has a different legitimate claim, and the claim expires. No table-level grant expresses that.

See the animal record surface these layers protect →

Layer 6: the control that is never rendered

The last layer is the interface. V.E.T.S. builds much of its HTML inside stored procedures, and the permission check runs before the markup is assembled. An action you may not take is not greyed out or hidden with a stylesheet rule — it was never generated, and no handler waits behind it.

What that buys is easy to oversell, so state it precisely: by the time markup is built the rows are already filtered by the five layers beneath. Layer 6 does not protect the data; it removes the affordance — the button or menu entry that would otherwise tell an unauthorised user something exists.

How the database generates its own interface →

What the AI layer can and cannot reach

Does adding AI open a door around all of this? The precise answer is more useful than a reassuring one. The vector index holds documentation about the system: UI help text, knowledge-base articles, and generated descriptions of stored procedures and tables. All 2,333 indexed documents are of that kind. Your animal records are never embedded and are not in the index. Neither are client details or patient history.

So the retrieval path does not re-check your permissions, and does not need to: there is no tenant data in the corpus to check. Documentation reads the same for everyone.

What actually gets embedded, and how retrieval works →