Project 6: Safe SQL Incident Investigation Runbook

Build a training-only, read-only Livebook runbook that accelerates SQL diagnosis while making its authority, bounds, redaction, and evidence impossible to miss.

Quick Reference

Attribute	Value
Main language	Elixir with SQL
Training database	PostgreSQL preferred; SQLite may be used for an initial fixture
Primary tools	Livebook, KinoDB, Postgrex
Difficulty	Level 2 — Intermediate
Coolness	Level 3 — Very Cool
Business potential	Level 3 — Strong internal product
Estimated time	12–20 hours
Safety classification	TRAINING ONLY — READ ONLY
Observable deliverables	Allowlisted query catalog, bounded UI, redacted transcript, failure-test report

Hard safety boundary: This project must use a disposable training database and a database role with no mutation privileges. The notebook must not contain a mutation query, administrative query, schema-changing path, arbitrary SQL console, production credential, or production connection. A UI label is not a security control.

1. Learning Objectives

By the end of this project, you will be able to:

Separate a friendly notebook interface from the database identity and privileges that enforce safety.
Use a small allowlisted query catalog instead of accepting arbitrary SQL text.
Bind user-supplied values as parameters rather than interpolating them into SQL.
Apply time-window, row-return, execution-time, and workload bounds at multiple layers.
Design result schemas that redact or aggregate sensitive fields by construction.
Distinguish an empty result, a rejected request, a timeout, a connection failure, stale data, and a partial result.
Produce an audit transcript containing query identities and redacted parameter summaries without leaking secrets or PII.
Threat-model operator mistakes, malicious input, credential compromise, and a slow or overloaded database.

The goal is not to create a universal database console. The goal is to encode a narrow investigative method whose safe operating envelope is inspectable and testable.

2. Theoretical Foundation

2.1 Core Concepts

Parameterized SQL. A parameterized query sends statement structure and values separately through the database protocol. A hostile value is then interpreted as data for a placeholder, not as new SQL syntax. Parameterization protects the structure of a known query, but it does not make an expensive or over-privileged query safe. Table names, sort directions, and SQL fragments generally cannot be treated as ordinary value parameters; select such structural choices from an allowlist.

Least privilege. The database role should possess only the capabilities required by the runbook: connect to the training database, use the intended schema, and select from an intentionally limited set of views or tables. Deny create, alter, drop, insert, update, delete, truncate, sequence manipulation, function execution beyond what is required, and broad access to system or sensitive schemas.

Read-only transactions. PostgreSQL can mark a transaction READ ONLY, which blocks many data-changing commands. This is useful defense in depth, not the sole boundary. PostgreSQL’s own documentation explains that read-only mode is a high-level SQL restriction rather than a claim that the database writes no bytes internally. More importantly, an overly privileged role plus a mistaken transaction setup is still dangerous. Combine restricted role grants, an explicitly read-only transaction, a dedicated training database, and a query allowlist.

Bounded investigation. A returned-row LIMIT controls result cardinality but may not control how much data the database scans, sorts, joins, or aggregates first. A safe runbook also restricts time range, permitted predicates, query timeout, concurrency, and—where the database and training setup allow—statement cost or scanned partitions. Query-plan inspection can inform a budget, but never expose an unrestricted EXPLAIN ANALYZE path against production in this lab.

Evidence and redaction. A useful transcript identifies what was asked and what happened: runbook name, training environment, role, transaction mode, query catalog ID/version, redacted parameter summary, bounds, duration, result count, status, and data freshness. It should not contain connection strings, secret values, raw customer identifiers, full free-text payloads, or unrestricted result exports.

2.2 Why This Matters

During an incident, people trade deliberation for speed. A notebook can help by presenting approved diagnostic questions, documenting context, and preserving evidence. It can also become an unbounded shell connected with excessive authority. The latter failure is especially likely when a polished control panel is mistaken for an authorization boundary.

This project makes the safe path the easiest path. The operator selects an incident question such as “which invoice-sync jobs exceeded the expected delay within this bounded window?” The runbook chooses a reviewed query, validates parameters, opens an explicitly read-only transaction using a read-only training role, applies time and row limits, renders aggregated or redacted results, and appends an audit event. Invalid, overly broad, stale, or failed requests remain visibly different from valid empty results.

