Skip to content

Support dashboard: sample-ticket audit and controlled cleanup

Status (06 Nov 2026): audit prepared, nothing deleted. The owner asked for the "sample tickets" on the Support dashboard to be removed. The repository cannot prove which tickets are samples, so no cleanup has been run or coded.

What the code and schema say

  • Tickets live in tickets (090), with ticket_replies, ticket_internal_notes, ticket_events and support_email_outbox all ON DELETE CASCADE from tickets(id). There are no attachment tables.
  • tickets has no provenance marker: no is_test / is_sample / seed_key column, and channel only says app / web / email / phone / admin. No migration or seed inserts a ticket (090's seeds are topics, FAQs, resolution codes, settings and templates only). Test fixtures create tickets only inside Go tests, against a CI database.
  • There is no soft-delete / archive column, so "archive" would need a schema change, and a hidden ticket would still count in exports and SLA reports. Hard deletion is the only mechanism that makes a record disappear.

What the owner's screenshot shows (06 Nov 2026, 16 tickets, ids #2 - #17)

All 16 tickets belong to real user accounts: most to a single customer account whose name is the same as the signed-in support agent's, two to an account named "Ghost" and one to an account named "Karthick". Their topics follow the app's real ticket flow, and their timestamps (21 - 31 Oct 2026) are spread over days, with replies and assignments on some of them. That is consistent with staff testing the desk through real accounts, which is not the same as seeded demo data, and some may be genuine. Subject, date, status and appearance are not accepted as proof.

Verdict: sample identity is not established. No ticket may be deleted on this evidence.

Dry run (read-only) - run on the production host

One self-contained block, read-only. Paste into root@VPS:

docker exec -i aim_postgres psql -U "$(docker exec aim_postgres printenv POSTGRES_USER)" -d "$(docker exec aim_postgres printenv POSTGRES_DB)" <<'SQL'
BEGIN READ ONLY;
\echo '== totals'
SELECT count(*) AS tickets_total FROM tickets;
\echo '== every ticket with its provenance (no message text)'
SELECT t.ticket_number, t.id, t.status, t.channel, t.created_at, t.updated_at,
       t.created_by_admin IS NOT NULL AS created_by_staff, t.client_request_id IS NOT NULL AS has_client_request_id,
       t.order_id IS NOT NULL AS has_order,
       u.id AS user_id, u.display_name, left(u.firebase_uid, 8) AS uid_prefix,
       (u.phone IS NOT NULL) AS has_phone,
       (SELECT count(*) FROM ticket_replies r WHERE r.ticket_id = t.id) AS replies,
       (SELECT count(*) FROM ticket_replies r WHERE r.ticket_id = t.id AND r.user_id <> t.user_id AND NOT r.is_admin) AS replies_by_other_customers,
       (SELECT count(*) FROM ticket_internal_notes n WHERE n.ticket_id = t.id) AS notes,
       (SELECT count(*) FROM ticket_events e WHERE e.ticket_id = t.id) AS events,
       (SELECT count(*) FROM support_email_outbox o WHERE o.ticket_id = t.id) AS outbox_rows,
       (SELECT count(*) FROM support_email_outbox o WHERE o.ticket_id = t.id AND o.status = 'sent') AS emails_actually_sent
  FROM tickets t JOIN users u ON u.id = t.user_id
 ORDER BY t.ticket_number;
\echo '== accounts that own tickets: are they real customers? (orders, wallet, role)'
SELECT u.id, u.display_name, u.created_at,
       (SELECT count(*) FROM orders o WHERE o.user_id = u.id) AS orders,
       u.is_admin, u.is_support, count(t.id) AS tickets
  FROM users u JOIN tickets t ON t.user_id = u.id GROUP BY u.id ORDER BY tickets DESC;
ROLLBACK;
SQL

Evidence required before ANY deletion (all must hold, per ticket)

  1. The owner names the exact ticket numbers / ids from the output above as samples (written confirmation).
  2. The owning account is a test account the owner confirms (not a customer): no real orders or wallet history, or the owner states it is an internal account.
  3. emails_actually_sent shows no customer was actually written to, or the owner accepts that.
  4. No reply on the ticket comes from a different genuine customer.

Anything that fails a check is excluded and reported. Unknown provenance is excluded by default.

Proposed removal method (only after the owner's written confirmation of the exact ids)

Hard delete, in one transaction, by explicit id list only (never by subject, status, date or LIKE):

  1. Backup first: pg_dump of the affected rows (COPY (SELECT ... WHERE id = ANY(...)) TO for tickets, ticket_replies, ticket_internal_notes, ticket_events, support_email_outbox) to a file outside the database host's data volume, plus the nightly full backup's timestamp. Verify the file's row counts equal the live counts.
  2. BEGIN; DELETE FROM tickets WHERE id = ANY('{<exact ids>}'::uuid[]); - the four child tables cascade. Check the deleted row counts against the dry run, then COMMIT (or ROLLBACK on any difference).
  3. Write an ADMIN audit note (who confirmed, ids, counts, backup file) in the owner's change log.
  4. Verify: dashboard total, tabs, search and pagination; a genuine ticket opens with its messages; a new ticket can be created; replies by a support agent still work.

Recovery: restore the dumped rows with COPY ... FROM in the reverse order (tickets, then children). Ticket numbers come from a SERIAL; restoring keeps the original ticket_number because it is part of the dumped row.

Not done, on purpose

No deletion code, no scheduled job, no UI "delete" was added: without a trustworthy marker any automatic rule would risk genuine tickets. If the owner wants samples to be identifiable in future, add an explicit is_test flag set only by a reviewed admin action - a separate decision.