Duplicate issue identifiers in same project (e.g., SERVE-25/UI-35) under degraded DB state; missing uniqueness enforcement

Summary

In a self-hosted Plane deployment, multiple distinct issues were created in the same project with the same human-readable identifier (<PROJECT_KEY>-<sequence_id>), e.g. SERVE-25 and UI-35 duplicated.

This appears to be possible because issues does not enforce uniqueness on (project_id, sequence_id), and sequence tracking (issue_sequences) can drift/miss rows.

Environment

• Plane: v1.2.1 (artifacts.plane.so/makeplane/plane-backend:v1.2.1)
• Postgres: postgres:15.7-alpine
• Self-hosted via Podman/systemd

Observed behavior

1. Distinct issues in the same project share the same sequence number.
2. API/UI show both with same plain ID (example: two SERVE-25, two UI-35).
3. In DB:
   • issues contains duplicates by (project_id, sequence_id).
   • some duplicate issues have no corresponding issue_sequences row, while newer duplicates do.

Concrete evidence (from DB)

public.issues duplicates:

• SERVE, sequence_id=25, 3 rows
• UI, sequence_id=35, 2 rows

Query used:

SELECT p.identifier, i.sequence_id, count(*) AS cnt
FROM public.issues i
JOIN public.projects p ON p.id=i.project_id
WHERE i.deleted_at IS NULL
GROUP BY p.identifier, i.sequence_id
HAVING count(*) > 1
ORDER BY cnt DESC, p.identifier, i.sequence_id;

Also, no unique constraint exists for sequence IDs:

SELECT conrelid::regclass AS table_name, conname, pg_get_constraintdef(c.oid) AS def
FROM pg_constraint c
WHERE contype='u' AND pg_get_constraintdef(c.oid) ILIKE '%sequence_id%';

Result: no rows.

Table definitions show:

• issues(sequence_id) indexed, but not unique per project.
• issue_sequences also lacks a unique (project_id, sequence) guard.

Degraded DB state seen during/around reproduction

During issue creation window, Postgres logs repeatedly showed:

• ERROR: xlog flush request ... is not satisfied
• ERROR: MultiXactId 399 has not been created yet -- apparent wraparound
• Prior PANIC/restart observed in same period.

Also saw historical Plane API error with corruption signal:

• psycopg.errors.DataCorrupted: missing chunk number 0 for toast value ...

I suspect degraded DB state may trigger/accelerate sequence drift, but the duplicate ID outcome is still possible because uniqueness is not enforced at DB level.

Expected behavior

<PROJECT_KEY>-<N> must be unique within a project, always.

Suggested fixes

1. Add DB uniqueness constraints:
   • UNIQUE(project_id, sequence_id) on issues.
   • UNIQUE(project_id, sequence) on issue_sequences.
2. Make sequence allocation atomic/transactional (and retry on conflict).
3. Add integrity checks/repair migration for existing drift:
   • detect duplicates in issues by (project_id, sequence_id).
   • detect issues missing issue_sequences mapping.
4. Add concurrent create tests to guarantee uniqueness under load/failure.

Why this matters

Ambiguous human IDs break references, automation, and manual workflows (SERVE-25 can point to multiple rows).

Start by inspecting the public.issues and issue_sequences schemas and the sequence-allocation entry point; use the duplicate-detection query in the report to understand the existing state. Done means project-scoped identifiers are protected, allocation handles concurrent creation, existing drift is addressed, and concurrency coverage is added.