The design applies beyond SQL: operational tools should encode narrow capabilities, fail closed, constrain workload, minimize data exposure, and record evidence without recording secrets.

2.3 Historical Context / Background

Operational troubleshooting once relied heavily on shell history, ad hoc SQL consoles, and personal query snippets. These tools were fast but difficult to review, reproduce, or constrain. Runbooks improved repeatability by documenting sequences; executable runbooks go further by connecting procedures to live evidence. The risk is that executable documentation inherits the authority of its runtime and credentials.

Database protocols and libraries have long supported prepared or extended-query execution where parameters travel separately from statement text. Role-based access control and read-only transactions add independent layers. Modern notebook systems add interactive controls and collaborative narratives, but they do not remove the need for database-native controls. Livebook explicitly executes Elixir code with the authority of its runtime, so anyone with notebook authoring access must be treated as able to exercise that authority. This is why this project stays on an isolated training database.

2.4 Common Misconceptions

“The button says read-only, so the runbook is safe.” Safety comes from database privileges, transaction mode, isolation, and query design.
“Parameterized SQL makes every query safe.” It prevents values from becoming syntax; it does not prevent expensive plans or overbroad data access.
“LIMIT 500 means the database touches at most 500 rows.” The plan may scan or sort far more before returning 500.
“A read-only transaction means the system performs no writes.” It is a SQL-level restriction, not a physical-I/O guarantee.
“Masking values in the UI prevents leakage.” Raw values can still leak through exports, logs, errors, or the audit transcript.
“An empty table means there were no incidents.” It may also mean a failed query, stale replica, wrong time zone, or rejected request.
“Attaching to a more powerful runtime is harmless.” Runtime authority expands what notebook code can do; use a standalone constrained setup.

3. Project Specification

3.1 What You Will Build

Create a Livebook notebook named delayed_invoice_sync that investigates a seeded incident in a disposable training database. It must provide:

A conspicuous TRAINING environment and READ ONLY authority panel.
A catalog of reviewed diagnostic questions identified by stable query IDs and versions.
Typed controls for a bounded UTC time window, service/tenant fixture selector, severity, and maximum returned rows.
Parameterized execution through KinoDB or Postgrex without string interpolation.
Database-role and read-only-transaction verification before every diagnostic execution.
A preflight workload estimate or bounded-plan check appropriate to the training schema.
A hard timeout and maximum result count.
Aggregated and redacted evidence views.
Explicit ready, empty, rejected, timed-out, failed, stale, and partial states.
An exportable audit transcript containing query IDs and redacted parameters but no secrets or direct identifiers.

The notebook must expose no free-form SQL cell for operators. Authors may inspect schema during construction, but the submitted artifact must offer only the cataloged investigative paths.

3.2 Functional Requirements

Connection and authority

Connect only to a disposable training database.
Use a dedicated support_readonly role or equivalent restricted identity.
Verify the current database, role, and transaction read-only status.
Refuse execution if any authority assertion differs from the configured training contract.

Query catalog

Store stable IDs, descriptions, allowed parameter types/ranges, maximum window, timeout, maximum rows, output classification, and SQL-template checksum.
Select structural variants such as sort order or grouping only through enums mapped to reviewed query definitions.
Never accept table names, column lists, predicates, or SQL fragments from a text input.

Bounds

Enforce a maximum time window before database execution.
Include time predicates in every incident query.
Apply returned-row limits and a statement timeout.
Bound concurrency to one active request per notebook client unless you explicitly design cancellation.
Reject a request whose preflight budget exceeds the training threshold.

Privacy and evidence

Query redacted views or select only permitted columns.
Hash, truncate, bucket, or aggregate direct identifiers according to the declared training policy.
Prevent raw result export; export only the bounded redacted report and transcript.
Record query catalog ID/version, not raw SQL, unless the SQL is itself public training content and contains no dynamic values.

3.3 Non-Functional Requirements

