> For the complete documentation index, see [llms.txt](https://docs.snapfix.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.snapfix.com/engineering-reference/db.md).

# Database

{% hint style="info" %}
Draft, written by an agent and not yet checked by a person ([how to read these docs](https://docs.snapfix.com/how-to-read.md)). [Source on GitHub](https://github.com/snapfix/docs/blob/main/spec/db/README.md).
{% endhint %}

The staging MySQL schema `live_001` on Aurora MySQL 3 (MySQL 8.0): 343 tables, 6 triggers and 1 stored procedure, no views. Its data looks like a copy of production: the newest rows in busy tables are from 2026-09-24.

Generated by `scripts/db.sh`:

* [schema.sql](https://github.com/snapfix/docs/blob/main/spec/db/schema.sql): the full schema as SQL, for tools and AI.
* [tables/](https://docs.snapfix.com/engineering-reference/db/tables.md): one page per table, with columns, keys, triggers and a diagram of its direct relations, plus one page per area (viewpoint) with a diagram of that area's tables. The index has no diagram of the whole schema: `scripts/db.sh tables` removes it, because GitHub cannot render one that big. The index also lists two procedures from the `live_api_ip_logs` schema, a tbls quirk.
* [tbls.yml](https://github.com/snapfix/docs/blob/main/spec/db/tbls.yml): notes on tables and columns, which appear on the table pages, and the areas.

Never edit `schema.sql` or `tables/` by hand. Notes go in `tbls.yml`, followed by `scripts/db.sh tables` (about a minute). Explanations that span tables go on this page. A full `scripts/db.sh` run takes about 25 minutes, mostly the schema dump. `scripts/db.sh rows` writes estimated row counts to `.tmp/db/rows.tsv`.

The rows are real customer data. How to query them, and what may be copied, is in [AGENTS.md](https://github.com/snapfix/docs/blob/main/AGENTS.md#safety).

## Naming

A table name starts with a prefix that says what kind of data it holds. The words after the prefix name the thing one row is about, often with a word the apps do not show. The [glossary](https://docs.snapfix.com/product/glossary.md) gives each product term its main table, and lists these code words in its "Also called" column.

| Prefix                                                                                      | Tables | What it holds                                                                                                                            | Example                                                                                                        |
| ------------------------------------------------------------------------------------------- | ------ | ---------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- |
| `tPri_`                                                                                     | 139    | Customer data: sites, groups, accounts, tasks, checklists, rooms, and their logs. The apps download most of these.                       | [`tPri_Job`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_job.md)                             |
| `tPub_`                                                                                     | 14     | Lists shared by every customer, with no site column (`uuid_tParent`): default statuses and tags, help links, API endpoint lists.         | [`tPub_Status`](https://docs.snapfix.com/engineering-reference/db/tables/tpub_status.md)                       |
| `tServerOnly_`                                                                              | 3      | Rows the server writes for its own use: messages sent, email events and device login tokens.                                             | [`tServerOnly_Comm`](https://docs.snapfix.com/engineering-reference/db/tables/tserveronly_comm.md)             |
| `enum_`                                                                                     | 7      | Code lists: the code is in `uuid` and its name in `name` (see [Code lists](#code-lists)).                                                | [`enum_JobType`](https://docs.snapfix.com/engineering-reference/db/tables/enum_jobtype.md)                     |
| `std_`                                                                                      | 9      | Standard reference lists: countries, continents, currencies, industry codes.                                                             | [`std_countries`](https://docs.snapfix.com/engineering-reference/db/tables/std_countries.md)                   |
| `comply_`                                                                                   | 18     | Snapfix Comply: systems, contractors, documents and their links and logs.                                                                | [`comply_System`](https://docs.snapfix.com/engineering-reference/db/tables/comply_system.md)                   |
| `ai_`                                                                                       | 13     | Snapfix AI: agent sessions and messages, brand documents, task insight data, token usage.                                                | [`ai_token_usage`](https://docs.snapfix.com/engineering-reference/db/tables/ai_token_usage.md)                 |
| `opera_`, `cloudbeds_`, `guestline_`, `stayntouch_`, `jonaschorum_`, `comtrol_`, `hotsoft_` | 51     | One property management system (PMS) integration each: building and room mappings, room statuses, inbound and outbound queues, webhooks. | [`opera_RoomMapping`](https://docs.snapfix.com/engineering-reference/db/tables/opera_roommapping.md)           |
| `tPri_Log*`, `*_Log`, `log_`                                                                |        | Logs of actions, views and requests. Log tables grow without limit.                                                                      | [`tPri_Log_JobViewedUser`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_jobvieweduser.md) |
| `*_copy*`, `temp_`                                                                          |        | Copies and scratch tables (see [Legacy](#legacy)).                                                                                       | [`tPri_UserParent_copy1`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userparent_copy1.md)   |

* Smaller families have their own prefix: `package_`, `schedule`, `blacklist_`, `qr_`, `sso_`, `hubspot_`, `xero_`, `stripe_`, `reg_industry`, `dynamiclink` and `mkt_`. Some older tables have no prefix (`assetMain`, `token_jwt`).
* Names mix two styles: older tables use CamelCase after the prefix (`tPri_JobTodo`), newer ones lowercase words (`ai_token_usage`). Copies end in `_copy`, `_copy1` or a date.
* No table name starts with `clock_`, `deputy_` other than [`deputy_auth`](https://docs.snapfix.com/engineering-reference/db/tables/deputy_auth.md), or `protel_`: the Clock and Protel integrations have no tables of their own on staging. Whether they run is open \[[ASK-13](https://github.com/snapfix/docs/blob/main/ASK.md#ask-13-which-services-and-scheduled-jobs-run-in-production)].
* The code words for site, group, task and the other main concepts are in the glossary's "Also called" column. One word has no glossary term: `StatusSub`, a group's sub-status label ([`tPri_StatusSub`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_statussub.md)).

## IDs and links

* `id`: an internal auto-increment number, the primary key. 321 tables have it: 311 as `int`, 9 as `bigint`, and [`failed_jobs`](https://docs.snapfix.com/engineering-reference/db/tables/failed_jobs.md) as a `bigint` without auto-increment. No foreign key points at `id`.
* `uuid`: the ID the apps and APIs use. 311 tables have it: 303 as `varchar(255)` and 8 as `char(36)` (four `ai_` tables, `saved_dashboard_views`, `tPri_ChecklistLog`, `tbl_email_logs` and `tbl_sms_logs`). 303 tables have a unique index on it; 8 do not, among them [`tPri_Job_StatusLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_job_statuslog.md) and [`tPri_Log_JobViewedUser`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_jobvieweduser.md).
* php-apis makes new values with `NewGuid()` and lower-cases them (php-apis @ 238ca4414e, `htmlphp/api/snapfix/class/class.functions.php:983`).
* `uuid_<name>`: the `uuid` of the row it points to. The name says the target: `uuid_tParent` a site, `uuid_tBusiness` a group, `uuid_tBuilding` a building, `uuid_tUser` an account.
* `uuid_tUser_CreatedBy`, `uuid_tUser_ModifiedBy`, `uuid_tUser_DeletedBy` and `uuid_tUser_ArchivedBy` hold the account that made the change. The Laravel API sets the creator and the modifier when it creates a row (api\_php\_live\_v2 @ ef3c4319f6, `app/Traits/ModelUpdate.php:17-18`).
* Foreign keys: the schema declares 312. Of the 1,047 `uuid_` columns, 306 have one; the rest are links by convention only, so the database does not stop a link to a row that does not exist. For example, `tPri_Job.uuid_tStatusSub` has no foreign key, and 37 of the newest 200,000 tasks point to a sub-status that does not exist.
* Most columns start with `f` (`fName`, `fDeleted`). The table pages say what each one means.
* Some IDs come from outside Snapfix: PMS room and building IDs in the mapping tables, and the device ID `tPri_Device.device_id`, which has no index.

### Code lists

* A column named `enum_<name>` holds a code. The `enum_<name>` table names it: the code is the row's `uuid`, not its `id`. Task type `5` is the row of [`enum_JobType`](https://docs.snapfix.com/engineering-reference/db/tables/enum_jobtype.md) whose `uuid` is 5, `web-normal`, and the same number is `NORMAL_TASK` in the Laravel API.
* The code lists and the API's own enums disagree. `enum_JobType` has no row for 45 and 46, which the API uses for cleaning and inspection tasks (api\_php\_live\_v2 @ ef3c4319f6, `app/Enums/JobType.php:13-14`) \[[ASK-246](https://github.com/snapfix/docs/blob/main/ASK.md#ask-246-should-the-enum-lookup-tables-follow-the-code-or-be-retired)]. [`enum_Origin`](https://docs.snapfix.com/engineering-reference/db/tables/enum_origin.md) has no row for 11, the vendor API (`app/Enums/EnumOrigin.php:18`). [`enum_ScheduleCategory`](https://docs.snapfix.com/engineering-reference/db/tables/enum_schedulecategory.md) has no `duedatenotif`, which the API uses (`app/Services/ScheduleService.php:141`) \[[ASK-246](https://github.com/snapfix/docs/blob/main/ASK.md#ask-246-should-the-enum-lookup-tables-follow-the-code-or-be-retired)].
* Some status columns hold other things than a state: `stripe_subscription.subscription_status` holds the name of the last Stripe event, such as `customer.subscription.updated` (`app/Services/StripeService.php:103`).
* The values of each code column are on its table page.

## Accounts, sites and groups

Every customer's data sits under these levels. The apps and the database use different names for them.

| Level                | Table                                                                                          | Name in the apps | What it is                                                                                                                                                                                                                                                                                                 |
| -------------------- | ---------------------------------------------------------------------------------------------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Billing organisation | [`tPri_Org`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org.md)             | none             | Billing records (Stripe, GoCardless, Xero, HubSpot). A site points to one with `uuid_tOrg`.                                                                                                                                                                                                                |
| Site                 | [`tPri_Parent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_parent.md)       | "Site"           | The customer's site, with its settings, buildings and members.                                                                                                                                                                                                                                             |
| Group                | [`tPri_Business`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_business.md)   | "Group"          | A board of tasks or assets inside a site, with its own members and statuses. Every task belongs to one group.                                                                                                                                                                                              |
| Multi-site           | [`tPri_MultiSite`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_multisite.md) | "Multi-site"     | A set of sites that one account can work in. Sites join it through [`tPri_MultiSiteParent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_multisiteparent.md) and accounts through [`tPri_MultiSiteUser`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_multisiteuser.md). |

```mermaid
flowchart TD
    org["tPri_Org: billing organisation"] --> site["tPri_Parent: site"]
    site --> building["tPri_Building: building"]
    building --> room["tPri_Room: room"]
    site --> group["tPri_Business: group"]
    group --> task["tPri_Job: task"]
    user["tPri_User: account"] --> up["tPri_UserParent: site membership"]
    up --> site
    user --> ub["tPri_UserBusiness: group membership"]
    ub --> group
```

### Memberships

* An account is one row in [`tPri_User`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_user.md). Every other scope is a membership.
* [`tPri_UserParent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userparent.md) puts an account in a site. Its `user_type` is `admin` or `normal`, and on a few rows `contractor` or `user`. Site-level actions check for `admin` (api\_php\_live\_v2 @ ef3c4319f6, `app/Policies/ParentPolicy.php:27`).
* [`tPri_UserBusiness`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userbusiness.md) puts an account in a group. It holds the role (`uuid_tUserType`) and one switch per permission, such as `perJobViewAll` or `perJobMoveToRed` (`app/Enums/Permission.php:12-14`). Roles and permissions are explained in [roles](https://docs.snapfix.com/product/roles.md).
* A multi-site member also sees the groups of the sites in that multi-site. A removed membership can still show a group there.

### How scope reaches every row

* A group carries its site in `uuid_tParent`. A task carries its group in `uuid_tBusiness` and has no `uuid_tParent`: to find a task's site, join through `tPri_Business`. Rows under a task carry the group too (`tPri_JobChat`, `tPri_JobTodo`).
* Site-level rows carry `uuid_tParent` (54 tables), group-level rows `uuid_tBusiness` (50 tables), multi-site links `uuid_tMultiSite` (5 tables). `uuid_tOrg` is only on `tPri_Parent` and its copy.
* Other tables reach a site through a parent row: a room carries its building (`uuid_tBuilding`), and the building carries the site.
* `uuid_tOrg` is set on 637 of the 43,441 sites on staging. Whether every site needs one is open \[[ASK-254](https://github.com/snapfix/docs/blob/main/ASK.md#ask-254-must-every-site-have-a-billing-organisation)].
* The database does not enforce scope: MySQL has no row-level security, so each API query adds the site or group filter itself. A query without it reads other sites' rows.
* On task lists, the Laravel API keeps a task only when the account is an admin of its group, has the group's view-all switch (`perJobViewAll`), created the task, is assigned to it, or follows it (`app/Traits/RelevantJobs.php:31-38`). The full rules are in [task permissions](https://docs.snapfix.com/engineering-reference/api/tasks.md#task-permissions).

## Timestamps and time zones

### Milliseconds

* Most times are whole milliseconds since 1970-01-01 UTC, in `bigint` columns whose names end in `Ts`: `fCreatedTs` (on 302 tables), `fModifiedTs`, `fServerTs`, `fDeletedTs`, `fArchivedTs`, `fApprovedTs`, `expiryTs`, `nextTriggerTs` and more.
* The Laravel API writes them with `getUnixTimestampMs()` (api\_php\_live\_v2 @ ef3c4319f6, `app/Helpers/common.php:37-40`), and its base model stores its own created and modified times (`fCreatedTs`, `fModifiedTs`) in milliseconds (`app/Models/BaseModel.php:18-24`).
* `fServerTs` is when the server last wrote the row: the Laravel API sets it on every create, update and delete (`app/Traits/ModelUpdate.php:15-43`).
* Some tasks have creation times in the future, because the mobile apps set them from the phone's clock.
* Five `ai_` tables use `int unsigned` for `fCreatedTs`, `fModifiedTs`, `fUpdatedTs` and `indexed_at`, not `bigint` milliseconds. They look like seconds \[[ASK-244](https://github.com/snapfix/docs/blob/main/ASK.md#ask-244-which-service-writes-the-ai-agent-and-brand-document-tables-and-in-what-time-unit)].

### Time zones

* The database server runs on UTC, and so does the Laravel API (api\_php\_live\_v2 @ ef3c4319f6, `config/app.php:104`). Whether the scheduler and the other servers do is open \[[ASK-55](https://github.com/snapfix/docs/blob/main/ASK.md#ask-55-do-the-scheduler-and-api-servers-run-on-utc)].
* A site's zone is `tPri_Parent.timezone_name`, an IANA name such as `Europe/Dublin`, the column default. Every site on staging has one; 114 zones are in use, and 42,310 of the 43,441 sites hold `Europe/Dublin`. Sites made by Onboard keep the default whatever zone the form asked for.
* The Laravel API switches PHP to the site's zone for some work (`app/Traits/Job/JobActions.php:1140`, `:4946`, `:5002`). With an empty zone it falls back to UTC, the app's zone (`app/Helpers/common.php:515-523`). Some code works out "today" from midnight UTC instead of the site's midnight.
* php-apis builds task reports in the zone of the `device-tz` request header, and in UTC without it (php-apis @ 238ca4414e, `htmlphp/api/snapfix/class/business_job_report_v5.php:79`).
* Some columns are `datetime`, `timestamp` or `date`. Only the comments on `sent_at` in [`tbl_email_logs`](https://docs.snapfix.com/engineering-reference/db/tables/tbl_email_logs.md) and [`tbl_sms_logs`](https://docs.snapfix.com/engineering-reference/db/tables/tbl_sms_logs.md) give a zone (UTC); the zone of the others is open \[[ASK-256](https://github.com/snapfix/docs/blob/main/ASK.md#ask-256-which-time-zone-do-the-php-written-dates-and-the-shift-dates-use)]. Examples: `created_at` on `queue_ai`, `failed_at` on `failed_jobs`, and the date-only `fDate` on `tPri_JobNotif` and `session_date` on `tPri_User_WorkingHours`.
* The schedule tables ([`schedule_v3`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_v3.md) and its older versions) keep `lastTriggerDatetime` and `nextTriggerDatetime` as `datetime`, with the schedule's zone in their `timezone` column.

## Soft deletes and flags

### Soft delete

* `fDeleted` (`tinyint`, 1 = deleted) marks a row as deleted and keeps it. 246 of the 343 tables have it; rows of the other 97 tables are removed or never deleted.
* `fDeletedTs` (68 tables) holds when, and `uuid_tUser_DeletedBy` (66 tables) who. The Laravel API sets `fDeletedTs` and `fServerTs` when it deletes a model (api\_php\_live\_v2 @ ef3c4319f6, `app/Traits/ModelUpdate.php:35-43`).
* Every read has to filter `fDeleted = 0` itself. Some do not: the QR form count and list, the staff package list, and the Dashboard, which keeps counting tasks of deleted systems.
* Six unique keys in five tables include `fDeleted`, so a deleted row does not block a new one with the same value: `tPri_User` (mobile number; username or email), `tPri_CL_Tag`, `tPri_CL_TagHeader`, `saved_dashboard_views` and `jonaschorum_RoomMapping`.
* In the newest 200,000 tasks, about 9.6% are deleted.

### Other flags

* `fArchived` (11 tables) hides a row without deleting it, with `fArchivedTs` and `uuid_tUser_ArchivedBy`. In the newest 200,000 tasks, about 27% are archived. See [Archive](#archive).
* `fInActive` (49 tables) is not a delete flag. On [`tPri_Business`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_business.md) 1 means the group is inactive. On other tables its meaning is on the table page.
* `excludeFromApi` with `excludeFromApiTs` is on 80 tables. Code only reads it; nothing sets it to 1 or filters on it.
* Other flags, such as `fVisible`, are explained on the table pages.
* Guest records in [`tPri_RoomGuests`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_roomguests.md) hold personal data. How long they are kept is open \[[ASK-245](https://github.com/snapfix/docs/blob/main/ASK.md#ask-245-how-long-should-guest-records-and-personal-data-in-log-tables-be-kept)].

## Status lifecycles

Only changes that the code makes are drawn. Who may make each change is in the API docs linked below.

### Task colour

`tPri_Job.color` holds `red`, `amber` or `green` (api\_php\_live\_v2 @ ef3c4319f6, `app/Enums/JobColor.php`). The apps show them as Red, Amber and Green. A task made in the apps starts red, with the group's red status and its "new" sub-status (`app/Http/Controllers/v1/Versions/v1_0/App/JobController.php:300-304`); the Open API and vendor API can set another colour. Of the newest 200,000 tasks, about 53% are red, 45% green and 2% amber.

```mermaid
stateDiagram-v2
    [*] --> Red: made
    Red --> Amber: moved
    Red --> Green: moved
    Amber --> Red: moved
    Amber --> Green: moved
    Green --> Red: moved
    Green --> Amber: moved
```

* Each move sets the group's status for that colour (`uuid_tStatus`), the time (`fRedTs`, `fAmberTs`, `fGreenTs`) and the person (`uuid_tUser_Red` and so on) (`app/Services/AssetJobService.php:2766-2820`).
* A move needs the group's "move to" permission for that colour (`perJobMoveToRed`, `perJobMoveToAmber`, `perJobMoveToGreen`), or a group admin. Automatic moves skip the check when the group's settings allow them: amber when a checklist starts (`fAutoYellowWhenChecklistStart`) and green when it is completed (`fAutoGreenWhenChecklistCompleted`) (`app/Policies/JobPolicy.php:69-94`). The full rules are in [`PUT /v1/app/tasks/{uuid}`](https://docs.snapfix.com/engineering-reference/api/tasks.md#put-v1apptasksuuid).
* A snooze is kept only on red tasks: a move to amber or green clears it.
* A green task is late when `fGreenTs` is after `fDueDateTs` (`app/Models/JobModel.php:741-744`).

### Approval

`tPri_Job.fApprovalStatus` holds -1 (default, nothing to approve), 2 (pending), 1 (approved) or 0 (rejected) (`app/Enums/ApprovalStatus.php:7-10`). `fApprovalRequired` is -1 (follow the group's setting), 0 or 1. The feature is described in [task approval](https://docs.snapfix.com/product/features/tasks/task-approval.md).

```mermaid
stateDiagram-v2
    [*] --> Default: task made
    Default --> Pending: moved to green by a non-admin, approval needed
    Default --> Approved: moved to green by an admin, approval needed
    Pending --> Approved: approved, or approval switched off
    Pending --> Rejected: rejected
    Approved --> Rejected: rejected
    Rejected --> Approved: approved
    Pending --> Default: moved to red or amber
    Approved --> Default: moved to red or amber
    Rejected --> Default: moved to red or amber
```

* Every colour change sets the value again: a move to green sets pending or approved when approval is needed, and any other move sets default. Housekeeping tasks keep their value (`app/Services/AssetJobService.php:2766-2848`).
* A decision does not check the current value, so any value can become approved or rejected (`app/Services/AssetJobService.php:3216-3236`). A rejection needs a note, which goes to the task chat (`app/Http/Requests/v1/Job/UpdateAssetJobRequest.php:56-60`). Group admins decide, and so can anyone assigned to the task.
* Switching approval off on a green, pending task approves it (`app/Services/AssetJobService.php:3178-3194`).
* In the newest 200,000 tasks, 1,663 are pending. 335 red tasks and 3 amber ones hold 0 or 1, which the rules above would reset unless they are housekeeping tasks \[[ASK-255](https://github.com/snapfix/docs/blob/main/ASK.md#ask-255-why-do-some-red-and-amber-tasks-keep-an-approval-value)].

### Archive

`tPri_Job.fArchived` is 0 for an active task and 1 for an archived one.

```mermaid
stateDiagram-v2
    [*] --> Active: fArchived 0
    Active --> Archived: archived by a person, or by auto-archive
    Archived --> Active: unarchived by a person
```

* `fArchivedTs` and `uuid_tUser_ArchivedBy` record when and by whom.
* When a group's auto-archive is switched on (`fAutoArchiveEnabled` in [`tPri_SettingsBusiness`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_settingsbusiness.md)), saving the settings archives the group's green tasks older than `fAutoArchiveInterval` weeks (`app/Http/Controllers/v1/Versions/v1_0/App/BusinessController.php:436-439`).

### Checklist items

A checklist item on a task is a row in [`tPri_JobTodo`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobtodo.md). `item_type` 1 is a section heading and 0 an item, in the template's [`tPri_CL_Item`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_cl_item.md) rows too (`app/Enums/Todo.php:7-8`; web\_app\_v2 @ 0989b8e685, `src/app/services/checklist/checklist-helper/checklist-helper.service.ts:535`). Ticking an item sets `fDone` to 1 and, the first time, `fCompletionTs` to the time (api\_php\_live\_v2 @ ef3c4319f6, `app/Http/Controllers/v1/Versions/v1_0/App/JobTodoController.php:231-235`); resetting the checklist sets both to 0 (`app/Services/TodoService.php:103-106`). About 75% of the newest 200,000 items are not done.

```mermaid
stateDiagram-v2
    [*] --> Open
    Open --> Done: fDone 1
    Done --> Open: fDone 0
    Open --> Deleted: fDeleted 1
    Done --> Deleted: fDeleted 1
```

The template history in [`tPri_ChecklistLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_checklistlog.md) records `template_create`, `template_download`, `template_update`, `template_copy`, `template_move` and `template_remove` events (`app/Enums/ChecklistActivity.php:7-12`).

### Compliance colour (computed)

The `color` columns of [`comply_System`](https://docs.snapfix.com/engineering-reference/db/tables/comply_system.md) and [`comply_Contractor`](https://docs.snapfix.com/engineering-reference/db/tables/comply_contractor.md) are not used. The API works out the colour on each read: red after `expiryTs`, amber within `expiryAlertDays` days before it, otherwise green ([compliance item object](https://docs.snapfix.com/engineering-reference/api/compliance.md#compliance-item-object)).

```mermaid
stateDiagram-v2
    [*] --> Green: no expiry, or expiry far away
    Green --> Amber: within expiryAlertDays of expiry
    Green --> Red: expiry passed
    Amber --> Red: expiry passed
```

A system or contractor with no alert days goes from green straight to red. The 13 default systems a site gets have none \[[ASK-140](https://github.com/snapfix/docs/blob/main/ASK.md#ask-140-should-the-default-compliance-systems-get-the-30-day-expiry-alert)].

### Not drawn

* `dynamiclink_user.invitation_status` is written as `pending` or `exist` when an invitation is made (api\_php\_live\_v2 @ ef3c4319f6, `app/Jobs/Onboarding.php:148`, `:335`), and as `onboarded` on a new row when the invitee joins (php-apis @ 238ca4414e, `htmlphp/api/snapfix/class/class.functions.php:26498`). No code moves a row from one value to another.
* `fBacklog`, `fSnoozed` and `fSoftFreeze` are flags on a task, not a lifecycle. The snooze reasons are in [`PUT /v1/app/tasks/{uuid}`](https://docs.snapfix.com/engineering-reference/api/tasks.md#put-v1apptasksuuid).

## File storage

* The Laravel API uploads photos, videos, audio and documents to its default disk, `s3-bo`, an S3 bucket set to private (api\_php\_live\_v2 @ ef3c4319f6, `config/filesystems.php:16`, `:49-59`). A second disk, `s3-log`, is for logs (`:18`, `:63`). How each API uploads is in the [files API](https://docs.snapfix.com/engineering-reference/api/files.md).
* The upload helper puts each file in a folder for its type (`img`, `vid`, `aud` or `doc`), deletes the file it replaces, and returns a plain `https://` link to the file (`app/Helpers/common.php:160-191`). The rows store that link, not the file. Whether anyone can open a link without signing in is open \[[ASK-115](https://github.com/snapfix/docs/blob/main/ASK.md#ask-115-can-compliance-documents-report-files-and-the-comply-report-be-opened-from-their-link-without-signing-in)].
* Report files are public, and their names can be guessed.
* Deleting a document soft-deletes its row, and the file stays in storage \[[ASK-111](https://github.com/snapfix/docs/blob/main/ASK.md#ask-111-should-a-deleted-documents-file-be-removed)].

Columns that point to files (names only):

| What                    | Columns                                                                                                                                                                                                                                                                             |
| ----------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Pictures on records     | `fImage` on `tPri_Parent`, `tPri_Business`, `tPri_MultiSite`, `tPri_Org`, `tPri_User`, `tPri_Area`, `tPri_Building`, `tPri_Room`, `tPri_BuildingRoomStatus`, `tPri_ParentRoomStatus`, `tPri_Job`, `tPri_JobTodo`, `tPri_CL_Item` and `assetReg`; `fImageUrl` on `list_TaskImageUrl` |
| Task chat files         | `fFileUrl` and `fThumbnailUrl` on `tPri_JobChat`; `imageUrl` on `tPri_JobChat_VoiceTranscript`                                                                                                                                                                                      |
| Checklist media         | `media_url` and `thumb_url` on `tPri_JobTodoMedia` and `tPri_CL_ItemMedia`                                                                                                                                                                                                          |
| Compliance documents    | `key_full_path_and_filename` and `url` on `comply_Document`                                                                                                                                                                                                                         |
| Reports and exports     | `reportUrl` on `report_userreports`; `file_url` and `file_url_json` on `tPri_Log_EmailQrRequests`                                                                                                                                                                                   |
| AI                      | S3 keys, not links: `s3_key` on `ai_brand_documents`, `turnLogS3Key` on `ai_token_usage`                                                                                                                                                                                            |
| Marketing popups        | `image_landscape_url` and `image_portrait_url` on `mkt_popup`                                                                                                                                                                                                                       |
| Wallboards              | `hotel_logo_url`, `p2_logo_url`, `p2_image_body_url` and `rep_image_url` on `wallboard_instance`                                                                                                                                                                                    |
| Single sign-on pictures | `sso_image` on `sso_apple_user`, `sso_azure_user` and `sso_google_user`                                                                                                                                                                                                             |

* Byte counts sit next to some of them: `fImageByte` on `tPri_Job`, `fFileUrlByte` on `tPri_JobChat`, `filesize_bytes` on `report_userreports`.
* Some link columns point to other companies' systems, such as `stripe_invoice_pdf` on `tPri_Org_Stripe`.

## Columns that hold secrets

Names and storage only; this page never records a value.

| Column                                                                                                                                                                       | Stored as                                                                                                                                       |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| `tPri_Device.device_aes_key`, `device_totp_key`                                                                                                                              | Plain text, compared as stored (authpy @ 5d5758661c, `model/t_device_data.py:72-84`)                                                            |
| `tPri_Parent_Api.x-api-key`                                                                                                                                                  | Plain text, compared as stored (apipy @ 0e83c85e5a, `model/t_log_ip_blocked_v3.py:766-770`)                                                     |
| `tPri_Api_Simple_SecretKey.x-api-key-hash`                                                                                                                                   | A hash (api\_php\_live\_v2 @ ef3c4319f6, `app/Http/Controllers/v1/Versions/v1_0/Admin/PublicSimpleAPIController.php:49`)                        |
| `tPri_UserHashSalt.fHash`, `fSalt`                                                                                                                                           | RIPEMD-160 hash with a salt (`app/Helpers/common.php:32`)                                                                                       |
| `credential_code.code`                                                                                                                                                       | Plain text, compared as stored (`app/Http/Requests/v1/User/VerifyCodeRequest.php:20`) and never deleted                                         |
| `tServerOnly_Comm.fNewPassword`                                                                                                                                              | Plain text: the new password of a reset or an invitation (php-apis @ 238ca4414e, `htmlphp/api/snapfix/class/class.functions.php:4147`, `:4283`) |
| PMS secrets in the `*_BuildingSiteMapping` tables (`client_secret`, `access_token`, `refresh_token`, `webhook_apikey`, `partner_token`, `ba_password`, `basicauth_password`) | Plain text, used as stored (opera\_py\_server @ 61bc356c45, `app/api/v1/endpoints/opera.py:289`); Guestline keeps its passwords encrypted       |

## Other schemas on the cluster

From `SHOW DATABASES` on the staging cluster:

| Schema                                                     | Tables | Holds                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| ---------------------------------------------------------- | ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `live_001`                                                 | 343    | The Snapfix database this page covers, with 6 triggers and the procedure `cg_customer`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `live_api_ip_logs`                                         | 83     | API request logs: 82 daily tables (`apilog_YYYY_MM_DD`), the counter table `apilog_0_count`, and the procedures `Create_apilog_TablesBetweenDates` and `Drop_apilog_TablesBetweenDates`, which the [tables index](https://docs.snapfix.com/engineering-reference/db/tables.md) lists. The Laravel API writes the daily tables (api\_php\_live\_v2 @ ef3c4319f6, `app/Loggers/ApiIpLogger.php:11`), and so do php-apis (php-apis @ 238ca4414e, `htmlphp/api/snapfix/class/ip.functions.v3.php:1647`), authpy (authpy @ 5d5758661c, `model/t_log_ip_blocked_v3.py:1548`) and apipy (apipy @ 0e83c85e5a, `model/t_log_ip_blocked_v3.py:1147`). |
| `pymysql`                                                  | 1      | One table, `tPri_Business`. The repos name the `pymysql` driver, never this schema \[[ASK-257](https://github.com/snapfix/docs/blob/main/ASK.md#ask-257-is-the-pymysql-schema-on-the-cluster-needed)].                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `tmp`                                                      | 0      | Empty.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `information_schema`, `mysql`, `performance_schema`, `sys` |        | MySQL's own schemas.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |

## Areas

Each area is a tbls viewpoint in [tbls.yml](https://github.com/snapfix/docs/blob/main/spec/db/tbls.yml), with its own page and diagram. The product areas follow [notes/inventory/areas.md](https://github.com/snapfix/docs/blob/main/notes/inventory/areas.md); four more group tables across areas. A table can sit in more than one area.

| Area                                                                                         | What it holds                                                                                                                        | Main tables                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| -------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [Account](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-0.md)           | Signing in, passwords, verification codes, single sign-on, devices and sign-in tokens, consent logs, and the links that open the app | [`tPri_UserHashSalt`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userhashsalt.md), [`tPri_Device`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_device.md), [`token_jwt`](https://docs.snapfix.com/engineering-reference/db/tables/token_jwt.md), [`credential_code`](https://docs.snapfix.com/engineering-reference/db/tables/credential_code.md), [`dynamiclink`](https://docs.snapfix.com/engineering-reference/db/tables/dynamiclink.md)               |
| [Onboarding](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-1.md)        | Setting up a new site, the pre-registration survey, invitations, joining a site                                                      | [`group_invite`](https://docs.snapfix.com/engineering-reference/db/tables/group_invite.md), [`dynamiclink_invitation`](https://docs.snapfix.com/engineering-reference/db/tables/dynamiclink_invitation.md), [`reg_industry_roles`](https://docs.snapfix.com/engineering-reference/db/tables/reg_industry_roles.md), [`tPri_Log_PreRegSurvey`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_preregsurvey.md)                                                               |
| [Tasks](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-2.md)             | Tasks, statuses and their history, assignees and followers, tags on tasks, time and cost, task links, the shared task page           | [`tPri_Job`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_job.md), [`tPri_Job_StatusLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_job_statuslog.md), [`tPri_JobUserAssigned`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobuserassigned.md), [`tPri_JobTag`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobtag.md), [`url_job_token`](https://docs.snapfix.com/engineering-reference/db/tables/url_job_token.md) |
| [Chat](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-3.md)              | Task chat, reactions, voice transcripts, chat item types                                                                             | [`tPri_JobChat`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobchat.md), [`tPri_JobChat_Reaction`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobchat_reaction.md)                                                                                                                                                                                                                                                                                       |
| [Planner](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-4.md)           | Planned and repeating tasks and their logs                                                                                           | [`schedule_v3`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_v3.md), [`schedule_log_v3`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_log_v3.md)                                                                                                                                                                                                                                                                                                     |
| [Checklists](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-5.md)        | Templates, libraries, folders and permissions, and checklists on tasks                                                               | [`tPri_CL`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_cl.md), [`tPri_CL_Item`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_cl_item.md), [`tPri_CL_SiteTemplate`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_cl_sitetemplate.md), [`tPri_JobTodo`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobtodo.md)                                                                                                           |
| [Groups](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-6.md)            | Groups and their settings, tag folders and tags, rules                                                                               | [`tPri_Business`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_business.md), [`tPri_SettingsBusiness`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_settingsbusiness.md), [`tPri_TagHeader`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_tagheader.md), [`tPri_Tag`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_tag.md)                                                                                                 |
| [Rooms](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-7.md)             | Buildings, areas and rooms, room statuses, guests and beds                                                                           | [`tPri_Building`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_building.md), [`tPri_Room`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_room.md), [`tPri_Area`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_area.md), [`tPri_RoomGuestStatus`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_roomgueststatus.md)                                                                                                           |
| [Housekeeping](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-8.md)      | Cleaning and inspection links, room-status task rules, working hours                                                                 | [`tPri_JobLink`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_joblink.md), [`tPri_RoomStatus_TaskRules`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_roomstatus_taskrules.md), [`tPri_User_WorkingHours`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_user_workinghours.md)                                                                                                                                                               |
| [Assets](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-9.md)            | Asset registers, categories, attributes and their rules, task–asset links                                                            | [`assetReg`](https://docs.snapfix.com/engineering-reference/db/tables/assetreg.md), [`tPri_JobAttribute`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobattribute.md), [`tPri_JobJobLink`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_jobjoblink.md)                                                                                                                                                                                                     |
| [Compliance](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-10.md)       | Snapfix Comply                                                                                                                       | [`comply_System`](https://docs.snapfix.com/engineering-reference/db/tables/comply_system.md), [`comply_Contractor`](https://docs.snapfix.com/engineering-reference/db/tables/comply_contractor.md), [`comply_Document`](https://docs.snapfix.com/engineering-reference/db/tables/comply_document.md)                                                                                                                                                                                           |
| [Reports](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-11.md)          | Dashboard views, report files, monthly statistics, wallboards                                                                        | [`report_userreports`](https://docs.snapfix.com/engineering-reference/db/tables/report_userreports.md), [`saved_dashboard_views`](https://docs.snapfix.com/engineering-reference/db/tables/saved_dashboard_views.md)                                                                                                                                                                                                                                                                           |
| [Console](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-12.md)          | Site details, addresses and settings, multi-sites, username logins                                                                   | [`tPri_Parent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_parent.md), [`tPri_SettingsParent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_settingsparent.md), [`tPri_MultiSite`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_multisite.md), [`staff_username`](https://docs.snapfix.com/engineering-reference/db/tables/staff_username.md)                                                                                             |
| [Billing](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-13.md)          | Packages, the billing organisation and its payment records, in-app purchases, support minutes                                        | [`package_server_parent`](https://docs.snapfix.com/engineering-reference/db/tables/package_server_parent.md), [`tPri_Org`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org.md), [`tPri_Org_Stripe`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org_stripe.md), [`stripe_subscription`](https://docs.snapfix.com/engineering-reference/db/tables/stripe_subscription.md)                                                                                   |
| [Integrations](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-14.md)     | PMS integrations, Deputy, Open API and vendor API keys                                                                               | [`opera_BuildingSiteMapping`](https://docs.snapfix.com/engineering-reference/db/tables/opera_buildingsitemapping.md), [`opera_InboundQueue`](https://docs.snapfix.com/engineering-reference/db/tables/opera_inboundqueue.md), [`tPri_Api_Simple_SecretKey`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_api_simple_secretkey.md)                                                                                                                                             |
| [Notifications](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-15.md)    | Push tokens, reminders, notification settings, email and SMS logs, in-app surveys, popups                                            | [`firebase_user_device_token`](https://docs.snapfix.com/engineering-reference/db/tables/firebase_user_device_token.md), [`tPri_UserNotifSettings`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_usernotifsettings.md), [`tbl_email_logs`](https://docs.snapfix.com/engineering-reference/db/tables/tbl_email_logs.md)                                                                                                                                                         |
| [QR](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-16.md)               | QR forms and their submissions                                                                                                       | [`qr_formdata`](https://docs.snapfix.com/engineering-reference/db/tables/qr_formdata.md), [`qr_formdata_log`](https://docs.snapfix.com/engineering-reference/db/tables/qr_formdata_log.md)                                                                                                                                                                                                                                                                                                     |
| [AI](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-17.md)               | Snapfix AI and AI suggestions                                                                                                        | [`ai_token_usage`](https://docs.snapfix.com/engineering-reference/db/tables/ai_token_usage.md), [`log_ai_job`](https://docs.snapfix.com/engineering-reference/db/tables/log_ai_job.md), [`ai_task_user_job_resolution`](https://docs.snapfix.com/engineering-reference/db/tables/ai_task_user_job_resolution.md)                                                                                                                                                                               |
| [Help](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-18.md)             | The Help Hub contact form and help links                                                                                             | [`tPri_Log_Helphub_ContactForm`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_helphub_contactform.md), [`tPub_Help`](https://docs.snapfix.com/engineering-reference/db/tables/tpub_help.md)                                                                                                                                                                                                                                                                               |
| [Staff](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-19.md)            | Staff tools: blocklists, HubSpot and Jira data, site history                                                                         | [`blacklist_email`](https://docs.snapfix.com/engineering-reference/db/tables/blacklist_email.md), [`hubspot_company`](https://docs.snapfix.com/engineering-reference/db/tables/hubspot_company.md), [`time_tPri_Parent`](https://docs.snapfix.com/engineering-reference/db/tables/time_tpri_parent.md)                                                                                                                                                                                         |
| [People and sites](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-20.md) | Organisations, sites, groups, accounts and memberships, across areas                                                                 | [`tPri_Parent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_parent.md), [`tPri_User`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_user.md), [`tPri_UserParent`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userparent.md), [`tPri_UserBusiness`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userbusiness.md)                                                                                                         |
| [Logs](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-21.md)             | Application and API logs, rate limits, queues, the cache                                                                             | [`tPri_Log`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log.md), [`tPri_Queue`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_queue.md), [`failed_jobs`](https://docs.snapfix.com/engineering-reference/db/tables/failed_jobs.md)                                                                                                                                                                                                                           |
| [Reference](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-22.md)        | Code lists, standard data, defaults the apps download                                                                                | [`enum_JobType`](https://docs.snapfix.com/engineering-reference/db/tables/enum_jobtype.md), [`std_countries`](https://docs.snapfix.com/engineering-reference/db/tables/std_countries.md), [`tPub_Status`](https://docs.snapfix.com/engineering-reference/db/tables/tpub_status.md)                                                                                                                                                                                                             |
| [Legacy](https://docs.snapfix.com/engineering-reference/db/tables/viewpoint-23.md)           | Tables nothing uses: see below                                                                                                       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |

## Legacy

A table counts as used when code in one of the code repos in [sync.yml](https://github.com/snapfix/docs/blob/main/sync.yml) reads or writes it (searched on 2026-11-16 by table name, with and without its prefix). A migration, a seeder or an unused model alone does not count. Whether these tables can be dropped is open \[[ASK-242](https://github.com/snapfix/docs/blob/main/ASK.md#ask-242-can-the-unused-copy-and-scratch-tables-be-dropped)].

**Unused** (no code; no rows, or a single test row):

* [`ai_token_budget`](https://docs.snapfix.com/engineering-reference/db/tables/ai_token_budget.md), [`assetUserReg`](https://docs.snapfix.com/engineering-reference/db/tables/assetuserreg.md), [`schedule_debug_log`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_debug_log.md), [`tag_vision`](https://docs.snapfix.com/engineering-reference/db/tables/tag_vision.md), [`tPri_Log_Binary`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_binary.md), [`tPri_Log_Job_SoftFreeze`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_job_softfreeze.md) and [`tPri_MultiSiteChecklistTemplate`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_multisitechecklisttemplate.md): empty, and no code names them.
* [`tPri_AppVersion`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_appversion.md): empty, and no code names it. Some of its columns are named for secrets (`server_private_key`, `universal_totp_key`, `universal_basic_auth`).
* [`comply_ContractorTag`](https://docs.snapfix.com/engineering-reference/db/tables/comply_contractortag.md) and [`comply_SystemTag`](https://docs.snapfix.com/engineering-reference/db/tables/comply_systemtag.md): empty; only a model and a migration name them.
* [`tag_vision_job`](https://docs.snapfix.com/engineering-reference/db/tables/tag_vision_job.md) and [`tPri_UserHubspotContact`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userhubspotcontact.md): empty; only a migration names them.
* [`flask_test`](https://docs.snapfix.com/engineering-reference/db/tables/flask_test.md) and [`tag`](https://docs.snapfix.com/engineering-reference/db/tables/tag.md): one row each; no code names them (the word `tag` appears only in unrelated code).

**Copies and scratch tables** (rows, no code): [`qr_formdata_copy_20250513`](https://docs.snapfix.com/engineering-reference/db/tables/qr_formdata_copy_20250513.md), [`qr_formdata_copy_cg`](https://docs.snapfix.com/engineering-reference/db/tables/qr_formdata_copy_cg.md), [`qr_formdata_ol1`](https://docs.snapfix.com/engineering-reference/db/tables/qr_formdata_ol1.md), [`temp_adrian_todo_data`](https://docs.snapfix.com/engineering-reference/db/tables/temp_adrian_todo_data.md) to [`temp_adrian_todo_data5`](https://docs.snapfix.com/engineering-reference/db/tables/temp_adrian_todo_data5.md), [`tPri_Parent_copy_2025-05-07`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_parent_copy_2025-05-07.md), [`tPri_UserParent_copy1`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_userparent_copy1.md) and [`unittest`](https://docs.snapfix.com/engineering-reference/db/tables/unittest.md).

**Old tables with rows and no code** (named at most by a migration or a seeder): [`enum_IntegrationSource`](https://docs.snapfix.com/engineering-reference/db/tables/enum_integrationsource.md), [`guestline_business_job`](https://docs.snapfix.com/engineering-reference/db/tables/guestline_business_job.md), [`jira_job`](https://docs.snapfix.com/engineering-reference/db/tables/jira_job.md), [`package_bundle_server`](https://docs.snapfix.com/engineering-reference/db/tables/package_bundle_server.md), [`schedule_bug_v2`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_bug_v2.md), [`schedule_nfc02`](https://docs.snapfix.com/engineering-reference/db/tables/schedule_nfc02.md), [`std_continents`](https://docs.snapfix.com/engineering-reference/db/tables/std_continents.md), [`std_naics_codes_2`](https://docs.snapfix.com/engineering-reference/db/tables/std_naics_codes_2.md) to [`std_naics_codes_6`](https://docs.snapfix.com/engineering-reference/db/tables/std_naics_codes_6.md), [`stripe_product_detail_retired`](https://docs.snapfix.com/engineering-reference/db/tables/stripe_product_detail_retired.md), [`tPub_ApiEndPoint_v7_LEGACY`](https://docs.snapfix.com/engineering-reference/db/tables/tpub_apiendpoint_v7_legacy.md) and [`user_blacklist`](https://docs.snapfix.com/engineering-reference/db/tables/user_blacklist.md) \[[ASK-242](https://github.com/snapfix/docs/blob/main/ASK.md#ask-242-can-the-unused-copy-and-scratch-tables-be-dropped)].

**Rows from a writer outside the repos.** These tables get new rows, but no in-scope code names them, so they stay in their areas: [`ai_agent_feedback`](https://docs.snapfix.com/engineering-reference/db/tables/ai_agent_feedback.md), [`ai_agent_messages`](https://docs.snapfix.com/engineering-reference/db/tables/ai_agent_messages.md) and [`ai_agent_sessions`](https://docs.snapfix.com/engineering-reference/db/tables/ai_agent_sessions.md) \[[ASK-179](https://github.com/snapfix/docs/blob/main/ASK.md#ask-179-which-database-does-snapfix-ai-use-in-production-and-are-the-ai_agent_-tables-still-used)]; [`ai_brand_documents`](https://docs.snapfix.com/engineering-reference/db/tables/ai_brand_documents.md) and [`ai_brand_document_permissions`](https://docs.snapfix.com/engineering-reference/db/tables/ai_brand_document_permissions.md) \[[ASK-180](https://github.com/snapfix/docs/blob/main/ASK.md#ask-180-which-service-indexes-and-serves-the-brand-standards-documents-and-does-it-check-the-callers-site-and-group)]; [`tPri_IAP_History`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_iap_history.md), [`tPri_Log_PreRegSurveyMetadata`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_log_preregsurveymetadata.md), [`tPri_Org_GoCardlessWebhookLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org_gocardlesswebhooklog.md), [`tPri_Org_HubspotDealLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org_hubspotdeallog.md), [`tPri_Org_StripeWebhookLog`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_org_stripewebhooklog.md) and [`tPri_RoomBed`](https://docs.snapfix.com/engineering-reference/db/tables/tpri_roombed.md) \[[ASK-243](https://github.com/snapfix/docs/blob/main/ASK.md#ask-243-which-service-writes-the-tables-that-no-in-scope-code-names)].

Other empty tables, such as the Comtrol tables, are named by code and stay in their areas. [`cache`](https://docs.snapfix.com/engineering-reference/db/tables/cache.md) is Laravel's database cache table; the API's default cache store is `file` (api\_php\_live\_v2 @ ef3c4319f6, `config/cache.php:18`), so the table fills only when `CACHE_DRIVER` is `database`.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.snapfix.com/engineering-reference/db.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
