Find the Departures Your Offboarding Ticket Report Misses with SQLite

A dashboard can report that every offboarding ticket is closed while a departure never entered the ticket system. Improving the query over tickets alone will not reveal that person: the missing row is outside the query's starting population.

Here is a small, runnable example that starts with a separate departure list and reconciles it with ticket and account records. All identifiers and circumstances below are fictional. The program creates an in-memory demonstration database; it does not connect to an employer's systems or change access permissions.

Start with the population you want to check

The demonstration has three departures:

• u01: a closed ticket and an account recorded as disabled.
• u02: an account recorded as enabled, with no ticket.
• u03: a closed ticket, but its account is still recorded as enabled.

Counting closed tickets would give us two closed tickets out of two. It would miss u02 and would not challenge the completion claim for u03.

The reconciliation produces independent flags instead of collapsing every problem into one status. A record can have both missing ticket evidence and an enabled account.

Run the example

Save this as reconcile.py and run it with Python 3 and its sqlite3 module. No additional Python packages are required in an installation that includes that module.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE departures (
    person_id TEXT PRIMARY KEY
);
CREATE TABLE tickets (
    ticket_id TEXT PRIMARY KEY,
    person_id TEXT NOT NULL,
    status TEXT NOT NULL
);
CREATE TABLE accounts (
    account_id TEXT PRIMARY KEY,
    person_id TEXT NOT NULL,
    enabled INTEGER NOT NULL CHECK (enabled IN (0, 1))
);

INSERT INTO departures VALUES ('u01'), ('u02'), ('u03');
INSERT INTO tickets VALUES
    ('t01', 'u01', 'closed'),
    ('t03', 'u03', 'closed');
INSERT INTO accounts VALUES
    ('a01', 'u01', 0),
    ('a02', 'u02', 1),
    ('a03', 'u03', 1);
""")

query = """
WITH ticket_summary AS (
    SELECT person_id,
           COUNT(*) AS ticket_count,
           SUM(CASE WHEN status <> 'closed' THEN 1 ELSE 0 END)
               AS open_ticket_count
    FROM tickets
    GROUP BY person_id
), account_summary AS (
    SELECT person_id,
           COUNT(*) AS account_count,
           SUM(enabled) AS enabled_account_count
    FROM accounts
    GROUP BY person_id
)
SELECT d.person_id,
       CASE WHEN t.person_id IS NULL THEN 1 ELSE 0 END
           AS missing_ticket,
       COALESCE(t.open_ticket_count, 0) AS open_tickets,
       CASE WHEN a.person_id IS NULL THEN 1 ELSE 0 END
           AS missing_account_evidence,
       COALESCE(a.enabled_account_count, 0) AS enabled_accounts
FROM departures AS d
LEFT JOIN ticket_summary AS t ON t.person_id = d.person_id
LEFT JOIN account_summary AS a ON a.person_id = d.person_id
ORDER BY d.person_id;
"""

rows = db.execute(query).fetchall()
print("person | missing_ticket | open_tickets | "
      "missing_account_evidence | enabled_accounts")
for row in rows:
    print(" | ".join(map(str, row)))

assert rows == [
    ("u01", 0, 0, 0, 0),
    ("u02", 1, 0, 0, 1),
    ("u03", 0, 0, 0, 1),
]
db.close()

The example was executed with these results:

person | missing_ticket | open_tickets | missing_account_evidence | enabled_accounts
u01 | 0 | 0 | 0 | 0
u02 | 1 | 0 | 0 | 1
u03 | 0 | 0 | 0 | 1

The synthetic u02 record now appears because the query starts from departures and uses LEFT JOIN. The closed ticket for u03 does not hide its enabled-account flag.

Why aggregate before joining?

One person can have multiple tickets and multiple accounts. Joining the raw tables can multiply rows: two tickets and three accounts can produce six combinations for that person. Summing enabled-account flags after that join can inflate the count.

The two common table expressions aggregate each source by person first. The outer query then joins one summary row from each source to each departure. This prevents that particular multiplication problem; it does not establish that the source records are correct.

There is a related trap with missing values. COALESCE makes the count readable, but a zero must not silently become evidence that no account exists. That is why the query also retains missing_account_evidence. Without it, an absent account row could look identical to a confirmed disabled account.

Read the output as a review queue

For this fictional snapshot, u02 needs ticket reconciliation and investigation of the enabled account. u03 needs investigation of the difference between the ticket claim and the account state. u01 has no flags from these particular checks; that is a bounded observation, not proof that all its access was removed correctly.

Before applying a similar query to an authorised environment, define:

• The extraction time and whether all sources describe the same moment.
• The effective departure time and the organisation's actual removal deadline.
• Stable identifiers across HR, contractor, ticket and account systems.
• The applications, privileges and identities included or excluded.
• An owner and follow-up deadline for each unresolved flag.

This demo does not evaluate removal timeliness, reactivation, active sessions, tokens, shared credentials, legal retention, or account matching quality. It also assumes the departure list is complete. A reconciliation can inherit the blind spots of that list, and two extracts can share the same upstream error.

The useful change is small: a ticket report asks how recorded work was handled. A population-led reconciliation also asks which expected work has no matching record. Keep both questions visible before turning the dashboard green.

References

• SQLite SELECT and join processing
• Python sqlite3 documentation

The SQL and scenario are illustrative original examples, not a prescribed audit standard or a claim about a real organisation.