Mutation capability must be absent at the database privilege layer.
Secret values must never be rendered, logged, interpolated into error messages, or exported.
Every execution must have a terminal status and request identifier.
The notebook must fail closed on role mismatch, transaction-mode mismatch, unknown query ID, invalid parameter, excessive window, or stale source.
Result rendering must remain bounded in rows and columns.
Audit records must be deterministic enough to compare while retaining unique request identity.
A fresh runtime must reproduce the seeded incident and the same diagnostic conclusions.
The notebook must not connect to production, a production replica, or any non-training endpoint.

3.4 Example Usage / Output

The final evidence panel should resemble:

RUNBOOK: delayed_invoice_sync
Environment: TRAINING
Database role: support_readonly
Transaction mode: READ ONLY
Window: 2026-07-15 12:00Z..13:00Z
Rows scanned estimate: 82,410
Rows returned: 214 / limit 500
Query duration: 184 ms
PII redaction: PASS
Mutation capability: NONE

A query attempt with an injection-shaped parameter must remain an ordinary value:

request_id: trn-0042
query_id: invoice_sync_by_tenant_v2
tenant_filter: [REDACTED, length=24]
parameter_binding: PASS
statement_structure_changed: false
status: EMPTY

An excessive request should stop before query execution:

request_id: trn-0043
requested_window: 31 days
maximum_window: 24 hours
database_query_sent: false
status: REJECTED_BOUND

3.5 Real World Outcome

A teammate can use the runbook to diagnose the seeded delay, explain which bounded questions were asked, distinguish absence of matching records from query failure, and export a safe transcript. A reviewer can verify that:

no mutation path exists in the notebook or the role;
values are bound separately from SQL structure;
expensive or broad requests are rejected;
displayed and exported data follow the same redaction policy;
query results and failure states remain distinguishable;
the evidence identifies the training environment and authority actually in effect.

The durable outcome is a reusable pattern for constrained operational investigation, not permission to point the notebook at a real system.

4. Solution Architecture

4.1 High-Level Design

 +-------------------- Livebook client ----------------------+
 | Incident question selector                               |
 | Typed parameters: UTC window, fixture tenant, severity   |
 +--------------------------+-------------------------------+
                            |
                            v
 +----------------------------------------------------------+
 | Request validator                                        |
 | known query id? types? max window? max rows? freshness?   |
 +--------------------------+-------------------------------+
                            | valid
                            v
 +----------------------------------------------------------+
 | Reviewed query catalog                                   |
 | id + version + SQL checksum + bounds + output policy      |
 +--------------------------+-------------------------------+
                            |
                            v
 +----------------------------------------------------------+
 | Safety gate                                               |
 | endpoint=TRAINING? role=support_readonly? tx=READ ONLY?   |
 | preflight within budget? timeout installed?               |
 +--------------------------+-------------------------------+
                            | all pass
                            v
 +---------------- PostgreSQL training database ------------+
 | restricted role -> redacted views -> seeded incident data |
 | values bound separately through extended query protocol   |
 +--------------------------+-------------------------------+
                            |
               +------------+-------------+
               |                          |
               v                          v
 +---------------------------+  +---------------------------+
 | Bounded redacted results  |  | Audit event               |
 | summary + anomaly chart   |  | ids + bounds + status     |
 +---------------------------+  | redacted params, no secret|
                                +---------------------------+

 Any failed gate --> explicit REJECTED/FAILED state; no query sent

4.2 Key Components

Component	Responsibility	Must not do
Environment banner	Display resolved training endpoint identity	Infer safety from a user-selected label
Query catalog	Map stable IDs to reviewed statements and policies	Accept arbitrary SQL fragments
Parameter validator	Parse, normalize, and bound typed values	Concatenate values into SQL
Authority verifier	Confirm role, database, and read-only transaction	Trust configuration without querying actual state
Budget gate	Enforce window, plan/work estimate, timeout, and row cap	Treat returned `LIMIT` as the only cost control
Executor	Bind parameters and run one catalog query	Reuse a privileged application connection
Redaction projector	Return only approved columns/aggregates	Fetch raw PII and hide it only with CSS
State renderer	Distinguish empty, rejected, timeout, stale, partial, failure	Convert every non-success into an empty table
Transcript builder	Record safe evidence	Record credentials, raw identifiers, or unrestricted results

4.3 Data Structures

