---
name: create-datasource
description: >
    Use when creating or modifying .datasource.xml files for the CG Mobile modeler contracts.
    Trigger phrases: "create a datasource", "DS for Visit", ".datasource.xml", "visit table binding",
    "declarative DS", "scripted DS", "context menu datasource", "DsBo", "DsLo", "DsLu".
    Invokes verify-sqlite-schema first for every table/column the DS references before writing
    the file. Prevents runtime crashes from wrong column names, missing tables, wrong external
    attribute, and broken save mapping.
---

# Create DataSource — CG Mobile Modeler

## When to Use This Skill

| Invoked by      | Phrase                                                                                     |
| --------------- | ------------------------------------------------------------------------------------------ |
| User directly   | "create a DataSource", "DS for X", ".datasource.xml", "visit table binding"                |
| Workflow skills | `add-cockpit-card`, `create-business-object`, `create-list-object`, `create-lookup-object` |

NOT for:

-   Generic SQL queries outside the modeler framework
-   Raw app.db3 inspection (use `verify-sqlite-schema` directly)
-   Build errors in an existing DS (use `build-and-simulate`)

---

## Prerequisites

**Invoke `verify-sqlite-schema` for every table/column this DS references BEFORE writing the file.
Stop if any column is missing and cannot be remediated.**

```bash
# Quick manual check (if you already know the table):
sqlite3 appl/data/app.db3 "PRAGMA table_info(Visit);"
```

If the skill returns `missing_in_org` for any column, do not write the DS. The column must be created
in Salesforce Core first. See `references/macros-and-params.md` for system macros that need no check.

---

## Mandatory Checklist

-   [ ] Verified all referenced tables and columns in `app.db3` via `verify-sqlite-schema`
-   [ ] Chose `external` value correctly — `false` for declarative (`<QueryCondition>`), `true` for scripted or empty (see failure mode 1)
-   [ ] DS name starts with `Ds<Kind>` where Kind ∈ {Bo, Lo, Lu}
-   [ ] File lives in `src/<Module>/DS/`
-   [ ] For sync-backed tables, filename ends `_sf.datasource.xml`
-   [ ] All `<OrderCriterion>` blocks have `entity`, `attribute`, and `direction` attributes

---

## Failure Modes

### Fm1 — Wrong `external` attribute (runtime crash)

**Symptom:** `Cannot read properties of undefined (reading 'Load')`
**Cause:** Declarative DS (`<QueryCondition>`) has `external="true"`, so the framework looks for a
`<Database><Load>` block, finds nothing, and crashes.
**Fix:** If your DS has `<QueryCondition>` → `external="false"`. If it has `<Database><Load>` → `external="true"`.
**Note:** This is a runtime crash, not a build error. It only surfaces when the screen is loaded.

### Fm2 — Missing column in SQLite (runtime crash)

**Symptom:** `SQLITE_ERROR: no such column: ColumnName`
**Cause:** A `<Attribute column="...">` references a column not present in `appl/data/app.db3`.
**Fix:** Run `verify-sqlite-schema` with the table and column name. Either the field is not synced
(add to `Mobility_relevant` field set) or the spec is wrong.

### Fm3 — `OrderCriterion` missing `direction` (silent wrong ordering)

**Symptom:** List items arrive in unpredictable order; no build error shown.
**Cause:** `<OrderCriterion>` is missing the `direction` attribute or uses `sortOrder` (wrong name).
**Fix:** Always write all three: `entity="..."`, `attribute="..."`, `direction="ASC|DESC"`.

```xml
<!-- CORRECT -->
<OrderCriterion entity="Visit" attribute="PlannedVisitStartTime" direction="ASC" />

<!-- WRONG — no build error, but silent failure -->
<OrderCriterion attribute="PlannedVisitStartTime" sortOrder="ASC" />
```

### Fm4 — `editableEntity=""` on a DS that needs saves (saves silently fail)

**Symptom:** Save succeeds in the UI (no error) but the database row is never updated.
**Cause:** `editableEntity=""` tells the framework this DS is read-only; saves are silently no-oped.
**Fix:** Set `editableEntity="<EntityName>"` matching one of the `<Entity name="...">` declarations.

### Fm5 — Wrong `schemaVersion` (attribute type rejection)

**Symptom:** Build warning or newer attribute types behave unexpectedly.
**Cause:** `schemaVersion="1.x"` was copied from an old DS. Newer attribute types (e.g. `<DerivedAttribute>`) may not be fully supported below `schemaVersion="2.0"`.
**Fix:** Always use `schemaVersion="2.0"` for new files.

### Fm6 — Scripted `<Load>` executes the SQL itself (build passes, runtime crash)

**Symptom:** Build is green. At runtime, the first tab-click or list-load logs:

