# Halo data model

> **Shared platform corpus** — `halo-platform/` is vendored byte-identical across the plugins that carry it; a consistency check enforces identity. Do not hand-edit a vendored copy — edit the canonical copy (see `halo-platform/README.md`) and re-run the corpus sync.

The Halo PSA database model: table domains, naming gotchas, JOIN patterns, the EAV asset pattern, the dual role model, and the standard GRCITSM tenant's ticket/asset taxonomy. Query dialects live in `halo-platform/sql-dialect.md`; write behavior in `halo-platform/write-semantics.md`.

## 1. Table domain overview

| Domain | Tables | Purpose |
|--------|--------|---------|
| Ticket Management | FAULTS, ACTIONS, TSTATUS, REQUESTTYPE, RelatedFaults, CATEGORYDETAIL | Incidents, changes, SRs, projects, vulnerability issues, deviations |
| CF Overflow | FaultsCustom1, FaultsCustom2, … | Newer ticket CFs — NOT on FAULTS. Join `pkid = Faults.faultid` |
| Workflow | FLOWHEADER, FLOWDETAIL, FlowStages | Workflow/stage/step — type-level binding via `REQUESTTYPE.RTWorkFlowID`; `FAULTS.FWorkFlowID` is a dead end |
| People & Orgs | UNAME (agents), AREA (clients), SITE (locations/SSP systems), USERS (portal contacts), UserRoles/UserRoleLink + NHD_Roles/NHD_UserRoles/NHD_User (dual role model) | Agents, customers, locations, end-users, role assignments. SITE doubles as the FedRAMP SSP-system entity |
| Asset Management | Device, deviceinfo, typeinfo, Xtype, DeviceChild, faultdevice | Assets via EAV pattern, parent-child links, ticket-asset junctions |
| Compliance | CTControlBaselines, CTControlRequirements, CTCategoriesControlsRequirements, CTValidations (1025) | Controls, requirements, crosswalks, validation runs |
| SLA & Priority | SLAHEAD, POLICY | SLA definitions, per-SLA priority names |
| Knowledge Base | KBENTRY | KB articles — shares the CF* custom field framework with FAULTS |
| Contracts | CONTRACTHEADER | Managed service contracts with billing terms and SLA overrides |
| Billing & Invoicing | INVOICEHEADER, INVOICEDETAIL, STDREQUEST, CALENDAR | Recurring invoice templates (IHid < 0), line items, schedules, date dimension |
| Lookups | LOOKUP | Generic `fid`/`fcode` → `fvalue` resolution for custom-field dropdowns |

## 2. Column naming gotchas

Halo's schema uses inconsistent casing and naming. The most common traps:

| What you might guess | Actual column | Table |
|---|---|---|
| `unumber`, `agent_id` | `Unum` | UNAME |
| `area_id`, `client_id` | `Aarea` | AREA |
| `area_name` | `aareadesc` | AREA |
| `site_id` | `Ssitenum` | SITE |
| `site_name` | `sdesc` | SITE |
| `status_id` | `Tstatus` | TSTATUS |
| `status_name` | `tstatusdesc` | TSTATUS |
| `type_id` | `RTid` | REQUESTTYPE |
| `type_name` | `rtdesc` | REQUESTTYPE |
| `title`, `subject` | `Symptom` | FAULTS |
| `ticket_type` (for JOINs) | `requesttypenew` (NOT `Requesttype`) | FAULTS |
| `priority` | `seriousness` | FAULTS |
| `client`, `area` | `Areaint` | FAULTS |
| `agent`, `assigned` | `Assignedtoint` | FAULTS |
| `when`, `date` | `Whe_` | ACTIONS |
| `team` | `sectio_` | FAULTS |
| `is_active` | `Uisdisabled` (inverted!) | UNAME |
| `is_inactive` | `AIsInactive` | AREA |
| `Slaid` (FK name) | `Slid` (actual PK) | SLAHEAD |
| `user_id` | `Uid` | USERS |
| `contract_id` | `CHid` | CONTRACTHEADER |

Never guess column names — consult the schema reference (dev-tooling `halo/halo-tables.json` where present, or `SHOW COLUMNS`).

**RequestType vs RequestTypeNew:** FAULTS has TWO ticket-type columns — `Requesttype` (type at creation) and `RequestTypeNew` (current type). **Always JOIN on `requesttypenew`**; it matches Halo dashboards.

