Flagsmith / Flagsmith/flagsmith
Tombstone traitless identities in ClickHouse left behind by earlier Segment Membership seeding runs
- Dominant language
- Python
- Stars
- 6.6k
- Forks
- 567
- Avg merge
- 1d 13h
- Merged PRs (30d)
- 121
Description
Organisations seeded before [#8455](https://github.com/Flagsmith/flagsmith/pull/8455) have rows in ClickHouse `IDENTITIES` for identities carrying no traits. Those rows are live (`is_deleted = false`) and nothing will ever supersede them:
- The seed is one-shot per organisation, and it now skips traitless identities, so neither `reconcile_segment_membership_seeds` nor the admin "Force re-seed" action writes a superseding row. An identity already deleted from DynamoDB can't be tombstoned from a scan at all.
- [edge-api#716](https://github.com/Flagsmith/edge-api/pull/716) drops stream records whose old and new images are both traitless, so the `REMOVE` that would tombstone such a row never reaches ClickHouse.
The effect is ghost members: they inflate `SegmentMembershipCount` and appear in the members list for any segment a traitless identity can satisfy (percentage split, conditions on `$.identity.key`/identifier, `is_not_set`). Deleting one from the dashboard queues a recount — `EdgeIdentity.delete` — that will never see the number move. If the empty-identity deletion script ([edge-api#692](https://github.com/Flagsmith/edge-api/pull/692)) runs against a seeded environment, the ghosts arrive by the million.
New occurrences are prevented by the tombstone-on-going-traitless change in edge-api#716, so this is a one-off repair of what is already stored.
## Suggested approach
Size it first:
```sql
SELECT environment_id, count() AS traitless
FROM IDENTITIES FINAL
WHERE is_deleted = false AND empty(JSONAllPaths(traits))
GROUP BY environment_id
ORDER BY traitless DESC
```
Then insert tombstones over the offending rows:
```sql
INSERT INTO IDENTITIES
(environment_id, identifier, identity_key, traits, inserted_at, source, is_deleted)
SELECT environment_id, identifier, identity_key, traits,
inserted_at + INTERVAL 1 SECOND, 'cleanup', true
FROM IDENTITIES FINAL
WHERE is_deleted = false AND empty(JSONAllPaths(traits))
```
Two things to preserve:
- **Insert tombstones, don't delete rows.** An `ALTER TABLE … DELETE` that removes a traitless row which supersedes an older row resurrects the stale traits underneath it.
- **Stamp `inserted_at` from the source row, not `now()`.** CDC rows carry the DynamoDB event time and can land minutes late, so a `now()` stamp can beat a legitimate in-flight write. Deriving from the row being superseded means any later CDC event still wins. Note `inserted_at` is `DateTime`, i.e. second granularity.
`traits` is a non-nullable `JSON` column: a NULL insert lands as `{}`, so `empty(JSONAllPaths(traits))` is the emptiness test (verified on 25.12, the version we run).
Worth deciding whether this ships as a `clickhouse` migration (runs once per installation, including self-hosted) or a management command we run by hand against the seeded organisations — the affected set is `SegmentMembershipSeed.objects.filter(seeded_at__isnull=False)`, which is small.
## Optional follow-up
Adding `AND notEmpty(JSONAllPaths(i.traits))` to the count and members queries in `segment_membership/services.py` would enforce "only identities with at least one trait are members" at read time, independently of what the table holds. It doesn't reclaim the storage, and in the members query it would inherit that path's existing compromise of filtering before `LIMIT 1 BY`.
Contributor guide
Research direction
Start by running the sizing query against ClickHouse and inspect the IDENTITIES schema and existing SegmentMembershipSeed handling. Decide whether this belongs in a ClickHouse migration or a management command, then verify the affected seeded organisations. Done means existing live traitless rows are superseded by correctly timestamped cleanup tombstones without deleting historical rows.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clickhouse, python, sql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100