QueryDefinition
  id: stable string
  version: positive integer
  purpose: text
  statement_checksum: sha256
  parameter_schema: list of typed bounded parameters
  max_window_seconds: integer
  max_rows: integer
  timeout_ms: integer
  output_columns: list of approved classified fields
  freshness_requirement_seconds: integer

RunbookRequest
  request_id
  query_id + query_version
  normalized UTC bounds
  validated fixture selectors
  requested result limit <= catalog max

ExecutionEvidence
  environment identity
  database and role identity
  transaction mode
  preflight estimate/budget status
  started/finished timestamps and duration
  rows returned and truncation flag
  source freshness
  terminal status
  redacted parameter summary

Classify output fields as aggregate, operational_non_sensitive, pseudonymous, or forbidden. The catalog should not include a forbidden field at all.

4.4 Algorithm Overview

receive typed control event
resolve query definition by exact id/version
normalize values and validate all bounds
reject unknown structural choices
open dedicated training connection
begin explicitly read-only transaction
verify database, role, and transaction mode
run bounded preflight appropriate to fixture
reject if estimated work exceeds threshold
execute reviewed statement with separately bound values and timeout
project approved columns and cap rows
classify result: ready / empty / partial / timeout / failed / stale
render redacted evidence
append redacted audit event
close transaction and connection cleanly

Catalog lookup and parameter validation are O(p) for p parameters. Database complexity depends on the reviewed statement and indexes; the lab should design time-bounded queries to use an indexed timestamp range, approximately O(log n + k) for index positioning plus matching rows k, while recognizing that joins and aggregates may cost more. Rendering and transcript creation must be O(r) where r is capped by the catalog.

5. Implementation Guide

5.1 Development Environment Setup

Start an isolated PostgreSQL training instance or disposable database.
Load synthetic invoice-sync events containing a known delay incident, known empty cases, pseudonymous fixture tenants, and no real customer data.
Create restricted reporting views that omit or transform direct identifiers.
Create a login role such as support_readonly with only connect, schema usage, and select privileges required for those views.
Set a safe default transaction mode and timeout for that training role where appropriate, while still asserting them per request.
Configure Livebook with a training-only secret reference. Never display the resolved value.
Verify the role cannot mutate using a controlled negative test against a disposable table owned by another setup role.

Expected setup evidence:

database_identity: livebook_runbook_training
role_identity: support_readonly
approved_views_visible: 3
base_tables_visible: policy-defined
mutation_negative_test: DENIED
secret_value_rendered: false

Do not reuse a developer superuser, application owner, cloud administrator, or attached application runtime.

5.2 Project Structure

— TRAINING ONLY / READ ONLY safety statement
— Threat model and authority assumptions
— Dependency and runtime versions
— Secret reference and dedicated connection
— Database/role/transaction verification
— Query catalog and output classifications
— Typed controls and request validation
— Workload preflight and execution bounds
— Parameterized execution
— Result states and anomaly views
— Redacted audit transcript
— Seeded incident walkthrough
— Abuse, failure, and mutation-negative tests
— Fresh-runtime replay report

Keep the catalog and policy in an auditable cell or data section. Generated controls may reference it, but must not redefine authority or SQL ad hoc.

5.3 The Core Question You’re Answering

How can an executable runbook accelerate diagnosis without becoming an unbounded production shell?

Answer by listing each capability the operator genuinely needs and the independent mechanism that constrains it. If a feature requires arbitrary SQL, unrestricted export, production authority, or hidden credentials, it is outside this project’s scope.

5.4 Concepts You Must Understand First

Before implementation, explain:

Why parameter binding protects statement structure but not workload cost.
The difference between a database role, a transaction mode, and a notebook UI permission.
Why a returned-row limit may not bound scanned or sorted data.
How redaction by query/view design differs from post-fetch masking.
Why an empty result and a failed request require different states.
What an audit transcript must include to be useful without becoming a leakage channel.

5.5 Questions to Guide Your Design

Which incident questions are common and stable enough to deserve catalog entries?
Which parameters are values, and which apparent “parameters” are actually SQL structure requiring allowlisted variants?
What maximum time window and timeout fit each question?
How will the runbook determine source freshness and replica lag in the training model?
Which columns are prohibited even for notebook authors?
What is the policy when a query returns exactly the row cap?
How will the operator know whether data is partial, truncated, stale, or complete?
Which actual database query proves the current role and transaction mode?
Where will audit events be stored so that the lab remains read-only with respect to the investigated database? A local in-memory/export artifact is acceptable; writing an audit row into the training database through this role is not.

