fabric-app-sqldb-writeback

Fabric App · SQL DB Write-back

The architecture for Fabric Apps that write: the app owns its SQL Database (a managed child of the App item), a small job mirrors Lakehouse Gold into it, and write-back entities live alongside the mirrors. One database, one SDK, instant read-your-own-writes.

Lakehouse (canonical, untouched)
      ↓ copy job (~30s, headless via SP over TDS)
App SQL Database
   ├─ mirror entities (DimX, FactY...)      ← refreshed by re-running the job
   └─ write-back entities (Comment, Edit…)  ← transactional, instant
      ↓ ONE SDK (RayfinClient)
SPA — every read and write

Why not write to the Lakehouse directly (the scars)

1. Its SQL endpoint is read-only — the GraphQL over it has no mutations.
2. Its sync is lazy — a write landed in OneLake in seconds and surfaced through the API a day later. Users will not wait a day to see their own comment.
3. Two sources = two auth flows + dual-source merge logic + optimistic-cache band-aids in the frontend, forever.

The copy job moves that complexity out of the frontend into one script. Microsoft's name for this serving pattern: operational data store.

Step 1 · Model entities (TypeScript decorators)

Mirror entities for the Gold tables the app reads + the app's own write-back entities. Keep mirror columns text-friendly (no enum constraints — source data will surprise you). Register everything in the schema the client SDK is typed against.

// rayfin/data/DimCustomer.ts — a mirror entity, minimal shape
@entity()
export class DimCustomer {
  @field({ type: "uuid", primaryKey: true }) id!: string;
  @field({ type: "text", unique: true }) customer_id!: string;
  @field({ type: "text" }) customer_name!: string;
  @field({ type: "text", optional: true }) segment?: string;
}
// register it in rayfin/data/schema.ts so the client SDK is typed

Schema apply is OWNER-ONLY: npx rayfin up db apply needs one interactive login by the item owner. The SP cannot apply schema (403 "Only AppBackend artifact owner") — but handles everything else headless.