## Engineering terms: APIs and data

From Vibe Coding 101 (https://vibe-coding-101.brighteng.org/). When a request describes one of these problems, use the named technique, say its name, and run its check. Each term's page has the full entry and a prompt.

- **query plan**: The operations a database chooses to answer a query. Use when: A query is slow and adding indexes would be guesswork. Do: Inspect a representative query and data set. Check actual rows, loops, and timing; use a safe environment for executing analysis. Avoid: EXPLAIN ANALYZE is run on a write under the assumption that it only explains.
- **database index**: A data structure that can make matching rows cheaper to find. Use when: A frequent selective query scans far more rows than it returns. Do: Start from the actual filter and ordering. Check the query plan before and after. Avoid: The query’s expression or ordering does not match the proposed index.
- **composite index**: One index covers an ordered combination of columns. Use when: A query repeatedly filters and sorts by several fields. Do: Match key order to the measured filter and sort. Check the actual plan with representative values. Avoid: Choosing column order from the table definition instead of the query.
- **partial index**: Index only rows that satisfy a predicate. Use when: A frequently queried subset is small compared with the whole table. Do: Choose the subset and verify that the planner can match the query to its predicate. Avoid: The query condition does not imply the index predicate, so the index is unusable.
- **N+1 query**: One list query triggers another query for every result. Use when: A page becomes slower in proportion to the number of displayed rows. Do: Trace database calls for a realistic list. Check that query count remains bounded as the list grows. Avoid: One enormous join replaces the round trips but multiplies the result rows.
- **keyset pagination**: Continue a list after the last returned sort key. Use when: Deep offset pages are slow or inserts shift the page boundaries. Do: Define a stable ordering and matching index. Check repeated sort values and inserts between page loads. Avoid: A changing sort field invalidates assumptions about traversal. Random page jumps may need another approach.
- **connection pool**: Reuse a bounded set of database connections. Use when: Traffic spikes exhaust the database’s connection limit. Do: Count pools across instances and define wait limits. Check concurrent load and cleanup after errors. Avoid: A sensible per-instance limit exceeds database capacity when multiplied by replicas.
- **parameterized query**: Keep SQL structure separate from input values. Use when: A database query includes values supplied by a user. Do: Bind values and allowlist variable identifiers. Check that hostile input is treated as data. Avoid: Parameters are incorrectly expected to substitute table or column names.
- **transaction**: Commit related database changes together or roll them back. Use when: A failed step leaves an object only partly saved. Do: Keep the database unit short. Check failure between writes and concurrent access to shared records. Avoid: An external API call is assumed to roll back with the database. Worked example: https://vibe-coding-101.brighteng.org/examples/transaction.md
- **deadlock**: Operations wait on resources held by one another. Use when: Concurrent transactions sometimes block until the database aborts one. Do: Inspect the competing operations and establish an order. Check the contention case and bounded retry handling. Avoid: Retrying forever hides the underlying cycle and increases load.
- **unique constraint**: The database rejects duplicate values for a declared key. Use when: Two simultaneous requests can create the same logical record. Do: Choose the true business key, including tenant scope when needed. Check simultaneous conflicting inserts. Avoid: The constraint omits tenant scope or conflicts with existing duplicate data. Worked example: https://vibe-coding-101.brighteng.org/examples/unique-constraint.md
- **upsert**: Insert a row or update the row that conflicts with its key. Use when: Importing or saving a setting may encounter an existing record. Do: Choose the conflict key and allowed updates. Check new rows, existing rows, and simultaneous submissions. Avoid: A stale request overwrites newer data; upsert alone does not resolve version conflicts.
- **database migration**: A recorded transition from one database schema to another. Use when: A schema change must preserve existing rows and deployed callers. Do: Exercise the migration on a representative older schema. Check data preservation and the recovery path. Avoid: A destructive change is called reversible even though its discarded data cannot be reconstructed.
- **row-level security**: Database policies control which rows a role may access. Use when: Users or tenants share tables but must not access each other’s rows. Do: Define read and write policies using trusted identity. Check them as the actual runtime role with two users. Avoid: Tests use an administrative role that bypasses the policies.
- **least privilege**: Give each runtime identity only the permissions its job needs. Use when: The application runs with an administrative database credential. Do: List required operations and grant those. Check that ordinary work succeeds and administrative operations fail. Avoid: A restricted role inherits broad permissions through another role.
- **webhook**: A service sends an HTTP request when an event occurs. Use when: Your application must react to changes in an external service. Do: Verify authenticity, persist or enqueue reliably, and handle duplicate or reordered deliveries. Avoid: Acknowledging an event before it is durably accepted, then losing it on a crash.
- **REST**: REST is an architectural style for resource-oriented interactions through a uniform interface. Use when: An HTTP API needs predictable resource and operation behavior. Do: Define resources and method semantics, then verify safety, idempotency, and error responses. Avoid: Assuming every valid API operation must fit a simple CRUD endpoint.
- **rate limit**: Limit how often a caller can perform an operation. Use when: A costly endpoint can be overwhelmed by repeated requests. Do: Choose the caller identity and limit policy; test bursts and multiple instances. Avoid: One global bucket lets a noisy caller block everyone else.
- **cache invalidation**: Mark cached data stale, remove it, or refresh it when its assumptions change. Use when: Users keep seeing old data after a successful edit. Do: List cache keys, dependencies, and freshness expectations; connect relevant writes to their invalidation path. Avoid: A correct invalidation event uses a different key from the reader.
- **job queue**: A queue separates accepting work from processing it. Use when: Slow work should continue independently of the request that created it. Do: Choose durable acceptance, bounded retry, and idempotent processing; make terminal failures visible. Avoid: Returning success before the work has been durably accepted.
- **circuit breaker**: Temporarily stop calls to a failing dependency and probe for recovery. Use when: Repeated upstream failures consume the application’s remaining capacity. Do: Define which failures count, when calls are rejected, and how recovery is tested. Avoid: Counting every client validation error as evidence that the dependency is down.
- **graceful shutdown**: Stop accepting new work and finish or hand off existing work before exiting. Use when: Deployments interrupt active requests or background jobs. Do: Stop admission, drain or checkpoint work, close resources, and enforce an exit deadline. Avoid: Waiting forever for one stuck task to complete.
- **minor units**: Represent money using integer minor units or an appropriate decimal representation with an explicit currency. Use when: Totals drift because prices are calculated with unsuitable arithmetic. Do: Choose scale and rounding explicitly; verify sums, allocation, and currency-specific cases. Avoid: Assuming every currency has two decimal places or every integer fits safely in a JavaScript number.
- **UTC vs local time**: Distinguish an instant from a calendar date and a local scheduled time. Use when: Dates shift by a day or recurring times move unexpectedly around daylight-saving changes. Do: Choose the value’s meaning first, then its storage format and conversion boundary. Avoid: Turning a birthday into a UTC instant and later rendering it on another date.
- **optimistic concurrency**: Accept an update only if the version being replaced still matches the version the caller read. Use when: Two editors can overwrite each other’s changes. Do: Include the version condition in the atomic write and test competing writers. Avoid: Incrementing the version without checking it in the write condition.
- **partial update**: A partial update changes only the fields identified by its contract. Use when: Editing one field unexpectedly clears other stored values. Do: Define patch semantics and validate presence separately from value. Avoid: Applying create-time defaults to every update request.
- **transaction isolation**: Transaction isolation defines how concurrent transactions may observe and affect one another. Use when: A transaction is atomic but still violates a rule under concurrent access. Do: Identify the required invariant, choose the isolation strategy, and test conflicting transactions. Avoid: Retrying only the final statement after the database requires the whole transaction to restart.
- **DTO**: A data transfer object defines the fields crossing an application boundary. Use when: An endpoint exposes private fields or accepts writable fields the caller should not control. Do: Define explicit input and output contracts and map only the permitted fields. Avoid: Inheriting the full persistence model and calling it a restricted DTO.
- **WebSocket**: A WebSocket provides a persistent bidirectional connection. Use when: The client and server must exchange ongoing updates in both directions. Do: Define message schemas, access checks, bounded buffers, and reconnect behavior. Avoid: Treating the connection’s initial authentication as permission for every future operation.
- **error as value**: Represent expected failures explicitly in a function’s result. Use when: Callers cannot tell not-found from validation failure or an unavailable dependency. Do: Define a small result type and require callers to handle its relevant cases. Avoid: Converting every unexpected exception into an ordinary empty result.
- **option type**: An option type represents a value that may be absent. Use when: Missing data is represented by a misleading default value. Do: Keep optionality at the boundary where it exists and handle it before requiring a value. Avoid: Assuming one optional value automatically represents absent, null, and empty as three distinct states.
- **DataLoader**: DataLoader batches related loads and can memoize results within a chosen scope. Use when: Related resolvers repeat loads for the same entities. Do: Share a loader within the authorized request scope and handle missing keys explicitly. Avoid: Sharing a loader cache across users without the required authorization boundaries.
- **middleware order**: Middleware ordering determines which request and response processing runs before another step. Use when: Authentication, parsing, or error handling runs too late or is skipped. Do: Trace the actual request and error paths, including short-circuits and preflight requests. Avoid: Sending an error response and then continuing to the protected handler.
- **data access layer**: A data access layer centralizes access rules and data retrieval at a boundary. Use when: Several server entry points duplicate sensitive reads and permission checks. Do: Derive identity from trusted context, enforce access, and shape the allowed response. Avoid: Trusting a caller-provided identity because the function lives in a server-only file.
- **server action**: A server action exposes a framework-supported server operation to the client. Use when: A form needs to invoke server work through the framework’s action mechanism. Do: Check identity and permission inside the operation and validate its arguments before mutation. Avoid: Relying on a protected page or hidden button as the action’s authorization.
- **ETL and ELT**: ETL transforms extracted data before loading it; ELT loads data before transformation. Use when: A report imports and transforms data from another system. Do: Define source identity, transformation rules, and rerun behavior with representative fixtures. Avoid: Transforming away source information needed to correct a later rule.
- **table partitioning**: Table partitioning divides a logical table into physical parts. Use when: A large table needs efficient bounded scans or lifecycle operations. Do: Check pruning and operational cost on representative queries before adopting the layout. Avoid: Assuming partitioning replaces indexes or guarantees every query is faster.
- **backfill**: A backfill populates or recomputes existing data after a rule or schema change. Use when: A new field works for new records but is missing from historical ones. Do: Process bounded ranges, persist progress, and define how concurrent writes interact with the job. Avoid: Overwriting a newer live value with a value computed from an older snapshot.
- **denormalization**: Denormalization duplicates or precomputes data to serve a read pattern. Use when: A measured read bottleneck motivates storing a value near its consumer. Do: Choose whether the value is a historical snapshot or a maintained projection and test that policy. Avoid: Maintaining the same copy through several independent update paths.
- **batch vs stream**: Batch processing operates on bounded input; streaming processes ongoing input. Use when: A data workflow is acquiring a broker despite only needing periodic results. Do: State the latency requirement and choose the simplest model that meets it. Avoid: Treating processing time as event time when late-arriving records change the result.
- **runtime validation**: Check untrusted values when the program receives them. Use when: An API or file supplies data that TypeScript types merely assume is valid. Do: Validate at the boundary, then pass the validated value inward. Check both malformed shapes and domain constraints. Avoid: Validating the shape but trusting a caller-supplied identity or ownership claim.
- **physical row clustering**: Place related rows near each other to reduce scattered reads. Use when: A range query fetches related rows from many storage pages. Do: Measure page reads for the target range query. Check engine semantics, locking, and free disk before reorganizing. Recheck after representative updates. Avoid: A one-time rewrite can lose its benefit as rows change; clustered-index behavior varies by engine.
- **normalization**: Store facts according to their keys to reduce update anomalies. Use when: Changing a customer’s current address requires editing many inconsistent copies. Do: Identify each fact’s key, split independent entities, and add references and constraints. Check one update changes the current fact everywhere it is read. Avoid: Splitting every value into a table without considering meaning or query cost is not a useful goal.
- **leader–follower replication**: Send writes through a leader and replicate them to followers. Use when: You need read capacity or a standby while keeping one write authority. Do: Define write and read routes, acknowledgement guarantees, and promotion rules. Check replica lag and read-after-write behavior during a failover drill. Avoid: One leader simplifies write ownership but does not eliminate data loss, lag, or split-brain risk.
- **multi-leader replication**: Allow multiple write authorities with explicit conflict handling. Use when: Disconnected sites or multiple regions need to accept writes. Do: Identify overlapping keys and constraints. Choose ownership or conflict rules. Test concurrent edits, retries, and recovery after disconnection for the selected product. Avoid: Last-write-wins can discard valid work; extra writers do not guarantee linear write scaling.
- **BASE**: Design for availability with temporary state and eventual convergence. Use when: A non-critical projection can stay usable while updates propagate. Do: Name the data that may lag, how it converges, and what still requires atomic changes. Check delayed, duplicate, and failed updates against those rules. Avoid: An eventually updated view can coexist with ACID transactions. Product categories do not establish exact guarantees.
- **ACID**: Specify atomicity, consistency, isolation, and durability for transactions. Use when: A transfer or order must preserve invariants across several database changes. Do: Define constraints, isolation, and commit durability. Check rollback and competing transactions. Keep external side effects outside the claimed local atomic boundary. Avoid: ACID consistency is invariant preservation, not CAP consistency. A local transaction cannot roll back an already sent email.
- **strong consistency**: Make the exact freshness and ordering guarantee explicit. Use when: A permission revocation or inventory decision must not use an older acknowledged value. Do: Identify the data scope and real-time ordering requirement. Use the supported consistent read or atomic conditional write. Check reads after acknowledged writes and overlapping updates. Avoid: A fresh read alone does not make a later write atomic; caches and unsupported indexes can change the read path.
- **write-ahead logging (WAL)**: Persist the recovery log before the corresponding data pages. Use when: A database must recover committed changes after a crash without flushing every changed page at commit. Do: Inspect log flush and commit settings. Run a crash-recovery drill on a disposable instance and reconcile acknowledged commits with recovered data. Avoid: WAL is not a standalone backup. Losing required log segments or the underlying durable storage can prevent recovery.
- **checkpointing**: Record a recovery boundary after making the required state durable. Use when: Recovery replays too much log or periodic flushing causes latency spikes. Do: Check checkpoint duration, write load, and recovery time on representative data. Tune the interval and WAL budget together. Verify backups separately. Avoid: A checkpoint does not replace backup retention or prove that a snapshot in another system is transactionally consistent.
- **storage compaction**: Merge storage runs and reclaim obsolete versions when safe. Use when: An LSM store accumulates files, tombstones, or background write pressure. Do: Measure pending compaction bytes, read/write amplification, disk headroom, and latency. Check tombstone and snapshot retention before expecting space to return. Avoid: Compaction consumes I/O and may need extra space before reclaiming any. It is different from a recovery checkpoint.
- **data locality**: Place related data and computation close to reduce remote work. Use when: A request crosses regions or shards repeatedly to join related records. Do: Map request hops, key placement, and transaction participants. Compare local and remote paths under the chosen durability and failure policy. Avoid: Local reads do not imply local writes, regulatory compliance, or survival of a regional failure.
- **hot partition**: One partition receives more work than its capacity can handle. Use when: One tenant or current-time bucket is throttled while the rest of the database is idle. Do: Measure traffic and throttling per key or partition. Choose a better key or split hot-key work where semantics allow. Verify query fan-out and distribution afterward. Avoid: More partitions do not divide a single key automatically. Balanced storage size does not guarantee balanced traffic.
- **caching**: Reuse a stored result with explicit freshness and failure rules. Use when: Repeated requests recompute the same expensive result or reread the same data. Do: Define the key and acceptable staleness. Test hits, misses, invalidation, tenant separation, eviction, and a cache outage. Measure origin load as well as latency. Avoid: A cache can amplify outages through miss storms. Fast stale data can still be wrong.
- **materialized view**: Persist a query result and define how it becomes current again. Use when: A dashboard repeats an expensive aggregate that can tolerate some lag. Do: Choose a freshness budget, refresh method, and failure handling. Change source data, verify the view’s documented lag, then verify the refreshed aggregate. Avoid: A normal view does not persist results. A materialized view can become stale and its refresh can be expensive.
- **secondary index**: Provide another access path besides the table’s primary organization. Use when: Queries use an email or status rather than the primary lookup key. Do: Choose an index for a real filter or ordering. Inspect the plan and table accesses, then measure write overhead and index size. Avoid: Secondary indexes add work to writes and are not necessarily unique or covering.
- **covering index**: Include the values a query needs so fewer table-row fetches may be necessary. Use when: An indexed query still spends time fetching columns from the table. Do: Check filters, ordering, and selected columns. Compare index size, write overhead, and actual heap fetches on representative data and update rates. Avoid: Coverage is relative to a query. Visibility, unsupported access methods, or changed projections can remove its benefit.
- **Bloom filter**: Reject definite misses cheaply while allowing possible false matches. Use when: Most lookups test keys absent from an expensive storage layer. Do: Choose a false-positive budget and memory size. Check all inserted keys, measure false positives for absent keys, and define update and rebuild behavior. Avoid: A standard Bloom filter cannot safely delete a key by clearing shared bits. A stale filter can reject valid new data.
- **LSM tree**: Buffer writes and organize persisted data as sorted runs. Use when: You are choosing storage for sustained writes and ordered key reads. Do: Measure the actual mix of writes, point reads, range scans, and deletions. Inspect compaction debt, cache use, and disk headroom before tuning. Avoid: Sequential ingestion does not mean every write is cheap; compaction rewrites data and can stall foreground work.
- **B-tree**: Keep keys ordered in a balanced tree for lookups and range scans. Use when: Queries filter by equality, compare ranges, or request indexed ordering. Do: Match the operator and key order to real queries. Inspect plans for equality, range, and ordering cases and measure write cost. Avoid: An index can be slower than a scan for a large result set; existence does not guarantee selection.
- **query planner**: Choose an executable access path and join strategy for a query. Use when: A database picks a surprising scan or join even though an index exists. Do: Compare estimated and actual row counts and inspect scans and joins. Refresh appropriate statistics before forcing a strategy. Use a safe environment for EXPLAIN ANALYZE, which executes the query. Avoid: A plan for one parameter or tiny fixture may not represent production. Planner costs are not elapsed milliseconds.
- **cost-based optimizer**: Choose among query plans using estimated resource costs. Use when: A join order performs badly because the database misestimates intermediate row counts. Do: Find the largest estimate-versus-actual mismatch. Check statistics, data distribution, and cost assumptions. Compare candidate plans under the same representative workload. Avoid: Cost units are model units, not milliseconds. Correlated columns and changing workloads can defeat simple estimates.
- **data skew**: Uneven values or partition sizes make some units of work much larger than others. Use when: One join task runs long after every other task finishes. Do: Inspect key frequencies, partition bytes, and task durations. Consider skew-aware joins or splitting heavy work. Check result correctness and the slowest task afterward. Avoid: Hashing distributes different keys; it does not divide all rows with the same key unless the algorithm explicitly supports that split.
- **change data capture (CDC)**: Publish source-data changes so downstream systems can follow them. Use when: A search index or warehouse must follow database updates without repeated full scans. Do: Define the snapshot-to-stream handoff and durable offsets. Test restart, duplicate delivery, deletes, schema changes, and reconciliation with the source. Avoid: CDC is not an automatic end-to-end exactly-once guarantee or an application business-event contract.
- **logical replication**: Replicate logical table changes rather than a physical copy of storage. Use when: A reporting database needs selected tables or a migration crosses compatible database versions. Do: Choose published data and row identity. Check initial synchronization, lag, updates, deletes, and schema compatibility before cutover. Avoid: Logical replication is not automatically multi-leader conflict resolution or a complete database clone.
- **physical replication**: Keep a standby aligned with the primary’s physical database changes. Use when: You need a compatible whole-database standby for read offload or recovery. Do: Check version and storage compatibility, base backup, retained WAL, and replay lag. Drill promotion and prevent the old primary from remaining writable. Avoid: A lagging standby can lose acknowledged asynchronous writes on failover. Replication does not replace independently retained backups.
- **query federation**: Query independently managed data sources through a shared interface. Use when: A report joins local records with data owned by another database. Do: Inspect pushdown and transferred rows. Restrict credentials and define remote failure handling. Check whether the required cross-source consistency is supported. Avoid: One SQL statement across sources does not automatically provide a single global snapshot or atomic distributed writes.
- **data lake**: Retain diverse data for later processing and analysis with governance. Use when: You need to preserve raw events and files for several future analytical uses. Do: Define ownership, formats, metadata, partitions, permissions, and retention. Verify a consumer can discover, read, and reconcile a sample dataset. Avoid: A lake is not inherently schema-free or transaction-free; table formats and lakehouse systems can add transaction semantics.
- **data warehouse**: Integrate and model data for reliable analytical queries and reporting. Use when: Reporting needs consistent business definitions across several operational systems. Do: Define fact grain, dimensions, freshness, and reconciliation rules. Check one metric against source records and test late or corrected data. Avoid: A warehouse is not automatically current, correct, or cheaper; ingestion, modeling, and query costs still matter.
- **columnar storage**: Store values by column to suit selective analytical scans. Use when: A report scans a few columns across many rows. Do: Benchmark the report and the actual insert/update mix. Measure scanned bytes, compression, latency, and maintenance work. Compare a row-oriented path for point access. Avoid: Columnar storage is not universally faster. Fetching many columns of one row or frequent small updates can favor other layouts.
- **row-based storage**: Keep a row’s fields together for record-oriented access. Use when: Requests frequently fetch or update complete individual records. Do: Measure point reads, updates, and analytical scans on realistic rows. Inspect indexes and bytes read; consider a separate analytical layout when needed. Avoid: Rowstore can still support efficient analytics with suitable indexes or mixed layouts. Storage orientation alone does not define transaction guarantees.
- **time-series partitioning**: Partition timestamped records into explicit time intervals. Use when: Retention cleanup or bounded-time queries scan a growing events table. Do: Choose the timestamp, UTC boundaries, interval, and late-data policy. Check boundary routing, pruning, retention, and traffic concentrated in the current partition. Avoid: Partitioning by time does not automatically distribute current writes across machines or accelerate queries missing the time filter.
- **hash partitioning**: Use a hash of the partition key to assign records to buckets. Use when: Independent keys should spread across partitions without maintaining range boundaries. Do: Choose the key and mapping. Check distribution with real key frequencies, query fan-out, and the migration procedure for changing the layout. Avoid: Uniform hashing does not remove skew caused by one heavily used key. Table partitions are not necessarily separate server shards.
- **range partitioning**: Assign rows to partitions by ordered key intervals. Use when: Queries and retention operations target a bounded date or key range. Do: Define lower and upper bounds and a missing-partition policy. Test boundary values, pruning for actual queries, and distribution of current writes. Avoid: Partitioning does not replace useful indexes, and a range layout can create a hot partition.
- **consistent hashing**: Limit key reassignment when the set of storage nodes changes. Use when: Adding a cache or storage node should not remap nearly every key. Do: Compare key assignments before and after a node change. Measure moved keys, per-node load, and replica placement, and plan any state transfer. Avoid: Fewer moved keys does not mean zero migration, balanced hot-key traffic, or safe membership changes.
- **data migration**: Move or transform existing records with validation and a controlled cutover. Use when: An application must move from one database or data layout to another. Do: Define snapshot, change capture or write pause, mappings, reconciliation, cutover, and rollback. Check counts, sampled values, deletes, and critical invariants before routing traffic. Avoid: Matching row counts alone does not establish equivalent data. A rollback after new target-only writes needs a recovery strategy.
- **schema registry**: Version message schemas and enforce a chosen compatibility policy. Use when: Producers and consumers deploy separately and disagree on event fields. Do: Choose the format and backward, forward, or full compatibility policy. Test old/new producer-consumer combinations and retained messages with the actual serializers. Avoid: Compatibility rules depend on format and mode. A registry does not migrate existing records or prove semantic compatibility.