## 3. Junction-table discovery for per-ticket-type lookups

When the question is "what {fields | statuses | categories} apply to ticket type Y", the answer is a **junction table** whose name contains both concepts — grep the schema catalog before guessing:

| Concept | Junction table | Keys |
|---|---|---|
| Fields per ticket type | `REQUESTTYPEFIELD` | `RTFRTid` → `REQUESTTYPE.RTid`, `RTFFieldId` → `FIELDINFO`, `rtfgroupid` → `FIELDGROUP.FGid` |
| Statuses per ticket type | `REQUESTTYPESTATUS` | confirm key columns in the schema catalog |
| Categories per ticket type | `RequestTypeCategory` | confirm key columns in the schema catalog |

**`FIELDDISPLAY` is NOT this table** — it is view-level visibility (form variants), not per-type assignment; using it silently returns 0 rows. Halo's naming is inconsistent (`RequestTypeCategory` vs `CategoryRequestType`) — grep both orders.

## 4. CF* unified custom-field framework

Multiple tables share the `CF`-prefixed tenant-configured custom-field pattern:

| Table | Prefix | Examples |
|---|---|---|
| FAULTS | `CF` | CFWeaknessDescription, CFFedRAMPControls, CFCVE, CFStrategicValue |
| SITE | `CF` | CFSystemName, CFBaselines, CFCloudServiceModel |
| Device | `CFA` | CFAFedRAMPControls, CFACMMCControls, CFAFedRAMPKSIs |
| KBENTRY | `CF` | Same pattern as FAULTS |
| CTControlBaselines | `CF` | CFControlID, CFFamily, CFControlName, CFBaselineName |

CF* fields storing integer codes require LOOKUP-table joins to resolve display values (scalar-subquery idiom — `sql-dialect.md` § 6-7).

**Overflow trap:** FAULTS is NOT the only home of ticket CFs — newer CFs spill into `FaultsCustom1`/`FaultsCustom2`/… (`pkid = Faults.faultid`). FIELDINFO lists a CF without revealing its storage table. Probe-first rule: `sql-dialect.md` § 4.

**Rich-text CF column triples** (bare / `HTML` / `PLAIN` suffixes): `sql-dialect.md` § 5.

## 5. Canonical JOIN patterns

Human-readable names (`f` = `haloitsm.FAULTS`):

```sql
LEFT JOIN haloitsm.TSTATUS ts     ON f.Status = ts.Tstatus            -- ts.tstatusdesc
LEFT JOIN haloitsm.REQUESTTYPE rt ON f.requesttypenew = rt.RTid       -- rt.rtdesc
LEFT JOIN haloitsm.AREA a         ON f.Areaint = a.Aarea              -- a.aareadesc
LEFT JOIN haloitsm.UNAME u        ON f.Assignedtoint = u.Unum         -- u.uname
```

### Priority names (POLICY) — compound join

Priority display names are SLA-specific: `LEFT JOIN haloitsm.Policy p ON f.seriousness = p.Ppolicy AND f.slaid = p.Pslaid`. Never join on `seriousness` alone — the same number maps to different names under different SLAs.

### SLA breach gating — use SLAHead tracking flags, not date sentinels

Gate each clock on the SLA's own flags: response on `SLAHead.sltrackslaresponsetime`, resolution on `SLAHead.sltrackslafixbytime`. Halo leaves `Faults.FRespondByDate`/`fixbydate` null OR carrying a `9999` placeholder even when the SLA doesn't track that clock, so date-based "is this clock defined" checks produce PHANTOM breaches. Targets live in `Policy.Ptime`/`Punits` (resolution) and `Policy.Presponsetime`/`PResponseUnits` (response); `9999` = disabled placeholder.

### Custom-field lookups (LOOKUP table)

Preferred scalar subquery (idiomatic Halo T-SQL):

```sql
(SELECT fvalue FROM haloitsm.Lookup WHERE fid = {LOOKUP_ID} AND fcode = f.{CF_COLUMN} LIMIT 1) AS DisplayName
```

Use a LEFT JOIN form only when filtering or grouping by the resolved value. `fid` families are cataloged in the dev-tooling `halo/halo-lookups.json` where present — resolve, don't memorize. **`[Impact Level]` fid ambiguity:** documented as `fid=147`, but some tenants only return data on `fid=196` — confirm on a known-good row before relying on either.