5.6 Thinking Exercise

Threat-model four actors and trace the controls that stop them:

A well-meaning operator selects a 90-day window during an incident.
A malicious input contains quotes, comments, and a second SQL statement.
The training credential is copied outside the notebook.
An author attaches the notebook to a more privileged runtime.

For each actor, identify prevention, detection, evidence, and residual risk. Then analyze a fifth case: the query returns zero rows because the seeded data refresh is stale. Which state should the operator see, and why is EMPTY dishonest?

5.7 The Interview Questions They’ll Ask

What problem do prepared or parameterized statements solve, and what do they not solve?
How would you enforce read-only behavior for an incident-analysis tool?
Why can a query with LIMIT 100 still overload a database?
How would you prevent sensitive data from leaking through errors and exports?
What states should a diagnostic dashboard distinguish?
How would you version and review an allowlisted query catalog?

5.8 Hints in Layers

Hint 1 — Start from questions, not SQL. Define “show delayed jobs for one bounded window” as a catalog capability.

Hint 2 — Bind every value. Map structural choices to reviewed query IDs; never concatenate a sort column, relation, predicate, or direction from user text.

Hint 3 — Bound twice or more. Validate the requested window in Elixir, encode the same bounded predicate in SQL, set a timeout, and cap rendered rows.

Hint 4 — Redact before data crosses the connection. Prefer reporting views or explicit safe projections.

Hint 5 — Verify actual authority. Read the database’s current role and transaction properties inside the same connection/transaction used for the query.

Pseudocode outline:

definition = catalog.fetch_exact(request.query_id)
validated = validate_values(request, definition.parameter_schema)
assert_training_endpoint()
with dedicated_connection:
  begin_read_only_transaction()
  assert_actual_role_and_mode()
  assert_preflight_within_budget(definition, validated)
  result = execute_with_bound_values(definition.statement, validated.values)
  safe_result = enforce_output_contract(result, definition)
emit_redacted_transcript(definition.id, validated.redacted_summary, safe_result.status)

5.9 Books That Will Help

Topic	Book	Suggested focus
PostgreSQL roles and query behavior	PostgreSQL: Up and Running — Regina Obe and Leo Hsu	Roles, permissions, query inspection
Database reliability	Designing Data-Intensive Applications — Martin Kleppmann	Transactions, replication, derived views
Security engineering	Security Engineering — Ross Anderson	Least privilege, audit, threat modeling
Operational response	Seeking SRE — David Blank-Edelman	Incident response and operational tooling

5.10 Implementation Phases

Phase 1 — Threat model and training database (2–4 hours)

Define the safety boundary and prohibited capabilities.
Seed synthetic incident data and create redacted reporting views.
Create the constrained database role and prove mutation denial.

Phase 2 — Query catalog and parameter validation (3–4 hours)

Implement two or three catalog questions.
Define typed bounds and stable IDs/versions.
Test injection-shaped values and invalid structural choices.

Phase 3 — Authority and workload gates (3–5 hours)

Use a dedicated connection and explicitly read-only transaction.
Verify role/mode in the active context.
Add maximum window, timeout, row cap, and training preflight budget.

Phase 4 — States, redaction, and transcript (3–4 hours)

Render bounded summaries and anomaly visuals.
Implement distinct empty/rejected/timeout/failed/stale/partial states.
Export a transcript with redacted parameter metadata.

Phase 5 — Abuse and replay tests (1–3 hours)

Attempt every prohibited path against the training fixture.
Simulate slow, stale, disconnected, and truncated responses.
Reproduce the seeded diagnosis from a fresh runtime.

5.11 Key Implementation Decisions

Decision	Safe default	Reason
Environment	Disposable training database only	Eliminates accidental production scope
Query surface	Versioned allowlist	Prevents arbitrary capability expansion
Connection	Dedicated constrained role	Separates notebook from application authority
Transaction	Explicitly read-only and verified	Adds defense in depth
Parameter handling	Typed values bound separately	Preserves statement structure
Result policy	Safe projection plus hard cap	Minimizes leakage and browser load
Transcript	IDs and redacted summaries	Retains evidence without secrets
Over-budget behavior	Reject before execution	Fails closed

