---
apiVersion: data-spec/v1
id: report-scope-field-metadata-20260409
description: "Register Report.Scope field in _System_Field so Show Me scope can be persisted"
created: 2026-04-09
applied: 2026-04-09
scope: production-only
status: applied
applied_to:
  - local (127.0.0.1:4000/nexuscrm)
  - tidb-branch xiaopeng-dev-test (bran-v3dchq6be5fe3nlpwo7mv6akem)
tables: [_System_Field]
depends_on: []
---

# Report.Scope Field Metadata — Data Spec

## Summary

The Report table already has a physical `Scope VARCHAR(50)` column (present in
SF-synced data with legacy values like `organization`, `user`, `team`). But the
metadata registry `_System_Field` has no entry for `Report.Scope`, so any write
through the normal persistence layer fails validation with:

> validation error on field 'Scope': Field 'Scope' does not exist on object 'Report'.

This blocks the new "Show me" scope feature (PR wiring the `mine`/`myTeam`/`all`
selector through to the backend `RunReport` path — the frontend saves
`reportMetadata.scope`, the REST handler sets `updates["Scope"]`, and the
persistence validator rejects it).

This spec registers the missing field metadata. The `system_tables.json`
bootstrap also gains the column, so a fresh environment would pick it up on
first boot — but existing envs (including the `bran-v3dchq6be5fe3nlpwo7mv6akem`
TiDB branch used for staging) were bootstrapped before that JSON change and
need this one-time metadata insert.

Values stored in the column:
- New NexusCRM reports: `all` | `mine` | `myTeam`
- Legacy SF-synced reports: `organization` | `user` | `team` | `orgActive` | …
  (ignored by `applyShowMeScope` → behaves as `all`, so no regression)

## Changes

### 1. Insert Report.Scope into _System_Field (idempotent)

```sql
INSERT INTO _System_Field
  (Id, ObjectId, ApiName, Label, PhysicalName, Type, Required, IsUnique, IsSystem, IsNameField, Indexed)
SELECT
  'fld_report_scope_showme_v1',
  o.Id,
  'Scope',
  'Show Me Scope',
  'Scope',
  'string',
  0, 0, 0, 0, 0
FROM _System_Object o
WHERE o.ApiName = 'Report'
  AND NOT EXISTS (
    SELECT 1 FROM _System_Field f
    WHERE f.ObjectId = o.Id AND f.ApiName = 'Scope'
  );
```

**Idempotency**: The `NOT EXISTS` subquery guards against a duplicate insert if
the spec is re-applied. The deterministic primary-key value
`fld_report_scope_showme_v1` keeps the row identifiable across environments so
the rollback can find it unambiguously. If a field with `ApiName='Scope'`
already exists under the Report object (e.g. added via a future bootstrap run
with a different Id), this insert is skipped and the existing row is left
untouched — safe by design.

**Safety:** this insert only affects *metadata* for a column that already
exists in the physical `Report` table. No user data is read, written, or
deleted. No existing report rows are modified.

## Rollback

```sql
DELETE FROM _System_Field
WHERE Id = 'fld_report_scope_showme_v1'
  AND ApiName = 'Scope'
  AND ObjectId = (SELECT Id FROM _System_Object WHERE ApiName = 'Report');
```

Rolling back blocks the new Show Me scope feature (writes will start failing
validation again) but will not affect existing report data or the legacy SF
scope values already in the `Report.Scope` column.

## Verification

After apply, confirm the row exists:

```sql
SELECT f.Id, f.ApiName, f.PhysicalName, f.Type
FROM _System_Field f
JOIN _System_Object o ON f.ObjectId = o.Id
WHERE o.ApiName = 'Report' AND f.ApiName = 'Scope';
```

Expected: one row with `Id=fld_report_scope_showme_v1`, `PhysicalName=Scope`,
`Type=string`.

Then save a report with a scope selection through the UI and confirm the
`PATCH /services/data/v63.0/analytics/reports/:id` returns 200 and the DB
`Report.Scope` column reflects the new value (`all`/`mine`/`myTeam`).