# DataSync Tagging Conventions — `SOURCETYPE` and Soft-Delete (`ISDELETED`)

**Audience:** anyone registering a new table into the DataSync engine (`MDATASET`/`MDATASETDETAIL`/`DBOBJECT` — see `docs/Platform-Architecture-Integration-Reference.md` §5) — module owners defining a new dataset, and reviewers checking a new `DdlScript` that adds a syncable table.

This doc formalizes two conventions the engine's own code already assumes but has never stated as a checklist. Both matter for the same reason: **incremental sync must never destroy a tenant's own customization of a row that also happens to be centrally-seeded reference data.**

---

## 1. `SOURCETYPE` — required on every syncable table

Present on ~200+ tables platform-wide today (legacy-derived and GB5-native alike), with the canonical meaning first documented in `DB/Migrations/001_PIE_Initial_Schema.sql` and repeated consistently across 25+ other migrations:

```
SOURCETYPE values: 1=Framework  2=Devadmin  3=Impadmin  4=Admin  5=User
```

This is what lets `Overwrite`/`Skip`/`LastWriteWins` conflict policies (`ConflictResolver`, see the Architecture Reference §5.1) distinguish rows that originated from the central framework/devsys build (`SourceType=1`) from rows a specific tenant customized locally (`SourceType=5`, or 2-4 for various internal-admin tiers). Without this tag, an `Overwrite`-policy sync pushing central master data down to a client DB has no way to avoid clobbering a client's own edit to that same row.

**Rule: any table registered in `MDATASETDETAIL` must carry a `SOURCETYPE` column**, and the sync job's own filtering (or a future enhancement to `ConflictResolver`) should respect it — i.e. never overwrite a row whose `SOURCETYPE` marks it as tenant-originated when the sync's own dataset is only supposed to push framework-originated rows.

## 2. Soft-delete (`ISDELETED`) — the required convention for any table needing delete-propagation from a non-SQL-Server source

**The gap this formalizes**: `SyncEngine.ApplyDeletesAsync` (`GB5Framework/FrameworkBLL/Engine/SyncEngine.cs:96-102`) propagates hard deletes from a SQL Server source via CDC (`cdc.[dbo_{Table}_CT]`, reading `__$operation = 1` rows) — but for any other source dialect, it returns `0` immediately:

```csharp
if (sourceDbType != DatabaseType.SqlServer)
{
    // Non-SQL-Server: propagate ISDELETED=1 rows via the UPDATE flow.
    // Full hard-delete via PostgreSQL logical replication is Phase 4.
    return 0;
}
```

This comment names the intended workaround but nothing in the engine enforces or verifies it — it's stated intent, not a checked convention. **This section makes it one.**

### The rule

Any table that (a) needs delete-propagation through DataSync, and (b) might ever be synced from a PostgreSQL (or other non-SQL-Server) source, **must** carry a soft-delete flag column, conventionally named `ISDELETED` (`TINYINT`/`bit`, default `0`), and the table's `DELETE` operations (wherever the table is normally maintained) must be replaced with `UPDATE ... SET ISDELETED = 1, MODIFIEDON = ...` instead of a real `DELETE FROM ...`.

Why this works with the existing engine unmodified: `ISDELETED=1` rows are just ordinary rows from the sync engine's point of view — they flow through the normal watermark-filtered, `ModifiedDateColumn`-driven `Overwrite`/`Skip`/`LastWriteWins` UPDATE path (§5.1 of the Architecture Reference) like any other change. The target database ends up with the same `ISDELETED=1` row, and whatever normally reads that table (BLL queries, UI grids) is expected to filter `WHERE ISDELETED = 0` — the same convention CLAUDE.md already expects for soft-deletable tables elsewhere in the platform.

### What this does NOT solve

- A row soft-deleted this way is never physically removed from either database — if a table's syncable rows accumulate a meaningful volume of `ISDELETED=1` rows over time, that's a storage/archival concern for the table owner, not something DataSync manages.
- This convention only covers **new** deletes going forward. It does nothing for a table that has always used real `DELETE` and is only now being registered for sync from a Postgres source — that table needs a one-time migration to add `ISDELETED` and a decision about whether pre-existing deletes (which left no trace) can be reconciled at all (usually: no, they can't — the sync will simply never know about them).
- True hard-delete propagation from a Postgres source (via logical replication) remains explicitly out of scope ("Phase 4" per the engine's own comment) — this convention is the interim answer, not a promise that Phase 4 is imminent.

### Checklist for registering a new syncable table

- [ ] Table carries `SOURCETYPE` (§1).
- [ ] Table carries `CREATEDON`/`MODIFIEDON` columns matching whatever is configured as `MDATASETDETAIL.CreatedDateColumn`/`ModifiedDateColumn`.
- [ ] If the table's source might ever be PostgreSQL **and** deletes need to propagate: table carries `ISDELETED`, and all normal maintenance code paths for this table use soft-delete (`UPDATE ISDELETED=1`), not `DELETE FROM`.
- [ ] If the table's source is always SQL Server and hard-delete propagation is needed: confirm CDC is (or will be) enabled on the source table — `ApplyDeletesAsync` degrades gracefully (logs a warning, skips delete propagation for that table) if CDC isn't enabled, but that's a silent gap worth catching at registration time rather than discovering later.

---

See `docs/Platform-Architecture-Integration-Reference.md` §5 (Metadata/Data Replication) for the full engine architecture these conventions support, and §9.5 for the proposed-direction context this doc was written to close out.