---
name: smartstack-entity-audit
description: Audit the GENERATED SmartStack app's LIVE database for referential-integrity gaps — every column ending in `Id` that should be a foreign key but is covered by NO integrity constraint. Runs SQL against the real database (SQL Server via sqlcmd) and classifies each finding CRITICAL / EXEMPT / REVIEW. Use when verifying that generated entities have real FKs (Tenant, cross-module, Core references), after generation/upgrade, or on demand.
argument-hint: "[project path]"
allowed-tools: [Bash, Read, Glob, Grep]
---

# smartstack-entity-audit — referential-integrity audit (live DB)

## Why this exists

A cross-table reference without a foreign key violates the fundamentals of a
relational database. This skill is the **empirical** guard: instead of trusting
what the generator emitted, it queries the **real database** and reports every
`*Id` column that points at another table but has **no FK constraint** — the
exact class of bug where `TenantId`, cross-module ids and SmartStack Core
references were left as bare `Guid`s.

It complements the static gates (`audit-dev-data` DEV-DAT-008, `audit-data-model`
DM-013) — those read the spec/code; this one reads the truth in SQL.

## When to use

- After `/ba-develop` (or `ss upgrade`) to confirm FKs actually landed in the DB.
- When investigating missing-FK reports ("Tenant is not a foreign key").
- As a pre-flight integrity check before shipping a module.

Requires a reachable database and `sqlcmd` on PATH (ships with the SQL Server
command-line tools). The connection string is read from the project's
`appsettings.Local.json` → `appsettings.Development.json` → `appsettings.json`
(`ConnectionStrings:DefaultConnection`), or pass it explicitly.

## How to run

```bash
npx --prefer-offline tsx skills/smartstack-entity-audit/cli/entity-audit/index.ts \
  --spec '{"projectPath":"<ABSOLUTE PROJECT ROOT>","schemas":["extensions"]}'
```

`--spec` (JSON) fields:

| Field | Default | Meaning |
|-------|---------|---------|
| `projectPath` | — (required) | Absolute path to the generated project root. |
| `schemas` | `["extensions"]` | Schemas whose `*Id` columns are audited (the business schema; principals are resolved across ALL schemas, incl. `core`). |
| `connectionString` | resolved from appsettings | Override the DB connection. |
| `allowlist` | `[]` | Extra case-insensitive regex sources to treat as non-FK identity/audit columns, on top of the built-in list. |
| `writeReport` | `true` | Write `<projectPath>/.smartstack/_audit/entity-fk-audit.md`. |
| `sqlcmdPath` | `"sqlcmd"` | Path to the sqlcmd executable. |

## How a finding is classified

For every `*Id` column (excluding the PK `Id`) with **no covering FK**:

- **CRITICAL** — its name resolves to an existing table (e.g. `TenantId` →
  `core.tenant_Tenants`, `ClientId` → `*_Clients`) but there is no FK → an
  integrity violation that must be fixed.
- **EXEMPT** — an identity/audit column that is intentionally NOT a FK, aligned
  with SmartStack.app (a referenced user may be soft-deleted or synced from an
  external IdP). Default allowlist: `CreatedByUserId`, `UpdatedByUserId`,
  `ModifiedByUserId`, `DeletedByUserId`, `ChangedByUserId`, `ApprovedByUserId`,
  `AssignedToUserId`, `UserId` (see `lib/fk-allowlist.ts`). **`TenantId` is NOT
  exempt — it must be a real FK.**
- **REVIEW** — ends in `Id` but no principal table resolves (likely an
  external/opaque id such as `CorrelationId`); surfaced for human confirmation.

Columns already covered by a FK are counted `ok` and not listed.

## Output

A standard JSON envelope on stdout (`report` carries `criticalCount`,
`reviewCount`, `exemptCount`, `okCount` and the per-column `findings`), plus a
Markdown verdict at `.smartstack/_audit/entity-fk-audit.md`. **Exit code is 1
when `criticalCount > 0`** (so it can gate a pipeline); REVIEW is advisory.

After running, read the envelope: for each CRITICAL column add the missing FK
(re-run `/ba-develop` Phase 2 — `scaffold-entity` now emits Tenant / cross-module
/ Core FKs — or add `HasOne<…>().WithMany().HasForeignKey(…)` and regenerate the
migration), then re-run this audit to confirm 0 critical.