Design a Ticket Deletion Lifecycle
Build a SQLite lab for safe ticket deletion, restoration, and retention.
Introduction
30 Second Summary
Support teams sometimes need to remove a ticket without losing details that still matter to a customer or an audit. One careless deletion can erase more history than anyone intended.
In this project, you will build a local support-ticket lifecycle simulator using Python and SQLite. You will compare hard delete with soft delete before adding safe reads, restoration, and a 30-day purge.
What You'll Build
You'll run ticket_lab.py and watch each deletion policy prove itself in the terminal before using two diagrams to explain the design in an interview.
By the end of this project, you'll have:
- A command-line lifecycle demo that shows tickets being permanently deleted, reversibly deleted, restored, and purged after a retention window.
- A set of automated lifecycle checks that proves deleted tickets stay hidden, related comments follow the chosen policy, and purged records cannot return.
- An interview-ready system design with architecture and lifecycle diagrams, a decision matrix, and a clear explanation of each deletion strategy.
- Secret Mission: Reproduce a restore collision caused by an active-only unique index, then block the unsafe restore with an explicit conflict policy.
Are there any prerequisites?
Basic SQL knowledge will help you follow the queries. You should also be comfortable running a Python file in Visual Studio Code's integrated terminal.
Before We Start
Before any hands-on work, commit to a deletion policy you can defend. It must define what stays recoverable, how related records behave, and when primary-database recovery ends.
Set Up the Local Lab
A deletion experiment is only trustworthy when the interpreter is known before any data changes. The embedded database version needs the same certainty.
In this step, you'll create a local workspace in Visual Studio Code.
You'll verify Python 3.15.0. A starter script will then report the embedded SQLite runtime.
In this step, get ready to:
- Create a local project folder with two starter files.
- Check whether your Python installation matches the pinned version.
- Run a starter script that reports the embedded SQLite runtime.
Open a project workspace
A dedicated workspace keeps the lifecycle script beside its design document. It also gives the integrated terminal a predictable starting folder.
Why SQLite for this lab?
SQLite supports real SQL deletion policies without a separate database service. This keeps your attention on ticket lifecycles instead of server administration.
- Select Finder from your Mac's Dock.
- Select Desktop in the Finder sidebar.
- Click File in the macOS menu bar.
- Click New Folder.
- Type soft-delete-lab as the folder name.
- Press Return to create the folder.
You should see the soft-delete-lab folder on your Desktop.
- Press Cmd+Space on macOS to open Spotlight.
- Type Visual Studio Code into Spotlight.
- Press Enter to open Visual Studio Code.
- Click File in the Visual Studio Code menu bar.
- Click Open Folder.
- Select the soft-delete-lab folder on your Desktop.
- Click Open to load the workspace.
You should see soft-delete-lab at the top of the Explorer sidebar.
- Click the New File button in the Explorer sidebar.
- Type ticket_lab.py as the file name.
- Press Enter to create the file.
- Click the New File button again.
- Type SYSTEM_DESIGN.md as the file name.
- Press Enter to create the file.
- Select SYSTEM_DESIGN.md in the Explorer sidebar.
- Add the starter design document by pasting this content:
# Support Ticket Deletion Lifecycle
This document will explain the ticket lifecycle architecture, deletion policy, query-safety contract, indexing decisions, retention policy, and trade-offs.
What does this document start?
The heading names the system you are designing. The opening sentence records the decisions that the finished document will defend.
- Press Cmd+S on macOS or Ctrl+S on Windows to save SYSTEM_DESIGN.md.
- Confirm that Support Ticket Deletion Lifecycle appears at the top of the editor.
Don't see both files?
- Confirm that the Explorer sidebar shows the soft-delete-lab folder.
- Check that both file names use the exact capitalization shown above.
- Confirm that ticket_lab.py does not have an extra text-file extension.
Still stuck? Help me create the two starter files in my Visual Studio Code workspace.
✔️ Awesome, I've got everything!
Your starter design document matches the workspace checkpoint.
ⓧ I'd like to double check the full code
# Support Ticket Deletion Lifecycle
This document will explain the ticket lifecycle architecture, deletion policy, query-safety contract, indexing decisions, retention policy, and trade-offs.
Verify Python and align the pinned environment
This lab pins Python 3.15.0 so the interpreter carries the expected SQLite runtime. The version check tells you whether the installer fallback is needed.
- Click View in the Visual Studio Code menu bar.
- Click Terminal to open the integrated terminal.
- Check your Python version by running this command:
python3 --versionWhat does this check prove?
The command reports which Python version your terminal finds first. The pinned path is ready when the output reports Python 3.15.0.
✔️ I see version 3.15.0
Great, your terminal is using Python 3.15.0. The pinned interpreter is ready for the runtime check.
ⓧ I see a different version
Your current Python version differs from the pinned 3.15.0 environment. Installing the pinned release aligns the embedded SQLite runtime used by this lab.
- Visit the official Python release page.
- Download the official macOS installer for Python 3.15.0.
- Open the downloaded .pkg file.
- Complete the standard installer flow.
- Run Install Certificates.command after the installation finishes.
Complete the certificate step
The certificate helper is easy to skip after the main installer closes. Completing it finishes the standard macOS setup for the pinned interpreter.
- Return to Visual Studio Code.
- Close the existing integrated terminal.
- Click View in the menu bar.
- Click Terminal to open a fresh terminal.
- Confirm the pinned version by running this command:
python3 --versionWhat should I see?
The fresh terminal should report Python 3.15.0. This confirms that the pinned interpreter is now available.
ⓧ Command not found
Your terminal cannot currently find Python. The official macOS installer adds the pinned interpreter needed for this lab.
- Visit the official Python release page.
- Download the official macOS installer for Python 3.15.0.
- Open the downloaded .pkg file.
- Complete the standard installer flow.
- Run Install Certificates.command after the installation finishes.
Complete the certificate step
The certificate helper is a separate part of the standard macOS setup. Run it before returning to the project terminal.
- Return to Visual Studio Code.
- Close the existing integrated terminal.
- Click View in the menu bar.
- Click Terminal to open a fresh terminal.
- Confirm the installation by running this command:
python3 --versionWhat should I see?
The fresh terminal should report Python 3.15.0. This confirms that the installer made the interpreter available.
No packages required
This project uses Python's standard library. You do not need a virtual environment or a package installation step.
Still seeing the wrong Python version?
- Confirm that you reopened the integrated terminal after completing the installer.
- Run the version check from the new terminal.
- Confirm that the output reports Python 3.15.0 before continuing.
Need help? Help me align my Visual Studio Code terminal with Python 3.15.0.
Prove the embedded SQLite runtime works
Python's standard library includes the database module used by this lab. Reading its runtime version proves which embedded SQLite library the interpreter loaded.
- Select ticket_lab.py in the Explorer sidebar.
- Add the runtime checkpoint by pasting this code into the file:
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
print(f"SQLite runtime: {sqlite3.sqlite_version}")
What does this code do?
- The sqlite3 module provides the database features used by the lifecycle lab.
- The time() function will provide epoch seconds for deletion timestamps in later lifecycle steps.
- DATABASE_FILE names the local database file that a later step creates.
- RETENTION_DAYS records the future retention window.
- SECONDS_PER_DAY converts that window into epoch seconds.
- ACTIVE_INDEX_NAME keeps the future active-row index name in one place.
- sqlite3.sqlite_version reports the SQLite library loaded by this Python interpreter.
- Press Cmd+S on macOS or Ctrl+S on Windows to save ticket_lab.py.
Before you run the script, what SQLite version do you expect the pinned Python environment to report?
- Run the runtime checkpoint in the integrated terminal with this command:
python3 ticket_lab.pyWhat should I see?
With the pinned environment, you'll see SQLite runtime: 3.53.4 in the terminal. The Explorer still lists only your two starter files because the script has not created ticket_lab.db.
That is the foundation locked in: your script now reports the pinned embedded database before any ticket data exists.
Runtime version looks different?
- Return to the applicable Python outcome tab if your interpreter version differs from 3.15.0.
- Reopen the integrated terminal after installing the pinned interpreter.
- Run the script again from the soft-delete-lab workspace.
Still seeing a mismatch? Help me diagnose why ticket_lab.py reports a different SQLite runtime.
✔️ Awesome, I've got everything!
Your starter script matches the checkpoint for this step.
ⓧ I'd like to double check the full code
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
print(f"SQLite runtime: {sqlite3.sqlite_version}")
Your local lab now has a verified runtime plus a design document ready to grow. Next, you'll create the ticket model and prove what hard deletion destroys.
Prove What Hard Delete Destroys
Your Visual Studio Code workspace already confirms that Python can reach the expected SQLite runtime. You now have a known environment for testing deletion behavior.
A hard delete removes the parent row. Its foreign-key cascade can remove dependent comments too.
The primary database then has no row to restore. You will prove each consequence with executable assertions.
In this step, get ready to:
- Create a relational ticket model with repeatable seed data.
- Hard-delete ticket 1 through a parameterized query.
- Assert that the ticket cannot be restored from the primary database.
Create the relational model
The current runtime print proves that the database library loads. The next layer creates a reusable connection with foreign-key enforcement.
- In ticket_lab.py, find the runtime print at the bottom of the file:
print(f"SQLite runtime: {sqlite3.sqlite_version}")Why move this print?
This standalone print runs as soon as Python reads the file. Moving it into run_demo() keeps all demonstration output behind one entry point.
- Delete that standalone runtime print.
Foreign-key enforcement belongs to each database connection. The connection also verifies the setting before any destructive operation can run.
- Add connect() below the constants by pasting this code:
def connect():
connection = sqlite3.connect(DATABASE_FILE)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys = ON")
foreign_keys_enabled = connection.execute("PRAGMA foreign_keys").fetchone()[0]
if foreign_keys_enabled != 1:
connection.close()
raise RuntimeError("SQLite foreign-key enforcement is not enabled")
return connectionWhat does this connection enforce?
- The connection opens the local ticket_lab.db database file.
- The row factory lets later queries read columns by name.
- The foreign-key check stops the lab if cascade enforcement is unavailable.
A single demo function gives the lab a controlled entry point. Its connection closes even if a later assertion fails.
- Add the initial run_demo() structure at the end of ticket_lab.py by pasting this code:
def run_demo():
now = int(time())
connection = connect()
try:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
finally:
connection.close()
if __name__ == "__main__":
run_demo()How does the entry point work?
- The now value captures the current epoch time for repeatable lifecycle calculations.
- The try block contains the demonstration.
- The finally block closes the database connection.
- Save ticket_lab.py.
Before you run the script, do you expect the connection to create the local database file?
- Create the database file through the new entry point by running:
python3 ticket_lab.pyWhat should you see?
You should see the SQLite runtime line in the terminal. The command should return to the terminal prompt without an exception.
- Look for ticket_lab.db in the VS Code Explorer sidebar.
You should see the new database file beside ticket_lab.py.
Database file missing?
- Confirm the VS Code terminal is still inside the soft-delete-lab folder.
- Confirm the final guard calls run_demo().
Still stuck? Help me find why ticket_lab.db was not created.
The model uses tickets as the parent table. Each comment points to a ticket through a primary key relationship.
- Add the schema portion of reset_database() above run_demo() by pasting this code:
def reset_database(connection, now):
old_deleted_at = now - ((RETENTION_DAYS + 1) * SECONDS_PER_DAY)
connection.executescript(
"""
DROP TABLE IF EXISTS ticket_comments;
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
ticket_number TEXT NOT NULL UNIQUE,
customer_email TEXT NOT NULL,
subject TEXT NOT NULL,
deleted_at INTEGER
);
CREATE TABLE ticket_comments (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);
"""
)What does the schema establish?
- The reset drops dependent comments before dropping their parent tickets.
- The unique ticket_number keeps each ticket identity reserved.
- The cascade tells SQLite to delete matching comments when their parent ticket is deleted.
- Continue reset_database() by adding this seed data immediately after the schema code:
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
(4, "T-1004", "dee@example.com", "Old notification issue", old_deleted_at),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
(4, 4, "Resolved before the retention window."),
]
connection.executemany(
"""
INSERT INTO tickets(id, ticket_number, customer_email, subject, deleted_at)
VALUES (?, ?, ?, ?, ?)
""",
tickets,
)
connection.executemany(
"INSERT INTO ticket_comments(id, ticket_id, body) VALUES (?, ?, ?)",
comments,
)
connection.commit()How is the sample data protected?
- The seed creates four known ticket states for the complete lifecycle lab.
- Ticket 1 receives two comments so the cascade has a visible effect.
- The question-mark placeholders keep data separate from each SQL statement.
- In run_demo(), find this runtime print:
print(f"SQLite runtime: {sqlite3.sqlite_version}")Where does the reset belong?
The reset belongs after the runtime print. This keeps the environment check visible before the sample tables are rebuilt.
- Replace that line with this expanded demo opening:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
print("\n1. HARD DELETE")
reset_database(connection, now)What changes during this run?
The reset now rebuilds both tables every time the script starts. Each run begins from the same four tickets.
- Save ticket_lab.py.
- Rebuild the sample database by running:
python3 ticket_lab.pyWhat should you see now?
You should see 1. HARD DELETE below the runtime line. Reaching that heading proves the reset completed without a schema error.
Seeing a schema error?
- Check that reset_database() appears above run_demo().
- Check that both seed lists remain indented inside reset_database().
Need a second pair of eyes? Help me debug my reset_database function.
Implement the irreversible operation
A parameterized DELETE targets one ticket by ID. The connection context commits the change when the operation succeeds.
- Add the deletion functions below reset_database() by pasting this code:
def hard_delete_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"DELETE FROM tickets WHERE id = ?",
(ticket_id,),
)
return cursor.rowcount
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL
""",
(ticket_id,),
)
return cursor.rowcountHow do these operations differ?
- The hard-delete function physically removes the matching row.
- The restore function can only update a deleted row that still exists.
- Each function returns the number of changed rows.
The demonstration needs observable queries around the destructive operation. One helper checks the parent row while another counts dependent comments.
- Add the observation helpers below restore_ticket() by pasting this code:
def ticket_exists(connection, ticket_id):
row = connection.execute(
"SELECT id FROM tickets WHERE id = ?",
(ticket_id,),
).fetchone()
return row is not None
def comment_count(connection, ticket_id):
row = connection.execute(
"SELECT COUNT(*) AS total FROM ticket_comments WHERE ticket_id = ?",
(ticket_id,),
).fetchone()
return row["total"]What do the helpers reveal?
- The existence query converts a matching row into a clear true-or-false result.
- The comment query exposes the cascade through a concrete count.
- In run_demo(), find this reset call:
reset_database(connection, now)Why use this anchor?
The destructive demonstration must start immediately after the clean reset. This anchor keeps the before-state predictable.
- Add the hard-delete demonstration immediately below that reset call:
print(f"Ticket 1 comments before: {comment_count(connection, 1)}")
hard_deleted = hard_delete_ticket(connection, 1)
hard_restore_rows = restore_ticket(connection, 1)
print(f"Ticket 1 exists after hard delete: {ticket_exists(connection, 1)}")
print(f"Ticket 1 comments after cascade: {comment_count(connection, 1)}")
print(f"Restore attempts after hard delete changed rows: {hard_restore_rows}")What does the demonstration measure?
- The first count records the two comments before deletion.
- The hard delete removes ticket 1.
- The restore attempt tests whether a primary-database row remains.
- Save ticket_lab.py.
Before you run the demonstration, do you expect the later restore attempt to find any row to update?
- Reveal the hard-delete outcome by running:
python3 ticket_lab.pyWhat should the deletion reveal?
- You should see Ticket 1 comments before: 2.
- You should see Ticket 1 exists after hard delete: False.
- You should see Ticket 1 comments after cascade: 0.
- You should see the restore attempt report zero changed rows.
Seeing the wrong deletion result?
- Check that hard_delete_ticket() receives ticket ID 1.
- Check that the comment foreign key ends with ON DELETE CASCADE.
- Check that connect() enables foreign-key enforcement before the reset runs.
Still seeing a ticket or comments? Help me debug the hard-delete cascade.
Lock in the behavior with assertions
Printed output makes the lifecycle visible. Assertions turn the same expectations into automated checks.
- In run_demo(), find the final hard-delete print:
print(f"Restore attempts after hard delete changed rows: {hard_restore_rows}")Where should the assertions run?
The assertions belong after every outcome has been calculated. They test the same state that the terminal displays.
- Add these assertions immediately below that print:
assert hard_deleted == 1
assert not ticket_exists(connection, 1)
assert comment_count(connection, 1) == 0
assert hard_restore_rows == 0What do the assertions guarantee?
- Exactly one parent ticket must be deleted.
- Ticket 1 must be absent after deletion.
- Ticket 1 must have zero remaining comments.
- The restore attempt must change zero rows.
Your design document should record the policy that the executable lab now proves. This connects the SQL behavior to a system design explanation.
- In SYSTEM_DESIGN.md, replace the starter content with this hard-delete baseline:
# Support Ticket Deletion Lifecycle
The project models `tickets` as the parent table and `ticket_comments` as dependent records. Hard deletion uses `DELETE FROM tickets WHERE id = ?`; SQLite foreign-key enforcement and `ON DELETE CASCADE` remove dependent comments. A hard-deleted ticket has no primary-database row for `restore_ticket` to update.Why record this boundary?
The design note names the parent relationship. It also explains why restoration fails after physical deletion.
- Save ticket_lab.py.
- Save SYSTEM_DESIGN.md.
Before the final run, do you expect every assertion to pass after ticket 1 disappears?
- Verify the complete hard-delete baseline by running:
python3 ticket_lab.pyWhat proves the baseline works?
- You should see Ticket 1 exists after hard delete: False.
- You should see Ticket 1 comments after cascade: 0.
- You should see Restore attempts after hard delete changed rows: 0.
- You should return to the terminal prompt after all four assertions pass.
Did an assertion stop the script?
- Compare the failed condition with the printed value directly above it.
- Check that ticket ID 1 is used in every hard-delete assertion.
- Check that hard_restore_rows stores the result from restore_ticket().
Need help tracing the failed check? Help me diagnose my hard-delete assertion.
You have now proved the destructive boundary. Ticket 1 has no row or comments left in the primary database.
✔️ Awesome, I've got everything!
Great. Double-check that both files are saved before you continue.
ⓧ I'd like to double check the full code
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
def connect():
connection = sqlite3.connect(DATABASE_FILE)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys = ON")
foreign_keys_enabled = connection.execute("PRAGMA foreign_keys").fetchone()[0]
if foreign_keys_enabled != 1:
connection.close()
raise RuntimeError("SQLite foreign-key enforcement is not enabled")
return connection
def reset_database(connection, now):
old_deleted_at = now - ((RETENTION_DAYS + 1) * SECONDS_PER_DAY)
connection.executescript(
"""
DROP TABLE IF EXISTS ticket_comments;
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
ticket_number TEXT NOT NULL UNIQUE,
customer_email TEXT NOT NULL,
subject TEXT NOT NULL,
deleted_at INTEGER
);
CREATE TABLE ticket_comments (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);
"""
)
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
(4, "T-1004", "dee@example.com", "Old notification issue", old_deleted_at),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
(4, 4, "Resolved before the retention window."),
]
connection.executemany(
"""
INSERT INTO tickets(id, ticket_number, customer_email, subject, deleted_at)
VALUES (?, ?, ?, ?, ?)
""",
tickets,
)
connection.executemany(
"INSERT INTO ticket_comments(id, ticket_id, body) VALUES (?, ?, ?)",
comments,
)
connection.commit()
def hard_delete_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"DELETE FROM tickets WHERE id = ?",
(ticket_id,),
)
return cursor.rowcount
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL
""",
(ticket_id,),
)
return cursor.rowcount
def ticket_exists(connection, ticket_id):
row = connection.execute(
"SELECT id FROM tickets WHERE id = ?",
(ticket_id,),
).fetchone()
return row is not None
def comment_count(connection, ticket_id):
row = connection.execute(
"SELECT COUNT(*) AS total FROM ticket_comments WHERE ticket_id = ?",
(ticket_id,),
).fetchone()
return row["total"]
def run_demo():
now = int(time())
connection = connect()
try:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
print("\n1. HARD DELETE")
reset_database(connection, now)
print(f"Ticket 1 comments before: {comment_count(connection, 1)}")
hard_deleted = hard_delete_ticket(connection, 1)
hard_restore_rows = restore_ticket(connection, 1)
print(f"Ticket 1 exists after hard delete: {ticket_exists(connection, 1)}")
print(f"Ticket 1 comments after cascade: {comment_count(connection, 1)}")
print(f"Restore attempts after hard delete changed rows: {hard_restore_rows}")
assert hard_deleted == 1
assert not ticket_exists(connection, 1)
assert comment_count(connection, 1) == 0
assert hard_restore_rows == 0
finally:
connection.close()
if __name__ == "__main__":
run_demo()# Support Ticket Deletion Lifecycle
The project models `tickets` as the parent table and `ticket_comments` as dependent records. Hard deletion uses `DELETE FROM tickets WHERE id = ?`; SQLite foreign-key enforcement and `ON DELETE CASCADE` remove dependent comments. A hard-deleted ticket has no primary-database row for `restore_ticket` to update.Your hard-delete baseline now proves what permanent removal costs. Next, you will preserve the ticket row through a lifecycle state change and expose the query leak that creates.
Add Soft Delete and Expose the Leak
Your SQLite hard delete baseline now proves that the parent row disappears. Its dependent comments disappear with it.
A soft delete preserves the row by changing its lifecycle state. An ordinary read query can still return that preserved row.
In this step, get ready to:
- Add a soft-delete state transition for active tickets.
- Run an unrestricted ticket query that exposes deleted data.
- Prove that the ticket row and its dependent comment remain stored.
Turn deletion into a state transition
The deleted_at column records when a ticket entered the deleted state. An epoch timestamp gives the later purge policy a numeric cutoff to compare.
- In ticket_lab.py, find the hard_delete_ticket() function.
- Add the soft-delete function below hard_delete_ticket() by pasting this code:
def soft_delete_ticket(connection, ticket_id, deleted_at=None):
deletion_time = int(time()) if deleted_at is None else deleted_at
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = ?
WHERE id = ? AND deleted_at IS NULL
""",
(deletion_time, ticket_id),
)
return cursor.rowcountWhat Does This Function Do?
- The deletion_time value holds the supplied timestamp or the current time.
- The UPDATE changes the lifecycle column while preserving the ticket row.
- The deleted_at IS NULL condition limits the transition to an active ticket.
- The returned rowcount shows whether one row changed.
The demonstration needs a clean copy of the seed data after the destructive scenario. This reset makes the hard-delete result independent from the soft-delete comparison.
- In run_demo(), find the line assert hard_restore_rows == 0.
- Add the first soft-delete demonstration directly below that assertion by pasting this code:
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
soft_deleted = soft_delete_ticket(connection, 2, now)
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1What Does This Demonstration Prove?
- The second reset_database() call restores the original sample rows after the hard-delete scenario.
- Ticket 2 receives the timestamp stored in now.
- The assertions prove that the ticket row still exists after its lifecycle state changes.
- The comment count proves that updating deleted_at does not trigger the parent-row delete cascade.
- Save ticket_lab.py.
- Run the updated demonstration in the Visual Studio Code terminal with this command:
python3 ticket_lab.pyWhat Should You See?
The hard-delete assertions should still pass. The new section should print Ticket 2 comments after soft delete: 1.
Good progress. Ticket 2 now carries a deleted timestamp while its dependent comment remains available.
Soft-Delete Check Failing?
- Check that soft_delete_ticket() appears above run_demo().
- Check that the second reset_database(connection, now) call appears before the soft-delete call.
- Check that the update condition contains id = ? AND deleted_at IS NULL.
Still stuck? Help me debug the soft-delete state transition.
Expose the unrestricted read
The database still contains ticket 2. A query without a lifecycle condition reads every stored ticket.
- In ticket_lab.py, find the comment_count() function.
- Add the unrestricted read helper below comment_count() by pasting this code:
def list_all_ticket_ids(connection):
rows = connection.execute(
"SELECT id FROM tickets ORDER BY id"
).fetchall()
return [row["id"] for row in rows]What Does This Query Do?
- The query reads every ticket ID in numeric order.
- The query has no condition involving deleted_at.
- The list comprehension converts the returned rows into a plain list of IDs.
- In run_demo(), find the soft-delete section that starts with print("\n2. SOFT DELETE AND QUERY SAFETY").
- Replace that section with this expanded version:
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
soft_deleted = soft_delete_ticket(connection, 2, now)
unsafe_ids = list_all_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert 2 in unsafe_ids
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1How Is the Leak Measured?
- The demonstration resets the sample data before changing ticket 2.
- The unrestricted helper reads the IDs after the soft delete.
- The assertion requires ticket 2 to appear in the unrestricted result.
- The final assertions confirm that the preserved parent still owns one comment.
- Save ticket_lab.py.
Before you run this, do you expect the unrestricted query to include ticket 2?
- Expose the query result by running this command:
python3 ticket_lab.pyThe Leak Is Real
You should see Unsafe query IDs: [1, 2, 3, 4]. Ticket 2 remains visible because the query reads stored rows without checking their lifecycle state.
You should also see Ticket 2 comments after soft delete: 1. The update preserves both records.
You have now reproduced the intended failure. The unrestricted path stays in the lab for administrative checks and test comparisons.
Ticket 2 Missing From the Unsafe Result?
- Check that list_all_ticket_ids() uses SELECT id FROM tickets ORDER BY id.
- Check that reset_database(connection, now) runs before soft_delete_ticket(connection, 2, now).
- Check that unsafe_ids receives the result from list_all_ticket_ids(connection).
Need a second pair of eyes? Help me find why ticket 2 is missing from the unsafe query.
✔️ Awesome, I've got everything!
Great. Save ticket_lab.py so the working soft-delete leak stays captured.
ⓧ I'd like to double check the full code
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
def connect():
connection = sqlite3.connect(DATABASE_FILE)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys = ON")
foreign_keys_enabled = connection.execute("PRAGMA foreign_keys").fetchone()[0]
if foreign_keys_enabled != 1:
connection.close()
raise RuntimeError("SQLite foreign-key enforcement is not enabled")
return connection
def reset_database(connection, now):
old_deleted_at = now - ((RETENTION_DAYS + 1) * SECONDS_PER_DAY)
connection.executescript(
"""
DROP TABLE IF EXISTS ticket_comments;
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
ticket_number TEXT NOT NULL UNIQUE,
customer_email TEXT NOT NULL,
subject TEXT NOT NULL,
deleted_at INTEGER
);
CREATE TABLE ticket_comments (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);
"""
)
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
(4, "T-1004", "dee@example.com", "Old notification issue", old_deleted_at),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
(4, 4, "Resolved before the retention window."),
]
connection.executemany(
"""
INSERT INTO tickets(id, ticket_number, customer_email, subject, deleted_at)
VALUES (?, ?, ?, ?, ?)
""",
tickets,
)
connection.executemany(
"INSERT INTO ticket_comments(id, ticket_id, body) VALUES (?, ?, ?)",
comments,
)
connection.commit()
def hard_delete_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"DELETE FROM tickets WHERE id = ?",
(ticket_id,),
)
return cursor.rowcount
def soft_delete_ticket(connection, ticket_id, deleted_at=None):
deletion_time = int(time()) if deleted_at is None else deleted_at
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = ?
WHERE id = ? AND deleted_at IS NULL
""",
(deletion_time, ticket_id),
)
return cursor.rowcount
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL
""",
(ticket_id,),
)
return cursor.rowcount
def ticket_exists(connection, ticket_id):
row = connection.execute(
"SELECT id FROM tickets WHERE id = ?",
(ticket_id,),
).fetchone()
return row is not None
def comment_count(connection, ticket_id):
row = connection.execute(
"SELECT COUNT(*) AS total FROM ticket_comments WHERE ticket_id = ?",
(ticket_id,),
).fetchone()
return row["total"]
def list_all_ticket_ids(connection):
rows = connection.execute(
"SELECT id FROM tickets ORDER BY id"
).fetchall()
return [row["id"] for row in rows]
def run_demo():
now = int(time())
connection = connect()
try:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
print("\n1. HARD DELETE")
reset_database(connection, now)
print(f"Ticket 1 comments before: {comment_count(connection, 1)}")
hard_deleted = hard_delete_ticket(connection, 1)
hard_restore_rows = restore_ticket(connection, 1)
print(f"Ticket 1 exists after hard delete: {ticket_exists(connection, 1)}")
print(f"Ticket 1 comments after cascade: {comment_count(connection, 1)}")
print(f"Restore attempts after hard delete changed rows: {hard_restore_rows}")
assert hard_deleted == 1
assert not ticket_exists(connection, 1)
assert comment_count(connection, 1) == 0
assert hard_restore_rows == 0
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
soft_deleted = soft_delete_ticket(connection, 2, now)
unsafe_ids = list_all_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert 2 in unsafe_ids
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1
finally:
connection.close()
if __name__ == "__main__":
run_demo()Document the lifecycle difference
Your system design notes now have two observable outcomes to compare. Hard deletion removes the parent row while soft deletion preserves the full relationship.
- Switch back to SYSTEM_DESIGN.md in Visual Studio Code.
- Replace the file contents with this lifecycle comparison:
# Support Ticket Deletion Lifecycle
## Hard delete
Hard deletion removes a parent ticket row. With `ticket_comments.ticket_id` configured as `FOREIGN KEY ... ON DELETE CASCADE`, deleting a ticket also deletes its dependent comments. The primary database has no row to restore afterward.
## Soft delete
Soft deletion changes `tickets.deleted_at` from `NULL` to an epoch timestamp. The ticket row and its comments remain physically present. An unrestricted query such as `SELECT id FROM tickets ORDER BY id` therefore leaks soft-deleted ticket 2 unless product reads explicitly enforce lifecycle filtering.What Does This Design Note Capture?
- The hard-delete section records the physical removal of the parent row.
- The cascade description records why dependent comments also disappear.
- The soft-delete section records the retained ticket state.
- The unrestricted query documents the read-path risk you reproduced.
- Save SYSTEM_DESIGN.md.
- Read the saved hard-delete and soft-delete sections in the editor.
You should see one section describing physical removal. The other section should describe preserved rows plus the unrestricted query leak.
Design Notes Look Incomplete?
- Check that SYSTEM_DESIGN.md contains both second-level headings.
- Check that the soft-delete section names tickets.deleted_at.
- Check that the unrestricted SQL contains ORDER BY id.
Need help checking the comparison? Help me review my hard-delete and soft-delete design notes.
Before the final run, which ticket ID do you expect the unrestricted query to reveal?
- Verify the completed comparison by running this command in the Visual Studio Code terminal:
python3 ticket_lab.pyWhat Should the Final Check Show?
- The terminal should print Unsafe query IDs: [1, 2, 3, 4].
- The terminal should print Ticket 2 comments after soft delete: 1.
- The script should finish without an assertion failure.
Strong work. Your lab now proves that preserved data can cross an unrestricted read boundary.
The leak is now reproducible and backed by assertions. Next, you will define the active-record boundary and restore ticket 2 through it.
Make Reads Safe and Restore Tickets
Your last run proved that a soft-deleted ticket still occupies its SQLite row. The unrestricted read exposed ticket 2 to the product.
Now you'll define a safe read contract for active tickets. You'll also restore ticket 2 without creating a new row.
A partial index will use the same active-row definition. This keeps storage behavior aligned with product behavior.
In this step, get ready to:
- Create a safe query that returns active tickets only.
- Restore a soft-deleted ticket to the active state.
- Create a partial index for active customer lookups.
Create the active-ticket read contract
A query-safety contract defines which rows normal product reads may expose. Active ticket reads require the explicit predicate deleted_at IS NULL.
- In ticket_lab.py, locate the list_all_ticket_ids function.
- Insert the safe active-ticket function directly below it by copying this code:
def list_active_ticket_ids(connection):
rows = connection.execute(
"""
SELECT id
FROM tickets
WHERE deleted_at IS NULL
ORDER BY id
"""
).fetchall()
return [row["id"] for row in rows]What does this function do?
- The WHERE deleted_at IS NULL predicate limits the result to active tickets.
- The ORDER BY id clause keeps the output predictable for assertions.
- In run_demo, locate the soft-delete section that builds unsafe_ids.
- Replace that section through the comment-count assertion with this safe-query comparison:
soft_deleted = soft_delete_ticket(connection, 2, now)
unsafe_ids = list_all_ticket_ids(connection)
safe_ids = list_active_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(f"Safe query IDs: {safe_ids}")
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert 2 in unsafe_ids
assert 2 not in safe_ids
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1How does the boundary work?
- The unrestricted path still includes ticket 2 for administrative comparisons.
- The active path hides ticket 2 because its deleted_at value contains an epoch timestamp.
- The assertions turn the lifecycle policy into an automated check.
- Save ticket_lab.py.
Before you run the script, do you expect ticket 2 to appear in both query results?
- Test the active-ticket boundary by running this command in the Visual Studio Code terminal:
python3 ticket_lab.pyWhat should you see?
The unsafe IDs include ticket 2. The safe IDs exclude ticket 2.
The script reaches the comment-count output without an assertion failure. Your product read now enforces the active-ticket boundary.
Still seeing ticket 2 in the safe IDs?
- Check that list_active_ticket_ids includes WHERE deleted_at IS NULL.
- Check that safe_ids calls list_active_ticket_ids.
Need help tracing the result? Help me debug why ticket 2 still appears in my safe ticket query.
Restore the soft-deleted ticket
Restoration reverses the soft-delete state transition. It clears deleted_at only when the ticket is currently deleted.
- In ticket_lab.py, locate soft_delete_ticket.
- Insert the restoration function directly below it by copying this code:
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL
""",
(ticket_id,),
)
return cursor.rowcountWhat does restoration protect?
- The deleted-state condition prevents an active ticket from counting as restored.
- The connection context commits a successful update as one transaction.
- The returned row count proves whether a deleted row changed state.
- In run_demo, locate the final soft-delete assertion.
- Insert the restoration demonstration directly below that assertion by copying this code:
print("Query safety checks passed.")
print("\n3. RESTORE")
restored = restore_ticket(connection, 2)
restored_active_ids = list_active_ticket_ids(connection)
print(f"Restored active IDs: {restored_active_ids}")
assert restored == 1
assert 2 in restored_active_ids
print("Restore check passed.")How is restoration verified?
- The restore changes exactly one deleted ticket.
- The safe read includes ticket 2 after deleted_at returns to NULL.
- The same ticket row keeps its globally unique ticket_number.
- Save ticket_lab.py.
Before you rerun the script, do you expect ticket 2 to return through the safe read path?
- Verify the restored state by running this command:
python3 ticket_lab.pyWhat should you see?
The Restored active IDs output includes ticket 2. The script then prints Restore check passed..
That result proves restoration reactivates the preserved row. The original ticket number remains reserved throughout the lifecycle.
Restore check failing?
- Check that restore_ticket sets deleted_at = NULL.
- Check that the restore condition uses deleted_at IS NOT NULL.
- Check that restored_active_ids is created after the restore call.
Need another pair of eyes? Help me debug why ticket 2 does not return after restoration.
Align the partial index with active reads
A partial index stores entries for rows that satisfy its predicate. Matching that predicate to the safe query keeps active customer lookups focused on active tickets.
- Near the top of ticket_lab.py, add the index-name constant by copying this line:
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"Why name the index once?
The constant gives schema creation and inspection one shared index name. A typo becomes easier to detect during the automated check.
- In the reset_database schema script, locate the closing statement for ticket_comments.
- Insert the active-customer index directly below the table definition by copying this SQL:
CREATE INDEX idx_tickets_active_customer
ON tickets(customer_email)
WHERE deleted_at IS NULL;How does the index match the contract?
The index contains customer-email entries only for active rows. Its deleted_at IS NULL predicate matches the product query exactly.
- Save ticket_lab.py.
- Rebuild the sample database with the partial index by running this command:
python3 ticket_lab.pyWhat does this run prove?
The script still reaches Restore check passed. after rebuilding the database. The new index does not change the lifecycle results.
Seeing a schema error?
- Check that the index statement stays inside the existing schema script.
- Check that the index targets tickets(customer_email).
Still blocked? Help me debug the active-ticket partial index schema.
- Insert the index inspection helper directly below list_active_ticket_ids by copying this code:
def active_partial_index_is_ready(connection):
indexes = connection.execute("PRAGMA index_list('tickets')").fetchall()
return any(
row["name"] == ACTIVE_INDEX_NAME and row["partial"] == 1
for row in indexes
)What does the inspection check?
- The pragma returns the indexes attached to the tickets table.
- The helper finds the expected index by name.
- The partial flag must equal 1.
- In run_demo, locate the soft-delete section.
- Replace that section through Query safety checks passed. with this indexed version:
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
print(
"Active partial index ready: "
f"{active_partial_index_is_ready(connection)}"
)
soft_deleted = soft_delete_ticket(connection, 2, now)
unsafe_ids = list_all_ticket_ids(connection)
safe_ids = list_active_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(f"Safe query IDs: {safe_ids}")
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert 2 in unsafe_ids
assert 2 not in safe_ids
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1
assert active_partial_index_is_ready(connection)
print("Query safety checks passed.")How is the index verified?
The demonstration inspects the index before the soft delete. The assertion stops the script if the named index is missing or lacks the partial flag.
The query checks still prove ticket 2 is unsafe-visible but safe-hidden. Schema policy and read policy now share one definition of active data.
- Save ticket_lab.py.
Before you run the indexed version, do you expect the inspection helper to return true?
- Verify the partial index by running this command:
python3 ticket_lab.pyWhat should you see?
The script prints Active partial index ready: True before the soft-delete comparison. It also completes the query-safety assertions.
That output proves the index exists with the expected partial flag. The active predicate now protects both the read path and the index contents.
Index check returning false?
- Check that the schema creates idx_tickets_active_customer.
- Check that ACTIVE_INDEX_NAME contains the same spelling.
- Check that the index includes WHERE deleted_at IS NULL.
Need help comparing the schema with the inspection result? Help me debug why my active partial index check returns false.
The implementation now needs a written contract that future read paths can follow. Your design notes will capture query safety, restoration, indexing, and uniqueness.
- Switch to SYSTEM_DESIGN.md in Visual Studio Code.
- Replace its contents with this design record:
# Support Ticket Deletion Lifecycle
## Query-safety contract
Normal product reads must include:
```sql
WHERE deleted_at IS NULL
```
The unrestricted ticket query is reserved for administration, audits, migration, and tests. Counts, joins, exports, caches, search indexes, and background jobs must apply the same active-ticket policy.
## Restoration and indexing
`restore_ticket` clears `deleted_at` only for a ticket that is currently soft-deleted. `ticket_number` remains globally unique, so a deleted ticket's number is never reused. `idx_tickets_active_customer` is a partial index containing only rows where `deleted_at IS NULL`; its predicate matches the safe active-ticket query.What does the design record capture?
- Normal product reads share one active-ticket predicate.
- Administrative paths may deliberately inspect every lifecycle state.
- Global ticket-number uniqueness reserves identity after deletion.
- The partial index follows the same predicate as the safe query.
- Save SYSTEM_DESIGN.md.
- Confirm the file shows the Query-safety contract heading.
- Confirm the file shows the Restoration and indexing heading.
✔️ Awesome, I've got everything!
Great. Double-check that you saved ticket_lab.py and SYSTEM_DESIGN.md.
ⓧ I'd like to double check the full code
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
def connect():
connection = sqlite3.connect(DATABASE_FILE)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys = ON")
foreign_keys_enabled = connection.execute("PRAGMA foreign_keys").fetchone()[0]
if foreign_keys_enabled != 1:
connection.close()
raise RuntimeError("SQLite foreign-key enforcement is not enabled")
return connection
def reset_database(connection, now):
connection.executescript(
"""
DROP TABLE IF EXISTS ticket_comments;
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
ticket_number TEXT NOT NULL UNIQUE,
customer_email TEXT NOT NULL,
subject TEXT NOT NULL,
deleted_at INTEGER
);
CREATE TABLE ticket_comments (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);
CREATE INDEX idx_tickets_active_customer
ON tickets(customer_email)
WHERE deleted_at IS NULL;
"""
)
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
]
connection.executemany(
"""
INSERT INTO tickets(id, ticket_number, customer_email, subject, deleted_at)
VALUES (?, ?, ?, ?, ?)
""",
tickets,
)
connection.executemany(
"INSERT INTO ticket_comments(id, ticket_id, body) VALUES (?, ?, ?)",
comments,
)
connection.commit()
def hard_delete_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"DELETE FROM tickets WHERE id = ?",
(ticket_id,),
)
return cursor.rowcount
def soft_delete_ticket(connection, ticket_id, deleted_at=None):
deletion_time = int(time()) if deleted_at is None else deleted_at
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = ?
WHERE id = ? AND deleted_at IS NULL
""",
(deletion_time, ticket_id),
)
return cursor.rowcount
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute(
"""
UPDATE tickets
SET deleted_at = NULL
WHERE id = ? AND deleted_at IS NOT NULL
""",
(ticket_id,),
)
return cursor.rowcount
def ticket_exists(connection, ticket_id):
row = connection.execute(
"SELECT id FROM tickets WHERE id = ?",
(ticket_id,),
).fetchone()
return row is not None
def comment_count(connection, ticket_id):
row = connection.execute(
"SELECT COUNT(*) AS total FROM ticket_comments WHERE ticket_id = ?",
(ticket_id,),
).fetchone()
return row["total"]
def list_all_ticket_ids(connection):
rows = connection.execute(
"SELECT id FROM tickets ORDER BY id"
).fetchall()
return [row["id"] for row in rows]
def list_active_ticket_ids(connection):
rows = connection.execute(
"""
SELECT id
FROM tickets
WHERE deleted_at IS NULL
ORDER BY id
"""
).fetchall()
return [row["id"] for row in rows]
def active_partial_index_is_ready(connection):
indexes = connection.execute("PRAGMA index_list('tickets')").fetchall()
return any(
row["name"] == ACTIVE_INDEX_NAME and row["partial"] == 1
for row in indexes
)
def run_demo():
now = int(time())
connection = connect()
try:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
print("\n1. HARD DELETE")
reset_database(connection, now)
print(f"Ticket 1 comments before: {comment_count(connection, 1)}")
hard_deleted = hard_delete_ticket(connection, 1)
hard_restore_rows = restore_ticket(connection, 1)
print(f"Ticket 1 exists after hard delete: {ticket_exists(connection, 1)}")
print(f"Ticket 1 comments after cascade: {comment_count(connection, 1)}")
print(
"Restore attempts after hard delete changed rows: "
f"{hard_restore_rows}"
)
assert hard_deleted == 1
assert not ticket_exists(connection, 1)
assert comment_count(connection, 1) == 0
assert hard_restore_rows == 0
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
print(
"Active partial index ready: "
f"{active_partial_index_is_ready(connection)}"
)
soft_deleted = soft_delete_ticket(connection, 2, now)
unsafe_ids = list_all_ticket_ids(connection)
safe_ids = list_active_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(f"Safe query IDs: {safe_ids}")
print(
"Ticket 2 comments after soft delete: "
f"{comment_count(connection, 2)}"
)
assert soft_deleted == 1
assert 2 in unsafe_ids
assert 2 not in safe_ids
assert ticket_exists(connection, 2)
assert comment_count(connection, 2) == 1
assert active_partial_index_is_ready(connection)
print("Query safety checks passed.")
print("\n3. RESTORE")
restored = restore_ticket(connection, 2)
restored_active_ids = list_active_ticket_ids(connection)
print(f"Restored active IDs: {restored_active_ids}")
assert restored == 1
assert 2 in restored_active_ids
print("Restore check passed.")
finally:
connection.close()
if __name__ == "__main__":
run_demo()How should you use this file?
Compare each function with your saved ticket_lab.py file. Pay close attention to the active predicate and the order of the demonstration sections.
# Support Ticket Deletion Lifecycle
## Query-safety contract
Normal product reads must include:
```sql
WHERE deleted_at IS NULL
```
The unrestricted ticket query is reserved for administration, audits, migration, and tests. Counts, joins, exports, caches, search indexes, and background jobs must apply the same active-ticket policy.
## Restoration and indexing
`restore_ticket` clears `deleted_at` only for a ticket that is currently soft-deleted. `ticket_number` remains globally unique, so a deleted ticket's number is never reused. `idx_tickets_active_customer` is a partial index containing only rows where `deleted_at IS NULL`; its predicate matches the safe active-ticket query.What should match?
Your design file should contain these two sections exactly. Together they record the safe-query boundary and the restoration policy.
Before the final run, can the script prove that ticket 2 is hidden before restoration and visible afterward?
- Run the completed lifecycle section from the Visual Studio Code terminal with this command:
python3 ticket_lab.pyWhat should you see?
- The output includes Active partial index ready: True.
- The Safe query IDs output excludes ticket 2.
- The Restored active IDs output includes ticket 2.
- The final line for this section is Restore check passed..
Final lifecycle check failing?
- Check the first traceback line that points into ticket_lab.py.
- Compare the named function with the full-code reference above.
- Rerun the script after saving every edit.
Need help with the failing check? Help me trace my query-safety, restore, or partial-index assertion.
You now have a safe active-ticket boundary, a reversible restore path, and an index that follows the same lifecycle rule. Next, you'll add a retention cutoff that permanently purges expired tickets.
Purge Expired Tickets and Document the Design
Your safe read path now hides deleted tickets. Restoration returns recoverable tickets to the active workflow.
A soft-deleted row can remain in SQLite forever without a retention boundary. That unbounded state weakens the lifecycle policy.
You will add a 30-day cutoff that permanently removes expired rows. Then you will prove the entire lifecycle before documenting its trade-offs.
In this step, get ready to:
- Add a 30-day retention cutoff.
- Purge expired tickets with automated assertions.
- Document the deletion lifecycle in SYSTEM_DESIGN.md.
Implement the hybrid retention policy
A retention window defines how long a soft-deleted ticket remains recoverable. Tickets older than the cutoff become eligible for permanent deletion.
Why use a hybrid policy?
Soft deletion provides a recovery period. A later purge prevents deleted rows from accumulating forever.
The hybrid policy gives users time to recover mistakes while keeping retention bounded.
- In ticket_lab.py, find the constants below the imports.
- Replace the existing database and index constants with this complete block:
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"What do these constants control?
- RETENTION_DAYS sets the recovery period to 30 days.
- SECONDS_PER_DAY converts that period into the same epoch-second format stored in deleted_at.
- Keeping both values beside the database settings makes the retention policy visible.
- Find reset_database(connection, now) in ticket_lab.py.
- Add this line directly below the function definition:
old_deleted_at = now - ((RETENTION_DAYS + 1) * SECONDS_PER_DAY)How is the expired timestamp calculated?
Python subtracts 31 days from the current epoch time. That timestamp sits one day beyond the 30-day retention window.
- Save ticket_lab.py.
- Confirm the retention arithmetic loads by running this command in the Visual Studio Code terminal:
python3 ticket_lab.pyWhat does this check prove?
The command runs the lifecycle checks from the earlier steps with the new retention constants loaded. A complete run confirms that the timestamp calculation does not break the existing scenarios.
The script should finish without an assertion error. Your existing hard-delete, soft-delete, safe-read, and restore checks still pass.
Does the script stop early?
- Check that RETENTION_DAYS and SECONDS_PER_DAY appear above reset_database().
- Check that old_deleted_at is indented inside reset_database().
Still stuck? Help me debug the retention timestamp calculation.
The reset data now needs one ticket that has already exceeded the recovery period. Ticket 4 represents that expired lifecycle state.
- Find the tickets and comments lists inside reset_database().
- Replace both lists with this seeded dataset:
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
(4, "T-1004", "dee@example.com", "Old notification issue", old_deleted_at),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
(4, 4, "Resolved before the retention window."),
]What does the new seed represent?
- Ticket 4 starts with an old deleted_at value. It is already soft-deleted when each scenario begins.
- Comment 4 proves that a permanent parent deletion still follows the configured foreign-key cascade.
- The active tickets retain None as their lifecycle value.
Before you run the script, which ticket list do you expect to include ticket 4?
- Save ticket_lab.py.
- Run the seeded lifecycle scenarios with this command:
python3 ticket_lab.pyWhat does the seeded run reveal?
The unrestricted query includes every stored row. The safe query excludes ticket 4 because its deleted_at value is already populated.
This proves the expired ticket exists before the purge is implemented.
You should see ticket 4 in Unsafe query IDs. You should not see it in Safe query IDs.
Is ticket 4 missing from the unsafe IDs?
- Check that the ticket 4 tuple ends with old_deleted_at.
- Check that the ticket 4 tuple sits inside the tickets list.
- Check that reset_database() inserts the complete tickets list.
Need help? Help me find why expired ticket 4 is missing.
Run the complete lifecycle checks
The purge boundary needs two guards. A row must already be deleted before its timestamp can be compared with the retention cutoff.
These guards protect active tickets from the purge. They also preserve soft-deleted tickets that are still inside the recovery window.
- Find restore_ticket() in ticket_lab.py.
- Add this new function directly below it:
def purge_deleted_before(connection, cutoff):
with connection:
cursor = connection.execute("DELETE FROM tickets WHERE deleted_at IS NOT NULL AND deleted_at < ?", (cutoff,))
return cursor.rowcountWhat does the purge function enforce?
- deleted_at IS NOT NULL restricts the operation to tickets already in the deleted state.
- deleted_at < ? restricts the operation to timestamps older than the supplied cutoff.
- The connection context commits a successful purge. It rolls back the transaction if an exception escapes.
- rowcount reports how many tickets were permanently deleted.
- Find assert 2 in restored_active_ids near the end of run_demo().
- Replace everything after that assertion and before finally: with this retention scenario:
print("\n4. RETENTION PURGE")
cutoff = now - (RETENTION_DAYS * SECONDS_PER_DAY)
purged = purge_deleted_before(connection, cutoff)
purge_restore_rows = restore_ticket(connection, 4)
print(f"Purged tickets: {purged}")
print(f"Restore attempts after purge changed rows: {purge_restore_rows}")
assert purged == 1
assert not ticket_exists(connection, 4)
assert purge_restore_rows == 0
print("\nAll lifecycle checks passed.")What does the retention scenario test?
- cutoff marks the oldest timestamp still covered by the 30-day recovery window.
- purge_deleted_before() removes ticket 4 because it was deleted 31 days ago.
- restore_ticket() changes zero rows because the expired ticket no longer exists.
- The assertions stop the demonstration if any lifecycle promise is broken.
Before you run the complete lab, do you expect exactly one ticket or multiple tickets to cross the retention cutoff?
- Save ticket_lab.py.
- Run the complete lifecycle demonstration with this command:
python3 ticket_lab.pyWhat does the complete run prove?
- The hard-delete scenario proves that ticket 1 disappears with its comments.
- The soft-delete scenario proves that ticket 2 remains stored with its comment.
- The query-safety scenario proves that normal reads hide ticket 2.
- The restore and purge scenarios prove the two possible outcomes after soft deletion.
You should see Purged tickets: 1 and Restore attempts after purge changed rows: 0.
The final line should read All lifecycle checks passed. That is the hard part complete: your lab now enforces the full deletion lifecycle.
Did a lifecycle assertion fail?
- Check that ticket 4 uses old_deleted_at in the seed data.
- Check that the purge predicate includes both lifecycle guards.
- Check that the cutoff subtracts exactly RETENTION_DAYS * SECONDS_PER_DAY from now.
Still seeing a failure? Help me trace the failed lifecycle assertion.
✔️ Awesome, I've got everything!
Your ticket_lab.py file is saved. The complete lifecycle demonstration now passes.
ⓧ I'd like to double check the full code
import sqlite3
from time import time
DATABASE_FILE = "ticket_lab.db"
RETENTION_DAYS = 30
SECONDS_PER_DAY = 24 * 60 * 60
ACTIVE_INDEX_NAME = "idx_tickets_active_customer"
def connect():
connection = sqlite3.connect(DATABASE_FILE)
connection.row_factory = sqlite3.Row
connection.execute("PRAGMA foreign_keys = ON")
foreign_keys_enabled = connection.execute("PRAGMA foreign_keys").fetchone()[0]
if foreign_keys_enabled != 1:
connection.close()
raise RuntimeError("SQLite foreign-key enforcement is not enabled")
return connection
def reset_database(connection, now):
old_deleted_at = now - ((RETENTION_DAYS + 1) * SECONDS_PER_DAY)
connection.executescript(
"""
DROP TABLE IF EXISTS ticket_comments;
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
ticket_number TEXT NOT NULL UNIQUE,
customer_email TEXT NOT NULL,
subject TEXT NOT NULL,
deleted_at INTEGER
);
CREATE TABLE ticket_comments (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL,
body TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
);
CREATE INDEX idx_tickets_active_customer
ON tickets(customer_email)
WHERE deleted_at IS NULL;
"""
)
tickets = [
(1, "T-1001", "ana@example.com", "Cannot reset password", None),
(2, "T-1002", "ben@example.com", "Duplicate card charge", None),
(3, "T-1003", "cy@example.com", "Export is incomplete", None),
(4, "T-1004", "dee@example.com", "Old notification issue", old_deleted_at),
]
comments = [
(1, 1, "Asked customer to confirm the account email."),
(2, 1, "Customer confirmed the email."),
(3, 2, "Payment trace attached."),
(4, 4, "Resolved before the retention window."),
]
connection.executemany(
"""
INSERT INTO tickets(id, ticket_number, customer_email, subject, deleted_at)
VALUES (?, ?, ?, ?, ?)
""",
tickets,
)
connection.executemany(
"INSERT INTO ticket_comments(id, ticket_id, body) VALUES (?, ?, ?)",
comments,
)
connection.commit()
def hard_delete_ticket(connection, ticket_id):
with connection:
cursor = connection.execute("DELETE FROM tickets WHERE id = ?", (ticket_id,))
return cursor.rowcount
def soft_delete_ticket(connection, ticket_id, deleted_at=None):
deletion_time = int(time()) if deleted_at is None else deleted_at
with connection:
cursor = connection.execute("UPDATE tickets SET deleted_at = ? WHERE id = ? AND deleted_at IS NULL", (deletion_time, ticket_id))
return cursor.rowcount
def restore_ticket(connection, ticket_id):
with connection:
cursor = connection.execute("UPDATE tickets SET deleted_at = NULL WHERE id = ? AND deleted_at IS NOT NULL", (ticket_id,))
return cursor.rowcount
def purge_deleted_before(connection, cutoff):
with connection:
cursor = connection.execute("DELETE FROM tickets WHERE deleted_at IS NOT NULL AND deleted_at < ?", (cutoff,))
return cursor.rowcount
def ticket_exists(connection, ticket_id):
return connection.execute("SELECT id FROM tickets WHERE id = ?", (ticket_id,)).fetchone() is not None
def comment_count(connection, ticket_id):
return connection.execute("SELECT COUNT(*) AS total FROM ticket_comments WHERE ticket_id = ?", (ticket_id,)).fetchone()["total"]
def list_all_ticket_ids(connection):
return [row["id"] for row in connection.execute("SELECT id FROM tickets ORDER BY id").fetchall()]
def list_active_ticket_ids(connection):
return [row["id"] for row in connection.execute("SELECT id FROM tickets WHERE deleted_at IS NULL ORDER BY id").fetchall()]
def active_partial_index_is_ready(connection):
indexes = connection.execute("PRAGMA index_list('tickets')").fetchall()
return any(row["name"] == ACTIVE_INDEX_NAME and row["partial"] == 1 for row in indexes)
def run_demo():
now = int(time())
connection = connect()
try:
print(f"SQLite runtime: {sqlite3.sqlite_version}")
print("\n1. HARD DELETE")
reset_database(connection, now)
hard_deleted = hard_delete_ticket(connection, 1)
hard_restore_rows = restore_ticket(connection, 1)
print(f"Ticket 1 exists after hard delete: {ticket_exists(connection, 1)}")
print(f"Ticket 1 comments after cascade: {comment_count(connection, 1)}")
assert hard_deleted == 1
assert not ticket_exists(connection, 1)
assert comment_count(connection, 1) == 0
assert hard_restore_rows == 0
print("\n2. SOFT DELETE AND QUERY SAFETY")
reset_database(connection, now)
print(f"Active partial index ready: {active_partial_index_is_ready(connection)}")
assert soft_delete_ticket(connection, 2, now) == 1
unsafe_ids = list_all_ticket_ids(connection)
safe_ids = list_active_ticket_ids(connection)
print(f"Unsafe query IDs: {unsafe_ids}")
print(f"Safe query IDs: {safe_ids}")
assert 2 in unsafe_ids and 2 not in safe_ids
assert ticket_exists(connection, 2) and comment_count(connection, 2) == 1
assert active_partial_index_is_ready(connection)
print("\n3. RESTORE")
assert restore_ticket(connection, 2) == 1
restored_active_ids = list_active_ticket_ids(connection)
print(f"Restored active IDs: {restored_active_ids}")
assert 2 in restored_active_ids
print("\n4. RETENTION PURGE")
cutoff = now - (RETENTION_DAYS * SECONDS_PER_DAY)
purged = purge_deleted_before(connection, cutoff)
purge_restore_rows = restore_ticket(connection, 4)
print(f"Purged tickets: {purged}")
print(f"Restore attempts after purge changed rows: {purge_restore_rows}")
assert purged == 1
assert not ticket_exists(connection, 4)
assert purge_restore_rows == 0
print("\nAll lifecycle checks passed.")
finally:
connection.close()
if __name__ == "__main__":
run_demo()How should I use this reference?
Compare the reference with your saved file from top to bottom. Pay close attention to the retention constants, ticket 4 seed data, purge predicate, and final assertions.
Document the deletion design
The passing script proves behavior. The Markdown design document explains why each policy exists.
Your existing SYSTEM_DESIGN.md contains the safe-query and restoration notes from earlier. You will replace it with a complete system design that another engineer can review.
- Switch back to SYSTEM_DESIGN.md in Visual Studio Code.
- Select all existing content with Cmd+A (macOS) or Ctrl+A (Windows).
- Replace the selection with this architecture section:
# Support Ticket Deletion Lifecycle
## Architecture
```text
Support workflow or API
|
v
Ticket lifecycle functions
| | | |
hard soft restore purge
| | | |
v
SQLite database
| |
tickets ticket_comments
|__________|
foreign key with ON DELETE CASCADE
```What does the architecture show?
The lifecycle functions form the policy boundary between a support workflow and the database. Every deletion outcome passes through that boundary.
The lower relationship highlights that tickets own dependent comments through the foreign key.
- Append this lifecycle section below the architecture diagram:
## Lifecycle
```text
restore
+--------------+
| |
v |
ACTIVE --soft delete--> SOFT-DELETED
| |
| hard delete | retention expires and purge runs
v v
HARD-DELETED HARD-DELETED
```
`ACTIVE` means `deleted_at IS NULL`; `SOFT-DELETED` means `deleted_at IS NOT NULL`; `HARD-DELETED` means the row no longer exists in the primary database.How should the lifecycle be read?
An active ticket can move directly to permanent deletion. It can also enter a recoverable soft-deleted state.
A soft-deleted ticket can return through restoration. It becomes permanently unavailable after retention expires and the purge runs.
- Save SYSTEM_DESIGN.md.
- Select the preview icon in the top-right of the editor to render the two diagrams.
You should see the architecture flow from the support workflow to both tables. The lifecycle diagram should show restoration and permanent deletion as separate outcomes.
- Return to the SYSTEM_DESIGN.md editor.
- Append the policy sections and decision matrix below the lifecycle explanation:
## Data model and query safety
- `tickets.id` is the parent primary key.
- `ticket_comments.ticket_id` references the parent with `ON DELETE CASCADE`.
- Hard-deleting a ticket deletes dependent comments; soft deletion preserves both ticket and comments.
- Normal reads must include `WHERE deleted_at IS NULL`.
- Unrestricted reads are reserved for administration, audits, migration, and tests.
- Counts, joins, exports, caches, search indexes, and background jobs must follow the same lifecycle policy.
## Index and uniqueness policy
- `ticket_number` is globally unique and is never reused after deletion.
- `idx_tickets_active_customer` is partial and matches active-ticket lookup semantics.
- Active-only uniqueness can permit identifier reuse, but restoration then needs a conflict policy.
## Decision matrix
| Strategy | Choose it when | Main benefit | Main risk |
| --- | --- | --- | --- |
| Hard delete | Data is disposable or must leave the primary dataset immediately | Simple reads and no retained row | No application-level restore; foreign-key actions may remove related rows |
| Soft delete | Recovery, review, or audit context is required | Reversible state transition | Every read path and aggregate must filter lifecycle state |
| Hybrid | Recovery is needed for a defined period before removal | Bounded retention with recovery | Requires purge monitoring and restore-conflict policy |Why document these policies together?
The data model controls related-record behavior. The query contract controls which lifecycle states normal users can see.
The index and uniqueness rules must follow those same definitions. The decision matrix then connects each strategy to a product need and operational risk.
- Save SYSTEM_DESIGN.md.
- Return to the preview to confirm the decision matrix renders with three strategy rows.
You should see separate rows for hard delete, soft delete, and the hybrid policy. Each row should include a use case, benefit, and risk.
- Return to the SYSTEM_DESIGN.md editor.
- Append the storage nuance and interview explanation below the decision matrix:
## Storage and erasure nuance
A SQL `DELETE` removes a logical row. SQLite default `auto_vacuum=none` can retain reusable freed pages, and backups or replicas require their own retention policies.
## Interview explanation
Hard delete removes the primary row and can cascade to dependents. Soft delete preserves a row through `deleted_at`, but requires a safe-read contract, storage planning, and uniqueness policy. A hybrid policy allows restoration during a defined retention window, then purges expired rows. The right choice follows recovery promises, audit and privacy requirements, related-record behavior, and purge operations.Why does storage nuance matter?
A logical row deletion does not promise immediate file shrinkage. Backups and replicas also need retention rules if the wider system promises permanent removal.
The interview explanation ties the technical mechanisms to recovery, audit, privacy, and operational requirements.
✔️ Awesome, I've got everything!
Your system design now covers architecture, lifecycle states, query safety, indexing, retention, and trade-offs.
ⓧ I'd like to double check the full code
# Support Ticket Deletion Lifecycle
## Architecture
```text
Support workflow or API
|
v
Ticket lifecycle functions
| | | |
hard soft restore purge
| | | |
v
SQLite database
| |
tickets ticket_comments
|__________|
foreign key with ON DELETE CASCADE
```
## Lifecycle
```text
restore
+--------------+
| |
v |
ACTIVE --soft delete--> SOFT-DELETED
| |
| hard delete | retention expires and purge runs
v v
HARD-DELETED HARD-DELETED
```
`ACTIVE` means `deleted_at IS NULL`; `SOFT-DELETED` means `deleted_at IS NOT NULL`; `HARD-DELETED` means the row no longer exists in the primary database.
## Data model and query safety
- `tickets.id` is the parent primary key.
- `ticket_comments.ticket_id` references the parent with `ON DELETE CASCADE`.
- Hard-deleting a ticket deletes dependent comments; soft deletion preserves both ticket and comments.
- Normal reads must include `WHERE deleted_at IS NULL`.
- Unrestricted reads are reserved for administration, audits, migration, and tests.
- Counts, joins, exports, caches, search indexes, and background jobs must follow the same lifecycle policy.
## Index and uniqueness policy
- `ticket_number` is globally unique and is never reused after deletion.
- `idx_tickets_active_customer` is partial and matches active-ticket lookup semantics.
- Active-only uniqueness can permit identifier reuse, but restoration then needs a conflict policy.
## Decision matrix
| Strategy | Choose it when | Main benefit | Main risk |
| --- | --- | --- | --- |
| Hard delete | Data is disposable or must leave the primary dataset immediately | Simple reads and no retained row | No application-level restore; foreign-key actions may remove related rows |
| Soft delete | Recovery, review, or audit context is required | Reversible state transition | Every read path and aggregate must filter lifecycle state |
| Hybrid | Recovery is needed for a defined period before removal | Bounded retention with recovery | Requires purge monitoring and restore-conflict policy |
## Storage and erasure nuance
A SQL `DELETE` removes a logical row. SQLite default `auto_vacuum=none` can retain reusable freed pages, and backups or replicas require their own retention policies.
## Interview explanation
Hard delete removes the primary row and can cascade to dependents. Soft delete preserves a row through `deleted_at`, but requires a safe-read contract, storage planning, and uniqueness policy. A hybrid policy allows restoration during a defined retention window, then purges expired rows. The right choice follows recovery promises, audit and privacy requirements, related-record behavior, and purge operations.How should I compare the document?
Check each heading in order. Confirm that both diagrams remain inside text fences and that the decision matrix contains three strategy rows.
Before the final preview, which lifecycle paths do you expect to end with no row in the primary database?
- Save SYSTEM_DESIGN.md.
- Select the preview icon in the top-right of the editor to inspect the completed document.
- Scroll through the preview to confirm both diagrams and the decision matrix render correctly.
You should see the architecture diagram connect the lifecycle functions to the two tables. You should also see the lifecycle diagram route hard deletion and expired retention to HARD-DELETED.
Your decision matrix should compare all three strategies. The interview explanation should close the document beneath the storage section.
Does the Markdown preview look broken?
- Check that each diagram starts and ends with three backticks.
- Check that every decision-matrix row starts and ends with a pipe character.
- Check that each level-two heading begins with exactly two hash characters.
Need help fixing the layout? Help me debug the Markdown diagrams or decision matrix.
Your deletion lifecycle lab now proves every transition it documents. You can run it, inspect its policy boundaries, and defend its trade-offs.
Secret mission
Fix a Restore Collision in Production
A deleted ticket releases an active-only business reference. Reproduce the restore collision that follows when a replacement ticket claims that reference, then turn the database failure into an explicit conflict policy.
Clean Up Your Resources
Clean Up Your Resources
All of your work remains local on your Mac, so there are no cloud resources or ongoing costs. Choose whether to keep the lab available, pause your work, or remove the project files.
Resources you used:
- Your lifecycle script at soft-delete-lab/ticket_lab.py.
- Your system design document at soft-delete-lab/SYSTEM_DESIGN.md.
- Your local SQLite database at soft-delete-lab/ticket_lab.db.
- Your restore-collision script at soft-delete-lab/production_bug.py.
Keep everything running
Keeping the lab preserves your runnable demonstrations and system design notes. Choose this option if you want to practice explaining the deletion lifecycle again.
- Leave the soft-delete-lab folder on your Desktop.
- Return to the workspace in Visual Studio Code when you want to rerun the lifecycle demonstration.
- Review SYSTEM_DESIGN.md when you want to rehearse your interview explanation.
Pause - I'll come back to this later
Pausing ends your editing session while retaining every local artifact. Neither script creates a persistent background service.
- Close Visual Studio Code.
- Leave the soft-delete-lab folder on your Desktop.
- Return to the saved workspace when you are ready to continue.
Your code, diagrams, and sample database remain available without creating ongoing costs.
Delete - I don't want to use this again
Moving the project folder to Trash can feel final. This cleanup affects only the local soft-delete-lab folder.
- Switch to Finder.
- Select Desktop in the sidebar.
- Select the soft-delete-lab folder.
- Right-click the selected folder.
- Select Move to Trash.
You should see soft-delete-lab in Trash. The folder contains ticket_lab.py, SYSTEM_DESIGN.md, ticket_lab.db, and production_bug.py.
Nice Work!
Nice Work!
You completed a production-ready support-ticket lifecycle lab backed by SQLite. You can now run the lifecycle checks to defend each deletion decision with observable evidence.
You've learned how to:
- Demonstrate how a hard delete permanently removes a ticket. You also proved that its dependent comments disappear through a foreign-key cascade.
- Implement soft delete as a reversible lifecycle state. You exposed an unsafe-query leak before enforcing a safe active-record boundary.
- Apply a 30-day retention policy that permanently purges expired tickets. You documented the architecture with lifecycle diagrams plus an interview-ready decision matrix.
- Completed the Secret Mission by reproducing a restore collision caused by an active-only unique index. You replaced the failing restore with an explicit conflict-aware policy that preserves ticket 5 as the active owner.
Ready to quiz yourself?