---
skill: Backend
description: "Database and API: data modelling, SQL, indexes and transactions, plus API design, auth, validation, errors, versioning and operations."
owner: Developer B
guides: 4
exported: 2026-10-19
---
# Backend
Database and API: data modelling, SQL, indexes and transactions, plus API design, auth, validation, errors, versioning and operations.
## Contents
- [Database Fundamentals](#database-fundamentals) — Concept guide
- [Database standards](#database-standards) — Development standard
- [API Fundamentals](#api-fundamentals) — Concept guide
- [API standards](#api-standards) — Development standard
---
# Database Fundamentals
_Concept guide · written by Developer B · updated 2026-09-29_
> The mental model: what a database is, why we use one, and how the pieces fit.
The material behind the concept sharing session. The goal is the correct mental
model, not every implementation detail.
## What is a database?
A database is a program whose job is to store data safely and answer questions
about it quickly. We use one instead of files because it gives us three things
files do not:
- **Structure** — the shape of the data is declared, so bad data is rejected.
- **Concurrency** — many people can read and write at once without corruption.
- **Querying** — we ask *what* we want, not *how* to find it.
## Tables, rows, columns
A table holds rows of the same shape. Each column has a type and may allow NULL.
| Column | Type | Null | Meaning |
| --- | --- | --- | --- |
| id | text | no | Identity of the row |
| email | text | no | Unique per member |
| title | text | yes | Not everyone has one |
> NULL is not zero and not an empty string. It means "we do not know". That
> distinction matters in every comparison you write.
## Keys and relationships
- **Primary key** — identifies a row. One per table.
- **Foreign key** — points at another table's primary key. This is what makes
the data relational.
- **One-to-many** — a skill has many checklist items.
- **Many-to-many** — an assessment cites many evidence items, and a piece of
evidence can back many assessments. That needs a join table.
## SQL in one minute
```sql
SELECT m.name, s.name AS skill, ms.verified_level
FROM member_skill ms
JOIN member m ON m.id = ms.member_id
JOIN skill s ON s.id = ms.skill_id
WHERE ms.verified_level < ms.target_level
ORDER BY m.name;
```
Read it in this order: `FROM` → `JOIN` → `WHERE` → `SELECT` → `ORDER BY`.
That is roughly what the engine does too.
## Common problems in our projects
1. **A missing index on a foreign key.** Joins get slow as the table grows.
2. **An UPDATE or DELETE without a WHERE clause.** Always run the SELECT first.
3. **Doing work in a loop that SQL could do in one statement.**
4. **Long transactions** holding locks while waiting on something external.
## What to look at next
- The checklist on this skill, in order.
- Our own schema: `prisma/schema.prisma`.
- *Use The Index, Luke* for indexing.
---
# Database standards
_Development standard · written by Developer B · updated 2026-09-29_
> Naming, migrations, keys and the rules we hold each other to in review.
How we build in this area. Deviations are fine, but say why in the pull request.
## Naming
- Tables are **singular**: `member`, `skill`, `assessment`.
- Primary key is always `id`.
- Foreign keys are `_id`.
- Timestamps are `created_at` / `updated_at`.
- Booleans read as a statement: `is_primary`, `archived`.
## Keys
- Use a generated id (cuid) as the primary key. Do not make a natural key the
primary key — emails and names change.
- Put a unique constraint on anything that must be unique anyway, such as
`(member_id, skill_id)`.
## Migrations
- Every schema change ships as a migration. No manual edits to a live database.
- A migration must be safe to run while the old code is still serving:
1. add the column as nullable,
2. backfill,
3. make it required in a later migration.
- Never rename and reuse a column in one step.
## Queries
- No N+1. If you are querying inside a loop, stop and write a join.
- Always know what your `WHERE` clause matches before an `UPDATE` or `DELETE`.
- Index foreign keys and anything you filter or sort by regularly.
- Measure before and after adding an index. An index is not free — it costs on
every write.
## Transactions
- Wrap multi-statement writes that must succeed or fail together.
- Keep transactions short. Never hold one open across a network call.
## Review checklist
- [ ] Migration is reversible, or the risk is stated
- [ ] New foreign keys are indexed
- [ ] No query inside a loop
- [ ] Deletes are either soft or have a considered cascade
- [ ] The query plan was checked for anything on a hot path
---
# API Fundamentals
_Concept guide · written by Developer B · updated 2026-09-29_
> What an API contract is, and how a request becomes a response.
## What an API is
A contract. The client promises to send requests in an agreed shape; the server
promises a response in an agreed shape. Everything else — language, framework,
database — is the server's private business.
## The shape of a request
```http
POST /api/assessments HTTP/1.1
Content-Type: application/json
Authorization: Bearer
{ "memberId": "abc", "skillId": "def", "level": 3 }
```
- **Method** says what kind of operation: GET reads, POST creates, PATCH
modifies, DELETE removes.
- **Headers** carry metadata: who you are, what format you send.
- **Body** carries the payload.
## Status codes that matter
| Code | Meaning | Use it when |
| --- | --- | --- |
| 200 | OK | The read or update worked |
| 201 | Created | A POST made something new |
| 400 | Bad Request | The input failed validation |
| 401 | Unauthorized | We do not know who you are |
| 403 | Forbidden | We know, and you may not |
| 404 | Not Found | No such resource, or you may not see it |
| 409 | Conflict | It clashes with current state |
| 500 | Server Error | We broke, not you |
The 401/403 distinction matters: 401 means *authenticate*, 403 means *you are
authenticated and still not allowed*.
## Idempotency
GET, PUT and DELETE should be safe to repeat. POST usually is not. If a client
retries after a timeout, will you create a duplicate? If yes, you need an
idempotency key.
## Where things go wrong
1. Validation that only runs in the browser.
2. Authorization checked in the UI but not on the server.
3. Error responses with a different shape per endpoint.
4. Pagination without a stable sort, so rows repeat across pages.
---
# API standards
_Development standard · written by Developer B · updated 2026-09-29_
> Resource design, error shape, auth and versioning rules.
## Resource design
- URLs name **nouns**, not verbs: `/skills/:id/assessments`, not
`/getAssessmentsForSkill`.
- Plural collections, singular members: `GET /skills`, `GET /skills/:id`.
- Nest at most one level deep. Past that, use a query parameter.
## One error shape, everywhere
```json
{
"error": {
"code": "VALIDATION_FAILED",
"message": "Level must be between 0 and 4.",
"field": "level"
}
}
```
Clients should never have to parse prose to know what went wrong.
## Validation and authorization
- Validate at the boundary, before anything touches the database.
- Every handler answers two questions: *who is this* and *may they do this to
this specific record*. Ownership checks belong on the server, always.
- Never trust an id from the client to imply permission.
## Pagination
- Cursor-based for anything that grows. Offset pagination drifts as rows are
inserted.
- Always sort by something unique and stable, such as `(created_at, id)`.
## Versioning
- Additive changes need no version bump. Removing or renaming a field does.
- Version in the path: `/v1/...`.
- Announce a deprecation before you ship the removal.
## Review checklist
- [ ] Ownership and role checks are server-side
- [ ] Input validated at the boundary
- [ ] Errors use the standard shape and the right status code
- [ ] List endpoints paginate with a stable sort
- [ ] No secret, token or personal data in a URL or a log line