Skip to main content

Support Agent Account Consolidation

Break-glass runbook for retiring a duplicate or stale support-agent account on production. Used when the same human ended up with two prod accounts (different emails) and all their ticket references need to be consolidated onto the canonical account before the stale one is removed from the support team.

When to use this​

  • Two prod users for the same person, both marked isSupportAgent = true, both with open ticket references.
  • A support agent leaves and their ticket backlog must be handed to a specific replacement.
  • Any scenario where you need ticket.createdById changed — no GraphQL mutation exposes this field.

Overview​

assignTicket + assignTester cover the day-to-day fields. createdById is immutable via the public API — the only write path is a direct SQL UPDATE, which is the specific exception this runbook authorises.

Prerequisites​

  • Prod super-admin (rzeraat.tur@gmail.com as of 2026-04-17) — needed for adminRevokeSupportRole and for the SSM tunnel to prod RDS.
  • Fresh AWS SSO session: aws sso login --profile Administrator-842697652860.
  • session-manager-plugin installed (for the tunnel): brew install --cask session-manager-plugin.
  • psql on $PATH.
  • Canonical (NEW) account already has isSupportAgent = true. If not, call adminAssignSupportRole(userId: NEW_ID) first — otherwise assignTicket will fail with "User is not a support agent".

Step 1 — Resolve user IDs​

query($email: String!) {
  userByEmail(email: $email) { id email firstName lastName }
}

Run twice against https://api.aiqlick.com/graphql (with Authorization: Bearer <super-admin-token>) to capture OLD_ID and NEW_ID. Both are UUIDs.

Step 2 — Scan ticket references​

query($input: SupportTicketsInput!) {
  supportTickets(input: $input) {
    items {
      id ticketNumber
      assignedTo { id email }
      createdBy { id email }
      testerId
    }
  }
}

Variables: {"input": {"limit": 500, "sortBy": "LAST_ACTIVITY", "sortOrder": "DESC"}}.

Filter client-side:

  • assignedTo.id === OLD_ID → needs assignTicket.
  • testerId === OLD_ID → needs assignTester.
  • createdBy.id === OLD_ID → needs direct SQL (step 4).

Step 3 — Reassign assignee and tester (GraphQL)​

For each ticket matching assignedTo.id === OLD_ID:

mutation($t: ID!, $a: ID!) {
  assignTicket(ticketId: $t, agentId: $a) { id assignedTo { email } }
}

For each ticket matching testerId === OLD_ID:

mutation($t: ID!, $te: ID!) {
  assignTester(ticketId: $t, testerId: $te) { id testerId }
}

Both mutations are idempotent and require the NEW user to already be a support-agent.

Step 4 — Migrate createdById via direct SQL​

No GraphQL mutation exists for SupportTicket.createdById. This is the rare case where direct SQL against prod RDS is the only path. Wrap in a transaction, use RETURNING, and commit only if the row count matches expectations.

Open the SSM tunnel to prod RDS​

aws ssm start-session \
  --profile Administrator-842697652860 \
  --region eu-north-1 \
  --target i-0b9f31f3236c25b7f \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters '{"host":["aiqlick-postgres.cx86go6iuul4.eu-north-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["15434"]}'

Leave this running in a separate shell until the procedure is done.

Build the connection URL​

RAW_URL=$(aws secretsmanager get-secret-value --profile Administrator-842697652860 --region eu-north-1 \
  --secret-id aiqlick-backend/production --query 'SecretString' --output text | \
  python3 -c "import sys,json; print(json.load(sys.stdin)['DATABASE_URL'])")

DB_URL=$(python3 -c "
from urllib.parse import urlparse
u = urlparse('$RAW_URL')
print(f'{u.scheme}://{u.username}:{u.password}@localhost:15434{u.path}{\"?\" + u.query if u.query else \"\"}')")

Pre-check — confirm the rows exist​

psql "$DB_URL" -c "SELECT \"ticketNumber\", \"createdById\" FROM \"SupportTicket\"
  WHERE \"ticketNumber\" IN ('SUP-00019','SUP-00022','SUP-00018')
  ORDER BY \"ticketNumber\";"

The UPDATE (transaction with RETURNING)​

BEGIN;
UPDATE "SupportTicket"
SET "createdById" = '<NEW_ID>',
    "updatedAt"   = NOW()
WHERE "createdById" = '<OLD_ID>'
  AND "ticketNumber" IN ('SUP-00019','SUP-00022','SUP-00018')
RETURNING "ticketNumber", "createdById";
COMMIT;

If the RETURNING row count doesn't match the expected number, ROLLBACK instead of COMMIT. Always scope by explicit ticketNumber IN (...) so a mis-typed OLD_ID can't nuke unintended rows.

Post-check — verify via GraphQL​

Re-query supportTickets and confirm createdBy.email is the NEW email for every affected ticket. Never trust the SQL RETURNING alone — the GraphQL view is the user-facing truth.

Close the tunnel​

Stop the aws ssm start-session process once verification is done. Leaving tunnels open is not a security issue (SSM is IAM-gated) but it holds a port you'll want later.

Step 5 — Revoke the support-agent role​

mutation($u: ID!) {
  adminRevokeSupportRole(userId: $u) { success message }
}

Variables: {"u": "<OLD_ID>"}. Only call this after all three migration steps succeed — while the OLD user is still an agent, an accidentally-missed ticket can still be re-assigned to them by the UI.

Gotchas​

  • assignTicket side effect: auto-transitions OPEN → IN_PROGRESS. Usually desired for this kind of cleanup; override with updateTicketStatus afterwards if not.
  • sendSupportReply no longer changes status (since 2026-04-23). Agent replies only update lastActivityAt/firstResponseAt; they leave the ticket status where you set it. If you do need WAITING_ON_CUSTOMER during a consolidation, call updateTicketStatus explicitly.
  • Dev vs prod user IDs: UUIDs differ between environments. Always re-resolve via userByEmail against the target env — never copy-paste a dev ID into a prod mutation.
  • Do not promote dev-only accounts: The dev test account rezdev97@gmail.com exists on prod but must not be promoted to prod support-agent. When a dev-side assignee is rezdev97, route to rzeraat.tur on prod as the equivalent identity.
  • The User record survives: This runbook drains the OLD account of ticket references but does not delete the User row. Jobs, companies, candidates, talents, messages, and other FK references owned by the OLD account are untouched. If the goal is a full account merge, that's a separate migration touching every @relation in the Prisma schema.

Example — 2026-04-17 Tania Riaz consolidation​

Consolidated tania.riaz@qualipro.se (OLD) → taniariazpk786@gmail.com (NEW):

FieldAffected ticketsPath used
assigneeSUP-00109, SUP-00060, SUP-00046assignTicket (GraphQL)
tester(none)—
createdBySUP-00019, SUP-00022, SUP-00018direct SQL UPDATE

Then adminRevokeSupportRole(userId: <OLD_ID>). Post-consolidation verification: adminListSupportAgents no longer returns the OLD account; a supportTickets scan returned zero tickets referencing tania.riaz@qualipro.se on any field.