```
00000351 action loadLoXxx failed
Bo<Name>.loadLoXxxAsync Error: Database is not defined
```

**Cause:** The `<Load>` script ends with something like `return Database.loadRecordsAsync(sqlStmt, sqlParams)` (or any other attempt to execute the query itself). The build doesn't exercise the script so the reference to the undefined `Database` global is never caught.

**Fix:** A scripted `<Load>` is a **query builder**, not a query runner. It must return one of:

-   `Utils.replaceMacrosParam(sqlStmt, sqlParams)` — the canonical form, used by every shipped scripted DS
-   A `{sql, params}` object (which is what `replaceMacrosParam` returns)
-   A plain SQL string (with `#paramName#` macros)

The framework takes the returned SQL and executes it against the local SQLite database.

Right (matches `DsLoContactPartner_sf`, `DsLoVisit_sf`, every other shipped scripted DS):

```javascript
return Utils.replaceMacrosParam(sqlStmt, sqlParams);
```

Wrong — these all compile cleanly, all crash at runtime:

```javascript
return Database.loadRecordsAsync(sqlStmt, sqlParams); // "Database is not defined"
return runQuery(sqlStmt, sqlParams); // no such helper
return db.exec(sqlStmt); // no such global
```

See `ai-wiki/wiki/datasource.md` § "Build-critical: `<Load>` must RETURN the SQL, not execute it" for the full treatment.

---

## Minimal Template (inline preview)

Full template is at `templates/declarative.datasource.xml.template`. First 12 lines for at-a-glance:

```xml
<DataSource name="DsBo<ENTITY>" backendSystem="sf" businessObjectClass="Bo<ENTITY>"
            external="false" editableEntity="<ENTITY>" schemaVersion="2.0">
  <Attributes>
    <Attribute name="pKey" table="<ENTITY>" column="Id" />
    <Attribute name="name" table="<ENTITY>" column="Name" />
  </Attributes>
  <Entities>
    <Entity name="<ENTITY>" alias="" idAttribute="Id" />
  </Entities>
  <QueryCondition><![CDATA[
    <ENTITY>.Id = #pKey#
  ]]></QueryCondition>
```

---

## Common Variants

### Variant 1 — Declarative DS for a BO (`DsBoVisit`)

Single-record lookup: load one Visit by pKey, joined to User (inner join).

```xml
<DataSource name="DsBoVisit" backendSystem="sf" businessObjectClass="BoVisit"
            external="false" editableEntity="Visit" schemaVersion="2.0">
  <Attributes>
    <Attribute name="pKey"   table="Visit" column="Id" />
    <Attribute name="name"   table="Visit" column="Name" />
    <Attribute name="status" table="Visit" column="Status" />
    <Attribute name="visitor" table="User" column="Name" />
    <DerivedAttribute name="duration" value="'90'" />
  </Attributes>
  <Entities>
    <Entity name="Visit" alias="" idAttribute="Id" />
    <Entity name="User" alias="">
      <Join Type="inner">
        <SimpleJoin>
          <Condition leftSideValue="Visit.VisitorId" comparator="eq"
                     rightSideType="Attribute" rightSideValue="User.Id" />
        </SimpleJoin>
      </Join>
    </Entity>
  </Entities>
  <QueryCondition><![CDATA[ Visit.Id = #pKey# ]]></QueryCondition>
  <OrderCriteria />
  <Parameters>
    <Parameter name="pKey" type="TEXT" />
  </Parameters>
</DataSource>
```

Real file: `src/Visit/DS/DsBoVisit_sf.datasource.xml`

---

### Variant 2 — Declarative DS for an LO (multi-entity join with ordering)

List of Visits joined to RetailStore — declarative version (straightforward WHERE, no runtime branching).

```xml
<DataSource name="DsLoOrdersByCustomer" backendSystem="sf"
            businessObjectClass="LoOrdersByCustomer"
            external="false" editableEntity="" schemaVersion="2.0">
  <Attributes>
    <Attribute name="pKey"         table="Order" column="Id" />
    <Attribute name="orderDate"    table="Order" column="OrderDate" />
    <Attribute name="customerName" table="Account" column="Name" />
    <DerivedAttribute name="statusImage" value="'OrderStatus_' || Order.Status" />
  </Attributes>
  <Entities>
    <Entity name="Order" alias="" idAttribute="Id" />
    <Entity name="Account" alias="">
      <Join Type="left">
        <SimpleJoin>
          <Condition leftSideValue="Order.AccountId" comparator="eq"
                     rightSideType="Attribute" rightSideValue="Account.Id" />
        </SimpleJoin>
      </Join>
    </Entity>
  </Entities>
  <QueryCondition><![CDATA[
    Order.OwnerId = '#UserPKey#'
    AND Order.IsDeleted = '0'
    #cond#
  ]]></QueryCondition>
  <OrderCriteria>
    <OrderCriterion entity="Order" attribute="OrderDate" direction="DESC" />
  </OrderCriteria>
  <Parameters>
    <Parameter name="cond" treatAs="sqlSnippet" />
  </Parameters>
</DataSource>
```