6. Testing Strategy

6.1 Test Categories

The suite covers authority, input structure, operational states, privacy, and workload cost. Each category must prove a boundary independently so that a friendly UI cannot conceal an unsafe role, statement, result, or execution plan.

6.2 Critical Test Cases

Privilege tests

Verify the role can connect only to the training database intended for the lab.
Verify approved selects succeed.
Verify insert, update, delete, truncate, create, alter, and drop attempts are denied against disposable test objects.
Verify the submitted notebook contains no API or control that initiates those attempts.
Verify execution refuses to continue if the actual role differs from support_readonly.

Input and injection tests

Input	Expected behavior
Valid bounded UTC window	Query executes
Window over maximum	Rejected before database call
Injection-shaped tenant value	Treated as one bound value; statement unchanged
Unknown query ID	Rejected
User-supplied sort fragment	Rejected; only known enum variants allowed
Result limit above catalog max	Clamped or rejected according to documented policy

Operational state tests

Known empty fixture → EMPTY, with query success evidence.
Invalid input → REJECTED, with database_query_sent=false.
Deliberately delayed query beyond timeout → TIMED_OUT.
Dropped connection → FAILED_CONNECTION.
Old fixture refresh marker → STALE, not empty or ready.
Forced row cap → PARTIAL or TRUNCATED, never silently complete.
Malformed result contract → FAILED_OUTPUT_CONTRACT.

Privacy tests

Search rendered notebook output, exported transcript, runtime logs, and controlled error text for seeded canary PII and secret canary values.
Confirm neither canary appears.
Confirm prohibited columns never cross the connection by inspecting the reviewed projection and training query logs.

Cost tests

Compare an indexed bounded window with a deliberately broad request.
Demonstrate that a small returned limit can still have a high preflight estimate.
Verify the broad request is rejected before execution.

6.3 Test Data

Use only a disposable, seeded PostgreSQL database containing synthetic tenants, delayed jobs, stale refresh markers, forbidden columns, and unique PII and secret canaries. Include narrow indexed windows, deliberately broad windows, empty results, delayed statements, and dropped connections; never copy production rows into the fixture.

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Problem: A query returns only 500 rows but remains expensive

Why: the database scanned, joined, grouped, or sorted a large population before applying the final limit.
Evidence: preflight plan/estimate and elapsed time exceed the query catalog budget.
Fix: add selective indexed predicates, reduce window, preaggregate a reporting view, and keep the budget gate.
Quick test: compare plans for one-hour and thirty-day requests on the seeded fixture.

Problem: The UI is redacted but the export leaks PII

Why: raw rows were fetched and only the renderer masked them.
Evidence: canary identifier appears in the export or runtime inspection.
Fix: select from a redacted view or explicit safe projection; apply one output contract to UI and export.
Quick test: place a unique canary in a forbidden source column and scan every artifact.

Problem: The notebook says read-only but mutation succeeds

Why: a privileged connection was reused, transaction mode was not applied, or grants were too broad.
Evidence: actual role/mode differs from the banner; negative mutation test succeeds.
Fix: destroy the environment, create a dedicated role, revoke privileges, and fail closed on actual-state mismatch.

Problem: Empty and failed look identical

Why: errors are converted to an empty dataframe.
Evidence: no terminal status, query ID, duration, or freshness evidence accompanies the blank table.
Fix: model result states explicitly and render a distinct panel for each.

Problem: Parameterization test still changes SQL structure

Why: identifiers, ordering, or a predicate fragment were interpolated even though scalar values were bound.
Evidence: statement checksum varies with a user input.
Fix: map structural options to complete reviewed query variants.

Problem: Attached runtime grants unintended authority

Why: the notebook runs inside an application node with broader credentials or network reach.
Evidence: environment and connection identity differ from the standalone training setup.
Fix: use a standalone Livebook runtime and isolated training network/credentials.

7.2 Debugging Strategies