### Related tickets (RelatedFaults) — bidirectional UNION

```sql
LEFT JOIN (
  SELECT rffaultid1 AS main_fault, rffaultid2 AS related_fault FROM haloitsm.RelatedFaults
  UNION
  SELECT rffaultid2 AS main_fault, rffaultid1 AS related_fault FROM haloitsm.RelatedFaults
) AS rf ON f.faultid = rf.main_fault
```

### Parent-child tickets and devices

Tickets: self-JOIN via `fxrefto` (`LEFT JOIN haloitsm.Faults pf ON f.fxrefto = pf.faultid`). Devices: `DeviceChild` — `DCID` = parent, `DCCID` = child:

```sql
INNER JOIN haloitsm.DeviceChild dc ON child.Did = dc.DCCID
INNER JOIN haloitsm.Device parent  ON dc.DCID = parent.Did
```

### Asset EAV pattern (Device + deviceinfo + typeinfo)

Asset field values use an Entity-Attribute-Value pattern:

```sql
(SELECT idata FROM haloitsm.deviceinfo
 WHERE inum = d.Did
 AND iseq = (SELECT Xseq FROM haloitsm.typeinfo
             WHERE Xfieldnos = {FIELD_NUM} AND Xnum = d.Dtype LIMIT 1)
 LIMIT 1) AS AliasName
```

Field numbers are constant across device types. Combined fields use `CONCAT()`, not MSSQL `+`.

### Recurring invoice conventions

- Templates: negative `IHid` (`IHid < 0`) with `IHisRecurringInvoice = 1`; always filter `idisInactive = 0` on INVOICEDETAIL.
- Two INVOICEDETAIL join paths: `IDIHid = INVOICEHEADER.IHid` = the template's own line items; `idrecurringinvoiceid` = GENERATED invoices back-linked to the template (NOT for template line items). Confusing the two is the classic trap.
- Schedule: `STDREQUEST.stdinvoiceid = INVOICEHEADER.IHid`; `StdPeriod` frequency: 1=Weekly, 2=Monthly, 3=Yearly, 4=Quarterly, 5=6-Monthly, 8=2-Yearly. `stdenddate` NULL or `year < 2000` = perpetual; `1899-12-30` = Halo null date.

## 6. People and organizations

### Client → Site → User model

A **Client (Area)** owns **Sites** (locations; in GRCITSM tenants a Site IS the SSP system boundary — "System ≡ Site") and **Users** (portal contacts). Portal-user-to-site linkage flows through the Client: `Users.uarea = Site.Sarea = Area.aarea`. **A user is associated with a Client, not a Site**, and role assignment is Client-scoped (no per-site role-link table exists) — a role fans out across all sites under that Client in "POCs per system" views.

### The dual role model

Halo carries TWO parallel, independent role catalogs. The SAME role name (e.g. "ISSO", "System Owner") can exist in BOTH; the key type is the only reliable disambiguator — never match on name alone.

- **Model A — customer-contact roles (integer-keyed):** `UserRoles` (`URid` PK) + `UserRoleLink` junction over `USERS`. Client-scoped via `Users.uarea = Site.Sarea`.
- **Model B — agent roles (GUID-keyed):** `NHD_Roles` (`Id` GUID PK, `IdInt` mirror) + `NHD_UserRoles` junction + `NHD_User` bridge over `UNAME`. Filter `NHD_User.Unum > 0` (real agents) and `UNAME.Uisdisabled = 0` (active).

A "who holds role X on system Y" query covering agents AND customer contacts is a UNION of both paths with a source discriminator. **Role GUIDs and int ids are tenant-specific — fetch live per tenant; never hardcode.**

`NHD_Roles` agent-role categories: **Capability** (permission tier — an agent needs one to act), **Agent Assignment** (single-holder per role; auto-routes that ticket type — granting to a second agent displaces the first), **Approver** (per CAB, multiple holders), **Agent Membership** (team/section), **ISSO / System Owner** (POC/reporting-only — confer NO permissions), **Notifications** (routing only).

### UNAME ≠ USERS column names