Real-file structural anchor: `src/Visit/DS/DsLoVisit_sf.datasource.xml`
(note: this file uses `external="true"` with a scripted Load because it has dynamic date-range logic)

---

### Variant 3 — Context-menu / empty DS

BL-populated list — no DB query. The LO gets items pushed in by `.bl.js` code.

```xml
<DataSource name="DsLoVisitOverviewContextMenu" backendSystem="sf"
            businessObjectClass="LoVisitOverviewContextMenu"
            external="true" editableEntity="" schemaVersion="2.0">
  <Database>
    <Load><![CDATA[
      // Intended to be empty - This list object gets its items by business logic!
      return undefined;
    ]]></Load>
    <Update><![CDATA[ return undefined; ]]></Update>
    <Insert><![CDATA[ return undefined; ]]></Insert>
    <Delete><![CDATA[ return undefined; ]]></Delete>
  </Database>
</DataSource>
```

Real file: `src/Visit/DS/DsLoVisitOverviewContextMenu_sf.datasource.xml`

---

### Variant 4 — Scripted DS with dynamic date-range and conditional status filter

For queries that need runtime branching before SQL is assembled.

```xml
<DataSource name="DsLoProductsByDate" backendSystem="sf"
            businessObjectClass="LoProductsByDate"
            external="true" editableEntity="" schemaVersion="2.0">
  <Attributes>
    <Attribute name="pKey" table="Product" column="Id" />
    <Attribute name="name" table="Product" column="Name" />
  </Attributes>
  <Entities>
    <Entity name="Product" alias="" idAttribute="Id" />
  </Entities>
  <Database platform="SQLite">
    <Load><![CDATA[
      var currentDate = Utils.isDefined(jsonQuery.cardDate)
        ? Utils.convertForDBParam(jsonQuery.cardDate, "DomDate")
        : Utils.convertForDBParam(Utils.convertDate2Ansi(Utils.createDateToday()), "DomDate");
      var sqlParams = {currentDate};
      var sql = "SELECT Product.Id AS pKey, Product.Name AS name ";
      sql    += "FROM Product ";
      sql    += "WHERE Product.OwnerId = '#UserPKey#' ";
      sql    += "AND #compareAsDate('Product.ValidFrom', 'DateTime','<=',#currentDate#, 'Date')# ";
      sql    += "ORDER BY Product.Name ASC ";
      return Utils.replaceMacrosParam(sql, sqlParams);
    ]]></Load>
    <Update><![CDATA[ return undefined; ]]></Update>
    <Insert><![CDATA[ return undefined; ]]></Insert>
    <Delete><![CDATA[ return undefined; ]]></Delete>
  </Database>
</DataSource>
```

Real-file anchor: `src/Visit/DS/DsLoVisitsByDate_sf.datasource.xml`
(same pattern: `cardDate` param → converted date → `#compareAsDate#` helper in WHERE)

---

## Decision Table

| User wants                                    | Use template                            | `external` |
| --------------------------------------------- | --------------------------------------- | ---------- |
| A single-record BO lookup                     | `declarative.datasource.xml.template`   | `false`    |
| A list / grid backed by DB (simple filter)    | `declarative.datasource.xml.template`   | `false`    |
| A list with dynamic WHERE or date-range logic | `scripted-load.datasource.xml.template` | `true`     |
| A context menu populated by BL code           | `context-menu.datasource.xml.template`  | `true`     |
| Computed rows from a UNION or procedure       | `scripted-load.datasource.xml.template` | `true`     |

---

## Escape Hatch

When the above is not enough, consult in order:

1. `references/declarative-vs-scripted.md` — `external` attribute decision, symptom→cause→fix table
2. `references/macros-and-params.md` — `<<CURRENTUSERID>>` / `#UserPKey#`, `#Today#`, param passing, `treatAs="sqlSnippet"`, helper functions
3. `references/common-patterns.md` — joins, `DerivedAttribute`, `OrderCriterion`, save mapping, `QuickSearch`
4. `ai-wiki/wiki/datasource.md` (if available) — full 525-line wiki page with validation rules

---

## Verify

After writing the DS, run a build to confirm no structural errors:

```bash
# cd to your workspace root
sf mdl build 2>&1 | tail -20
```

If the build passes, load the owning screen in the simulator and watch the browser devtools console
for `SQLITE_ERROR` at runtime:

```bash
sf mdl simulate
# Open devtools → Console tab → look for SQLITE_ERROR or "Cannot read properties of undefined"
```

A clean build + no devtools errors confirms the DS is valid.