Start by reading actual state from the same connection used for the query: role, database, transaction mode, catalog ID, bound parameters, timeout, freshness, and terminal status. Compare the reviewed statement checksum before inspecting rendered output, and search every controlled artifact for canary data after each failure.

7.3 Performance Traps

Do not equate returned rows with work performed. Broad predicates, sorts before limits, unindexed joins, repeated reactive events, plan inspection that executes the statement, and unrestricted exports can all exceed the workload budget. Validate bounds before the database call and preserve timeout and concurrency caps during debugging.

8. Extensions & Challenges

Only attempt extensions inside the disposable training environment.

8.1 Beginner Extensions

Catalog signing: checksum the reviewed SQL templates and refuse definitions that do not match an approved manifest.
Role-matrix test: create several fixture roles and prove each runbook question’s minimum grants.

8.2 Intermediate Extensions

Replica-lag simulation: model data freshness and require a stale-state decision before interpretation.
Concurrency control: accept multiple UI events but execute at most one bounded database request per client.
Differential privacy discussion: design, but do not overclaim, a policy for aggregate counts with small-group suppression.

8.3 Advanced Extensions

Transcript verifier: independently validate that every execution has authority, bounds, terminal state, and redaction evidence.
Catalog review workflow: add owner, reviewer, expiry date, and change reason to each query definition.
SQLite comparison: explain which protections are application-enforced because SQLite’s role model differs from PostgreSQL.

9. Real-World Connections

9.1 Industry Applications

Support tooling: safe customer-impact diagnosis requires narrow views and pseudonymous identifiers.
SRE runbooks: bounded diagnostic questions improve repeatability during incidents.
Security engineering: least privilege and defense in depth matter more than cosmetic UI restrictions.
Data governance: field classification should control query projection, rendering, logging, and export together.
Platform engineering: query catalogs can become reviewed operational capabilities exposed through a service.
Compliance: audit evidence is useful only when it is complete, tamper-aware, and free of unnecessary sensitive data.

PostgreSQL and Postgrex provide the database and Elixir protocol layers; KinoDB supplies notebook-oriented connection and query components; OWASP guidance provides a useful external control checklist. Study how tools such as Metabase and Superset constrain saved questions, but do not assume their deployment controls automatically exist in Livebook.

9.3 Interview Relevance

A production-grade version would normally move execution behind an authenticated service with centralized authorization, network policy, query review, immutable audit storage, rate limiting, and independent monitoring. This training notebook is a design laboratory, not a production deployment shortcut.

10. Resources

10.1 Essential Reading

SQL Antipatterns by Bill Karwin — query construction and data-access failure modes.
Database Reliability Engineering by Laine Campbell and Charity Majors — operational boundaries, observability, and safe change.

10.2 Video Resources

Use current PostgreSQL security and query-planning conference sessions as supplements. Reproduce every claim against the disposable fixture and the documented role matrix rather than trusting a demo’s environment.

10.3 Tools & Documentation

Official documentation

Security references

Use the current PostgreSQL and package documentation for your chosen versions. Do not copy role or timeout assumptions from another environment.

11. Self-Assessment Checklist

11.1 Understanding

I can explain why this lab is restricted to a disposable training database.
I can prove the actual database role and transaction mode in use.
I can explain what parameterization protects and what it does not.
I can show that structural query choices come only from a reviewed allowlist.
I can demonstrate at least four independent workload bounds.

11.2 Implementation

I can prove mutation attempts are denied by database privileges.
I can distinguish ready, empty, rejected, timeout, failed, stale, and partial states.
I can show that PII and secret canaries are absent from UI, errors, logs, and exports.
I can explain why LIMIT alone is not a scan budget.
I can reproduce the seeded incident and audit transcript from a fresh runtime.
I have not connected the notebook to production or a privileged application runtime.

11.3 Growth

I can add a reviewed catalog query without expanding the database role’s privileges.
I can explain which controls must move behind an authenticated production service.

12. Submission / Completion Criteria

Submit the notebook, synthetic seed description, role/grant definition as a non-runnable reviewed configuration transcript or separate training setup artifact, query-catalog manifest, redacted example transcript, and test report.

The submission is complete when:

This expansion belongs to Livebook + Elixir Deep Dive. Return to the expanded project index for the rest of the sprint.