`UNAME` does NOT share `USERS`' column names; querying it with USERS field names silently returns empty. Email is `usmtp` (not `uemail`); phone/extension is `uextensionnumber` (not `uextn`); id is `Unum` (not `uid`); names are `ufirstname`/`ulastname`/`UJobTitle`.

## 7. Ticket taxonomy (standard GRCITSM tenant)

### Type families

| Family | Types | Notes |
|---|---|---|
| Work containers (top-level, no parent) | Project (type 5), Internal Project | Engagement / internal work containers |
| Work items (always parented) | Project Task (type 20), Internal Project Task, ConMon Task | Tasks under containers; ConMon Tasks recur monthly/quarterly/annually |
| Vulnerability & compliance | Issue (per-CVE finding), Vulnerability Deviation, Data Request, Validation (type 49) | Deviation (Awaiting Approval) → Issue children |
| Operational & service | Alert, Change Request, Service Request, User Access Request, Phishing Email | Alerts are automation-created |

Hierarchy rides `Faults.fxrefto` (`parent_id` in API bodies): Project → Project Task; Internal Project → Internal Project Task; Vulnerability Deviation → Issue.

### Statuses

General ladder: `New → In Progress → Completed`, with `Pending`, `On Hold`, `Backlog` side states. Approval ladders: `New → Awaiting Change Review → Approved` (Change Requests); `New → Awaiting Approval → Approved` (Deviations, User Access Requests). Valid statuses are ticket-type-scoped — resolve via `ticket_type_allowed_statuses`, never the global catalog.

**Backlog semantics:** Backlog = acknowledged, not prioritized. A Backlog container deprioritizes ALL its children regardless of their own status; Backlog items past their target date are NOT overdue/SLA-breached (intentionally deprioritized); count Backlog separately in summaries.

### Priority

Ladder: Critical > High > Moderate > Medium > Low — **"Moderate" and "Medium" are distinct levels.** Priority labels are SLA-scoped (`api-mcp-semantics.md` § 7); resolve via `slas_and_priorities` before presenting.

### Categories

Hierarchical, `>`-delimited paths (e.g. `Continuous Monitoring > Vulnerability > Operating System`). Category validity is ticket-type-scoped — resolve via `ticket_type_allowed_categories`.

### Date sentinels

`1/1/1900` target date = no target set; `1899-12-30` = Halo null date; `9999` in SLA columns = disabled placeholder. Never present a sentinel as a real date.

## 8. Asset taxonomy (standard GRCITSM tenant)

Asset types (Device.dtype) — always cross-check against the live `asset_types` catalog before writing:

| Type | dtype |
|---|---|
| Component | 173 |
| Capability | 178 |
| Cloud Asset | 188 |
| Hardware Asset | 189 |
| Implemented Requirement | 190 |
| Implementation Statement | 191 |
| Control Implementation | 192 |
| Set Parameter | 193 |

Typed asset-relationship IDs (SSP spine; prefer the live restriction table or the per-response `assettype_config.upstream/downstream_relationship_restrictions` — the legacy `DeviceRelationshipRestriction` table can be empty even when relationships are configured):

| Relationship | id |
|---|---|
| Capability → Component | 6 |
| Implemented Requirement → Implementation Statement | 7 |
| Component → Implementation Statement | 8 |
| Cloud Asset → Component | 9 |
| Control Implementation → Implemented Requirement | 10 |
| Control Implementation → Set Parameter (Scope=System) | 11 |
| Implemented Requirement → Set Parameter (Scope=Control) | 12 |

The deep SSP asset model (per-type field schemas, AFUUID conventions, OSCAL mappings, spine write order) is domain doctrine owned by the SSP-authoring plugin's skills, not this corpus.

## 9. Useful filters

| Goal | SQL pattern |
|---|---|
| Open tickets only | `WHERE f.Status NOT IN (8, 9, 20)` |
| Exclude deleted | `WHERE f.FDeleted = 0` |
| SLA breached | `WHERE f.Slastate = 'B'` |
| High priority+ | `WHERE f.seriousness <= 2` |
| Category path match | `WHERE f.category2 LIKE '%Parent>Child>Sub%'` |
| Exclude sensitive | `WHERE COALESCE(f.fissensitive, 0) != 1` |
| KSI-related tickets | `WHERE f.CFFedRAMPKSIs IS NOT NULL AND f.CFFedRAMPKSIs != ''` |
