Flagsmith / Flagsmith/flagsmith

Tombstone traitless identities in ClickHouse left behind by earlier Segment Membership seeding runs

Open
#8,499 0 comments 0 reactions 0 assignees View on GitHub
api bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.