---
name: verify-sqlite-schema
description: Use before writing any DataSource, BusinessObject, ListObject, or LookupObject for CG Mobile, OR when a field is missing locally / not syncing from Salesforce. Verifies a table/column exists in the local SQLite database (`appl/data/app.db3`). On local miss, investigates the connected Salesforce org — checks the SObject, locates the field, and determines whether it's in the `Mobility_relevant` field set. Can deploy a fix to the field set after explicit user approval.
---

# Verify SQLite Schema — CG Mobile Modeler

## When to use this skill

-   User asks: "is column X available in app.db3", "does this table exist locally", "why is this field missing", "missing field in my DS"
-   Auto-invoked by: `create-datasource`, `create-business-object`, `create-list-object`, `create-lookup-object`
-   NOT for: build errors (use `build-and-simulate`); fields you're certain exist (run the check yourself with `sqlite3`)

## Prerequisites

-   `appl/data/app.db3` exists
-   `sqlite3` CLI is on PATH (macOS ships it by default)
-   For Stage 2, `sf` CLI authenticated to a default org (`sf org display` succeeds)

## Mandatory input

Before running, know:

-   Table name (mobile-side, e.g., `Visit`)
-   Column name (mobile-side, e.g., `CustomerRating`)

## Three-stage execution

### Stage 1 — Local SQLite check

```bash
# cd to your workspace root
sqlite3 appl/data/app.db3 ".tables" | grep -x "<Table>" \
  && echo "TABLE_OK" || echo "TABLE_MISSING"

sqlite3 appl/data/app.db3 "PRAGMA table_info(<Table>);" \
  | cut -d'|' -f2 | grep -x "<Column>" \
  && echo "COLUMN_OK" || echo "COLUMN_MISSING"
```

Outcomes:

| Table   | Column  | Action                                           |
| ------- | ------- | ------------------------------------------------ |
| OK      | OK      | **STOP — success.** Report and return to caller. |
| OK      | MISSING | Proceed to Stage 2.                              |
| MISSING | —       | Proceed to Stage 2 with table-level search.      |

### Stage 2 — Salesforce org investigation

This stage runs only when Stage 1 reports a miss. See `references/sf-cli-commands.md` for exact commands.

**Step 2a — Confirm org connection**

```bash
sf org display --json 2>/dev/null | jq -r 'if .status == 0 then .result.username else "NOT_CONNECTED" end' \
  || echo "NOT_CONNECTED"
```

Alternative (lists all authenticated orgs if no default is set):

```bash
sf org list --json | jq -r '.result.nonScratchOrgs[]?.username, .result.scratchOrgs[]?.username' | head -5
```

If `NOT_CONNECTED`, stop and instruct the user to run `sf org login web`.

Always report the resolved org's `alias` AND `username` to the user and confirm before making any queries — there may be multiple authenticated orgs.

**Step 2b — Resolve the SObject name**

Apply the rules in `references/sobject-name-mapping.md` in order:

1. Exact: `<Table>`
2. With suffix: `<Table>__c`
3. With managed prefixes: `retailexecution__<Table>__c`, `cg__<Table>__c`
4. Fuzzy search via `sf sobject list`

Confirm the match with the user before proceeding (there may be more than one candidate).

**Step 2c — Check field existence on the SObject**

```bash
sf sobject describe --sobject <ApiName> --json \
  | jq -r '.result.fields[] | select(.name == "<FieldApiName>") | .name'
```

If empty: field doesn't exist. **Report to user and stop** — the field must be created in Core first.

**Step 2d — Check `Mobility_relevant` field-set membership**

```bash
sf sobject describe --sobject <ApiName> --json \
  | jq -r '.result.fieldSets[] | select(.name == "Mobility_relevant") | .fields[].name' \
  | grep -x "<FieldApiName>" \
  && echo "IN_FIELDSET" || echo "NOT_IN_FIELDSET"
```

### Stage 3 — Report and optional remediation

**Three possible states after Stage 2:**

#### State A — Field not on SObject

```
FIELD <FieldApiName> does not exist on <ApiName>.
Cannot proceed — the field must first be created in Salesforce Core.
Options:
  1. Ask the Core team to create the field
  2. Use a DerivedAttribute with a CASE expression if the value can be computed
  3. Skip this column in the current DS
```

**No remediation available.** Return to caller with `missing_in_org`.

#### State B — Field on SObject but NOT in `Mobility_relevant`

```
FIELD <FieldApiName> exists on <ApiName> but is NOT in the `Mobility_relevant` field set.
That is why it is not syncing to the device.

Proposed fix: add the field to the field set.
```

Show the proposed XML addition using `templates/fieldset-addition.xml.template`. **Ask the user for explicit approval before making any org changes.** Default to dry-run.

If approved:

1. Retrieve the current field set (see `references/sf-cli-commands.md` command #5)
2. Insert the new `<displayedFields>` block using the template
3. Show the full local XML diff to the user
4. Run a server-side validation with `--dry-run`:

    ```bash
    sf project deploy start --metadata "FieldSet:<SObject>.Mobility_relevant" --dry-run
    ```

    This validates the deploy against the org without committing changes. Exit non-zero means the org rejected the change — stop here and report the error.

5. If dry-run passes, ask the user for FINAL approval one more time, naming the org (`alias + username`) explicitly.
6. On final approval, deploy without `--dry-run`:

    ```bash
    sf project deploy start --metadata "FieldSet:<SObject>.Mobility_relevant"
    ```

7. Report success with the deployment ID
8. Suggest: "Run the mobile simulator sync, then re-run verify-sqlite-schema to confirm the column appears locally"

If declined: report the investigation findings and stop.

#### State C — Field in field set but missing locally

```
FIELD <FieldApiName> is in `Mobility_relevant` on <ApiName> but not in the local SQLite DB.
The org side is correctly configured. Trigger a resync on the device:
  - In the simulator: use the sync button
  - Or re-initialize the local DB if it is stale
```

**No remediation in this skill — resync is outside its scope.** Return `local_stale`.

## Mandatory safety rules

-   **Never modify app.db3 directly.** It's regenerated by sync.
-   **Never deploy to an org without explicit user approval.** Field-set changes are visible to all users of that org.
-   **Never assume the default org is correct.** Show the user which org you're about to modify (alias + username) and confirm.
-   Dry-run is the default. Every proposed deploy must first be validated with `sf project deploy start --dry-run` against the target org; only after dry-run passes does the skill ask for final approval.
-   For managed-package field sets, check `deployable` before attempting — some are read-only.

## Escape hatch

-   `references/fieldset-mechanics.md` — deep detail on how field sets drive sync
-   `references/sobject-name-mapping.md` — mapping rules
-   `references/sf-cli-commands.md` — raw commands for manual debugging
-   `ai-wiki/wiki/datasource.md` (if present) — full DS authoring context

## Return values (for skills that invoke this)

| Return                  | Meaning                                                          |
| ----------------------- | ---------------------------------------------------------------- |
| `ok`                    | Table and column exist locally. Safe to proceed.                 |
| `missing_in_org`        | Field doesn't exist on any matching SObject. Block DS authoring. |
| `needs_fieldset_update` | Field exists, not in field set. User can optionally remediate.   |
| `remediation_deployed`  | Field set was updated and deployed. Suggest resync.              |
| `local_stale`           | Field set is correct, local DB is stale. Suggest resync.         |
| `skill_declined`        | User declined remediation. Return findings.                      |