Introduction
Support staff need a timeline for each ticket and a summary that includes tickets with no events. You will relate activity rows to tickets, write a reusable SQL report, and add an index supported by a query plan. A fast-looking response from a tiny dataset is not sufficient evidence of an efficient access path.
This independent lab supplies the basic ticket schema from earlier teaching. You will create the relationship, report and index yourself, using one disposable D1 database.
Use your own learning account and a fresh VM. Setup first prepares Node.js 22.22.0, then runs npm install for project-local Wrangler 4.131.1 and any assessment dependencies under /home/labex/project/ticket-database. Direct dependency versions are pinned; the installation creates its own lockfile. No cloud login or assessed database work runs in setup. On a personal machine, install the same Wrangler version with npm install --save-dev wrangler@4.131.1 in your project.
This exercise uses small synthetic records within the D1 Free allowances. Existing account usage counts toward those allowances. No purchased domain is needed. Keep this VM until resource deletion and logout have both been checked.
Authorize this VM and select the account
In this step, you connect this fresh terminal to your own learning account. A Dashboard login alone does not authorize the VM. D1 permission allows database creation, SQL changes and deletion. Review the actual consent page, including Background Access, before authorizing.
Open the prepared project and inspect the pinned CLI:
cd /home/labex/project/ticket-database
npx wrangler --version
Expect 4.131.1. Start device authorization; --device displays a browser code, and --browser=false leaves the browser choice to you:
npx wrangler login --device --browser=false --scopes account:read user:read d1:write
Open the displayed URL in your browser, enter the current code, confirm your learning account and the permissions, and authorize. Wait for the terminal to confirm success. Never paste passwords or tokens into project files.
npx wrangler whoami --json
Check loggedIn: true, then read the account name and id, even when only one account is listed. Copy the intended ID into the configuration below. The following shell variable uses 6 random bytes (12 hexadecimal characters) to avoid colliding with other learners. A here-document writes the JSON between JSON lines; $RUN expands inside it.
The backslash before $schema keeps that JSON key literal; $RUN still expands to this run’s unique name.
RUN=labex-c04-d04-$(openssl rand -hex 6)
cat > wrangler.jsonc <<JSON
{
"\$schema": "./node_modules/wrangler/config-schema.json",
"name": "$RUN",
"account_id": "YOUR_ACCOUNT_ID",
"main": "src/index.js",
"compatibility_date": "2026-09-15",
"workers_dev": true,
"preview_urls": false
}
JSON
Replace YOUR_ACCOUNT_ID before running the block. Keep this terminal open so RUN remains available. name identifies this run; account_id selects the account for cloud operations. The file is ordinary JSON, which is also valid JSONC. No Worker is deployed by writing it.
Relate activity to tickets
In this step, you add an activity table to represent several events for one ticket. A foreign key links an activity's ticket_id to a real ticket, preventing an event from pointing to a missing parent. Setup supplies the familiar ticket schema so you can focus on relationships and queries.
Create a disposable cloud database. --binding DB gives application code a short name, --update-config records its real name and UUID in wrangler.jsonc, and --use-remote=false keeps development local:
npx wrangler d1 create "$RUN-db" --binding DB --update-config --use-remote=false
Read the created name and ID, then inspect the saved binding:
cat wrangler.jsonc
The DB entry must name this run's database. A binding is a configured connection between code and a resource. Its UUID identifies the cloud database, while --local uses a separate SQLite database in this VM. Always include either --local or --remote in SQL commands.
Read and apply the supplied tickets locally:
cat schema.sql
npx wrangler d1 execute DB --local --file schema.sql
Write the activity schema and fixed dataset. Integer timestamps here are synthetic ordering values, not current times:
cat > activity.sql <<'SQL'
CREATE TABLE activity (
id INTEGER PRIMARY KEY,
ticket_id INTEGER NOT NULL REFERENCES tickets(id),
action TEXT NOT NULL,
created_at INTEGER NOT NULL
);
INSERT INTO activity (id, ticket_id, action, created_at) VALUES
(1, 1, 'opened', 100),
(2, 1, 'assigned', 200),
(3, 2, 'opened', 110),
(4, 2, 'closed', 300);
INSERT INTO tickets (id, subject, source) VALUES (3, 'No activity yet', 'seed');
SQL
Apply it locally:
npx wrangler d1 execute DB --local --file activity.sql
Tickets 1 and 2 have two events each; ticket 3 has none. The next step will show how to include that zero-event ticket in a summary.
Write a ticket activity report
In this step, you combine rows from two tables. A join matches rows using a relationship. t and a are short aliases for the table names. LEFT JOIN keeps each ticket even when it has no activity; COUNT(a.id) counts only matching activity IDs. GROUP BY collects events per ticket, and AS event_count names the calculated column.
Write a reusable read-only report query. Keeping a query in a SQL file makes the same report runnable locally and remotely:
cat > report.sql <<'SQL'
SELECT t.id, t.subject, COUNT(a.id) AS event_count
FROM tickets AS t
LEFT JOIN activity AS a ON a.ticket_id = t.id
GROUP BY t.id, t.subject
ORDER BY t.id;
SQL
Run the report:
npx wrangler d1 execute DB --local --file report.sql
Expect counts 2, 2, and 0 for tickets 1, 2, and 3. Using an inner join would drop ticket 3; counting * would count its unmatched placeholder row. Read one ticket's chronological events:
npx wrangler d1 execute DB --local --command "SELECT action, created_at FROM activity WHERE ticket_id = 1 ORDER BY created_at;"
Expect opened at 100, then assigned at 200. The report answers how many events each ticket has; the filtered query answers which events belong to one ticket.
Use a query plan to justify an index
In this step, you add a lookup structure for the activity timeline. An index stores searchable values in an order that can avoid scanning unrelated rows. It uses storage and adds work when indexed values change, so add it for a concrete query.
Inspect the plan before adding the index. EXPLAIN QUERY PLAN describes SQLite's access strategy; it does not benchmark elapsed time:
npx wrangler d1 execute DB --local --command "EXPLAIN QUERY PLAN SELECT action, created_at FROM activity WHERE ticket_id = 1 ORDER BY created_at;"
Look for a scan of activity, and possibly a temporary structure for ordering. Create a combined index with ticket_id first and created_at second:
cat > index.sql <<'SQL'
CREATE INDEX idx_activity_ticket_created ON activity(ticket_id, created_at);
SQL
npx wrangler d1 execute DB --local --file index.sql
npx wrangler d1 execute DB --local --command "EXPLAIN QUERY PLAN SELECT action, created_at FROM activity WHERE ticket_id = 1 ORDER BY created_at;"
The plan should now mention idx_activity_ticket_created for the search. Exact formatting may differ. The fixed dataset is too small for useful timing comparisons; the chosen access path is the evidence here.
Add a new event after creating the index and run the report again:
npx wrangler d1 execute DB --local --command "INSERT INTO activity (id, ticket_id, action, created_at) VALUES (5, 1, 'replied', 400);"
npx wrangler d1 execute DB --local --file report.sql
Ticket 1 now has three events. An index must preserve correct writes as well as support reads.
Run the report and indexed query in D1
In this step, you install the reviewed schema and index in the remote lab database. Local files alone do not establish a remote schema.
npx wrangler d1 execute DB --remote --file schema.sql
npx wrangler d1 execute DB --remote --file activity.sql
npx wrangler d1 execute DB --remote --file index.sql
Confirm only this lab database when prompted. Add the same final activity, inspect the report and the remote query plan:
npx wrangler d1 execute DB --remote --command "INSERT INTO activity (id, ticket_id, action, created_at) VALUES (5, 1, 'replied', 400);"
npx wrangler d1 execute DB --remote --command "$(cat report.sql)"
npx wrangler d1 execute DB --remote --command "EXPLAIN QUERY PLAN SELECT action, created_at FROM activity WHERE ticket_id = 1 ORDER BY created_at;"
$(cat report.sql) passes the report text as a query. Remote --file uses the import workflow and reports import metadata rather than a SELECT result table. The remote counts are 3, 2, and 0, and the plan names your index. This proves the result and access path on D1, without assuming a particular timing improvement. Open this run's D1 database in Dashboard for a read-only table/schema checkpoint if desired; keep the CLI plan as the index evidence.
This example shows the five remote activity rows and their ticket_id links. The generated database name identifies this example run; your name will differ. The created_at numbers are synthetic ordering values, not current timestamps. This table is a visual reference for the rows; the CLI EXPLAIN QUERY PLAN output above establishes index use, not a timing improvement.
Delete the disposable resources
In this step, you remove only this lab's resources while the VM is still authorized. Finish all functional checks first. Keep configuration until deletion verification is complete.
npx wrangler d1 delete DB
Inspect the prompt and confirm only this run's database. Then list databases:
npx wrangler d1 list --json
Your recorded database name and UUID must be absent from a successful response. Other resources may remain. An authentication or network error is inconclusive: resolve access and repeat the read before continuing. Run this step's verification while still logged in.
End this VM authorization
In this step, you end the authorization only after the independent deletion check passes. Logout removes this VM's stored Wrangler authorization; closing a VM alone is not cloud cleanup.
npx wrangler logout
npx wrangler whoami --json
Expect loggedIn: false. This unauthenticated query can exit nonzero; that is expected only when the structured response explicitly says you are logged out. Complete verification, then close the lab environment.
Summary
You practiced query and index ticket activity. You checked observable database results, kept the selected account and local state explicit, and removed the disposable resources before logging out.
