Khurram Badar / Archive / Courses / JSON & PostgreSQL: A Practical Course

JSON & PostgreSQL: A Practical Course

course · 2026-08-01 · 3939 words · Khurram Badar · for professionals · practitioner

Hands-on course teaching JSON data structures and PostgreSQL database fundamentals for Node.js developers using Supabase stack.

programming · databases · json · education

JSON & PostgreSQL: A Practical Course

Built around the stack you're actually using: Node.js, Supabase/Postgres, pgvector, BullMQ.

---

Part 0: The One-Paragraph Version

**JSON** is a plain text file that holds structured data — lists, labelled values, nested groups — in a format both humans and machines can read. It's how your API sends data, how BullMQ stores job payloads, how `package.json` describes your project.

**PostgreSQL** is a database: a program that stores data in tables, keeps it consistent, and lets you ask precise questions about it very fast. Supabase is PostgreSQL with a hosting layer, an auth system, and an API bolted on top.

They meet in the middle: Postgres can store JSON *inside* a table column, which is how you'll hold messy document metadata next to clean structured columns.

---

PART 1: JSON

1.1 What a JSON file actually is

Open any `.json` file in a text editor and you'll see text. That's it. There is no magic binary format. It's a text file that follows a strict set of formatting rules so that any programming language can read it the same way.

The name stands for **J**ava**S**cript **O**bject **N**otation, because the syntax was lifted from JavaScript. But it's now language-neutral — Python, Go, Rust, and Postgres all read it.

Here is a complete, valid JSON file:

```json
{
"case_id": "UAE-2024-0417",
"client": "Al Futtaim Group",
"filed_date": "2024-11-03",
"is_active": true,
"value_aed": 4500000,
"counsel": ["Sara Haddad", "Omar Nasser"],
"court": {
"name": "Dubai Court of Cassation",
"emirate": "Dubai"
},
"closed_date": null
}
```

You can already read that without being taught anything. That's the point of the format.

1.2 The rules

JSON has exactly **six** data types.

| Type | Example | Notes |
|---|---|---|
| String | `"Dubai"` | Always double quotes. Never single. |
| Number | `4500000`, `-3.14` | No quotes. No commas as separators. |
| Boolean | `true` / `false` | Lowercase, no quotes. |
| Null | `null` | Means "known to be empty." |
| Array | `["a", "b"]` | Ordered list. Square brackets. |
| Object | `{"key": "value"}` | Labelled set. Curly braces. |

The structural rules:

1. **Keys must be strings in double quotes.** `{"name": "Sara"}` is valid. `{name: "Sara"}` is not (that's JavaScript, not JSON).
2. **No trailing commas.** `["a", "b",]` is invalid. This is the #1 cause of parse errors.
3. **No comments.** You cannot write `// this is a note` in JSON. (Some tools allow it — `tsconfig.json` is really JSONC — but standard JSON forbids it.)
4. **Objects and arrays nest freely.** An array of objects containing arrays is fine.
5. **Whitespace is ignored.** Pretty-printed and minified JSON are identical to a parser.

1.3 Objects vs arrays — the distinction that matters

An **object** is a labelled bag. Order doesn't matter. You access things by name.

```json
{ "emirate": "Dubai", "population": 3600000 }
```

An **array** is an ordered list. Order matters. You access things by position (0, 1, 2...).

```json
["Dubai", "Abu Dhabi", "Sharjah"]
```

The most common shape in real systems is **an array of objects** — a list of records:

```json
[
{ "id": 1, "title": "Employment Contract" },
{ "id": 2, "title": "NDA" }
]
```

That's what your API returns when you list documents. Rows from a database, translated into JSON.

1.4 Where JSON already lives in your stack

You use JSON constantly, whether you've thought about it that way or not:

1.5 Reading and writing JSON in Node

Two functions do 95% of the work:

```javascript
// Object -> JSON string
const jsonString = JSON.stringify(myObject);

// Pretty-printed with 2-space indent
const pretty = JSON.stringify(myObject, null, 2);

// JSON string -> Object
const myObject = JSON.parse(jsonString);
```

Reading a file from disk:

```javascript
import { readFile, writeFile } from 'fs/promises';

// Read
const raw = await readFile('./cases.json', 'utf-8');
const cases = JSON.parse(raw);

// Write
await writeFile('./cases.json', JSON.stringify(cases, null, 2));
```

**Critical detail:** `readFile` without `'utf-8'` returns a Buffer (raw bytes), not a string. `JSON.parse` will often still work because it coerces, but be explicit.

**Always wrap parsing in try/catch.** A malformed file throws and will crash a worker:

```javascript
let config;
try {
config = JSON.parse(raw);
} catch (err) {
console.error('Invalid JSON in config file:', err.message);
process.exit(1);
}
```

1.6 The things JSON can't do

Worth knowing before they bite you:

1.7 JSON Lines (`.jsonl`) — you'll hit this

A variant where each *line* is a complete JSON object, with no wrapping array:

```
{"chunk_id": 1, "text": "Article 1. Definitions..."}
{"chunk_id": 2, "text": "Article 2. Scope..."}
```

Why it exists: you can stream and process it line-by-line without loading the whole file into memory. Common in ML training data, log files, and bulk export/import. For a 50,000-chunk export from your legal engine, `.jsonl` is the right format — a single giant JSON array would need to be fully parsed before you could touch the first record.

---

Part 1 Exercises

1. Write a JSON file describing a legal document: id, title, language (`"ar"` or `"en"`), page count, an array of tags, and a nested object for the client with name and emirate.
2. Find the three errors: `{'name': "NDA", "pages": 12, "tags": ["draft",],}`
3. In Node, read that file, add a `processed_at` timestamp, and write it back pretty-printed.
4. What does `JSON.stringify({ a: 1, b: undefined, c: null })` return? Explain why.

---

PART 2: POSTGRESQL

2.1 What a database is, plainly

You could store your legal documents as JSON files in a folder. It would work — until you needed to answer "show me every contract for client X filed after March that mentions arbitration, sorted by date." Now you're loading and scanning thousands of files.

A database is a program whose entire job is to make that question fast, correct, and safe when twenty people ask it at once.

**PostgreSQL** (Postgres) is the most capable open-source one. It's what Supabase runs. Everything Supabase gives you — the auth system, the REST API, RLS, pgvector — is Postgres underneath.

2.2 The core model: tables, rows, columns

A **table** is a spreadsheet with enforced rules. A **row** is one record. A **column** is one field, with a fixed data type.

```
documents
┌────┬──────────────────────┬──────────┬────────────┐
│ id │ title │ language │ page_count │
├────┼──────────────────────┼──────────┼────────────┤
│ 1 │ Employment Contract │ en │ 12 │
│ 2 │ عقد إيجار │ ar │ 8 │
└────┴──────────────────────┴──────────┴────────────┘
```

Unlike a spreadsheet, the rules are enforced. You cannot put `"twelve"` into `page_count`. The database rejects it.

2.3 Data types you'll actually use

| Type | Use for |
|---|---|
| `text` | Any string. Postgres has no penalty for unbounded text — use it freely. |
| `integer` / `bigint` | Whole numbers. |
| `numeric(12,2)` | Money. **Never use floats for money.** |
| `boolean` | true/false |
| `timestamptz` | Dates and times. **Always use this, never `timestamp`.** It stores UTC and converts on the way out — essential when your firm is in Dubai and your server is in Frankfurt. |
| `uuid` | Non-guessable IDs. Supabase default. |
| `jsonb` | Structured JSON. Covered in Part 3. |
| `text[]` | An array of strings. |
| `vector(1536)` | pgvector embeddings. |

2.4 Creating a table

```sql
CREATE TABLE documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
firm_id uuid NOT NULL,
title text NOT NULL,
language text NOT NULL DEFAULT 'en',
page_count integer,
created_at timestamptz NOT NULL DEFAULT now()
);
```

Reading it line by line:

**Constraints are your friend.** Every rule you enforce in the schema is a rule you don't have to enforce in application code, and can't accidentally skip.

Adding a check constraint:

```sql
ALTER TABLE documents
ADD CONSTRAINT valid_language CHECK (language IN ('en', 'ar'));
```

2.5 The four operations

**INSERT** — add rows:

```sql
INSERT INTO documents (firm_id, title, language, page_count)
VALUES ('a1b2...', 'Employment Contract', 'en', 12)
RETURNING id;
```

`RETURNING` gives you back the generated ID — useful when you need it immediately for a related insert.

**SELECT** — read rows:

```sql
SELECT id, title, page_count
FROM documents
WHERE language = 'ar'
AND page_count > 5
ORDER BY created_at DESC
LIMIT 20;
```

**UPDATE** — change rows:

```sql
UPDATE documents
SET page_count = 14
WHERE id = 'a1b2...';
```

**DELETE** — remove rows:

```sql
DELETE FROM documents
WHERE id = 'a1b2...';
```

> **The rule that saves careers:** always write the `WHERE` clause *first*, run it as a `SELECT` to confirm what it matches, and only then convert it to `UPDATE` or `DELETE`. An `UPDATE` without `WHERE` changes every row in the table.

2.6 Relationships and foreign keys

Your legal engine has documents, and each document has many chunks. That's a **one-to-many** relationship. You model it by putting a reference on the "many" side:

```sql
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_index integer NOT NULL,
content text NOT NULL,
embedding vector(1536),
UNIQUE (document_id, chunk_index)
);
```

2.7 Joins

A join combines rows from two tables using their relationship.

```sql
SELECT
d.title,
c.chunk_index,
c.content
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.language = 'ar'
ORDER BY d.title, c.chunk_index;
```

Read it as: *for each chunk, go find its document, and give me fields from both.*

The four types, in order of how often you'll need them:

2.8 Aggregation

```sql
SELECT
d.language,
COUNT(*) AS document_count,
AVG(d.page_count) AS avg_pages,
MAX(d.created_at) AS most_recent
FROM documents d
WHERE d.firm_id = 'a1b2...'
GROUP BY d.language;
```

`GROUP BY` collapses rows into buckets. Every column in the `SELECT` must either be in the `GROUP BY` or be inside an aggregate function (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`). If you forget, Postgres tells you exactly which column is the problem.

Filtering *after* aggregation uses `HAVING`, not `WHERE`:

```sql
GROUP BY d.language
HAVING COUNT(*) > 10;
```

`WHERE` filters rows before grouping. `HAVING` filters groups after.

2.9 Indexes

An index is a lookup structure that lets Postgres find rows without scanning the whole table. Same idea as the index at the back of a book.

```sql
CREATE INDEX idx_documents_firm ON documents (firm_id);
CREATE INDEX idx_chunks_document ON chunks (document_id);
```

Rules of thumb:

```sql
EXPLAIN ANALYZE
SELECT * FROM documents WHERE firm_id = 'a1b2...';
```

If you see `Seq Scan` on a large table where you expected `Index Scan`, something is wrong — often a type mismatch or a function wrapping the column.

**Relevant to you:** pgvector indexes (HNSW, IVFFlat) are a specialised case of the same concept — they make approximate nearest-neighbour search fast instead of scanning every embedding.

2.10 Transactions

A transaction groups statements so they either **all** succeed or **all** fail. Nothing in between.

```sql
BEGIN;
INSERT INTO documents (firm_id, title) VALUES ('a1b2...', 'NDA');
INSERT INTO chunks (document_id, chunk_index, content) VALUES (...);
INSERT INTO audit_log (action, actor) VALUES ('document.created', 'user-99');
COMMIT;
```

If the third insert fails, `ROLLBACK` undoes the first two. You never end up with a document that has no audit entry.

This matters directly for your hash-chained audit log: writing the record and appending the chain link must be atomic, or the chain breaks.

2.11 Row Level Security

This is Postgres's answer to multi-tenancy, and it's the mechanism your legal engine depends on.

Normally, `SELECT * FROM documents` returns every row. RLS makes Postgres apply a filter automatically, at the database level, that the application cannot bypass.

```sql
-- Turn it on. Until a policy exists, this denies everything.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Users can only see documents belonging to their firm.
CREATE POLICY firm_isolation ON documents
FOR SELECT
USING (firm_id = (auth.jwt() ->> 'firm_id')::uuid);
```

Note the `->>` operator in there — that's reading a field out of the JWT's JSON claims. Part 3 covers it.

Two things worth internalising:

1. **RLS is enforced by the database, not your code.** A bug in an Express route cannot leak another firm's documents. This is a fundamentally stronger guarantee than a `WHERE` clause you remembered to write.
2. **Supabase's `service_role` key bypasses RLS entirely.** It must never reach the browser. Your Railway worker uses it; your frontend never does.

Separate policies control each operation — `FOR SELECT`, `FOR INSERT` (which uses `WITH CHECK` instead of `USING`), `FOR UPDATE`, `FOR DELETE`. Write them all, or leave gaps.

---

Part 2 Exercises

1. Write `CREATE TABLE` for a `cases` table: uuid PK, firm_id, case_number (unique per firm), client_name, status limited to `open`/`closed`/`archived`, opened_at.
2. Add a `case_id` foreign key to `documents` that is allowed to be null (some documents aren't case-specific). What `ON DELETE` behaviour do you want here, and why is it *not* `CASCADE`?
3. Write a query returning each case with its document count, including cases with zero documents.
4. Write an RLS policy restricting `chunks` to the user's firm. The tricky part: `chunks` has no `firm_id` column. (Hint: `EXISTS` with a subquery against `documents`.)

---

PART 3: WHERE THEY MEET — JSON INSIDE POSTGRES

3.1 `json` vs `jsonb`

Postgres has two JSON column types. **Use `jsonb`. Effectively always.**

| | `json` | `jsonb` |
|---|---|---|
| Storage | Exact text copy | Parsed binary |
| Whitespace/key order | Preserved | Not preserved |
| Duplicate keys | Kept | Last wins |
| Query speed | Reparses each time | Fast |
| Indexable | No | Yes (GIN) |

The only reason to pick `json` is if you need a byte-exact reproduction of the original document. That's rare.

3.2 The operators

```sql
CREATE TABLE documents (
id uuid PRIMARY KEY,
title text,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO documents (id, title, metadata) VALUES (
gen_random_uuid(),
'Lease Agreement',
'{
"source": "scanned",
"ocr_confidence": 0.94,
"parties": ["Al Futtaim", "Emaar"],
"court": { "emirate": "Dubai", "level": "cassation" }
}'::jsonb
);
```

Now the operators:

| Operator | Returns | Example |
|---|---|---|
| `->` | JSON | `metadata -> 'court'` → `{"emirate": "Dubai", ...}` |
| `->>` | **text** | `metadata ->> 'source'` → `scanned` |
| `#>` | JSON, deep path | `metadata #> '{court,emirate}'` |
| `#>>` | text, deep path | `metadata #>> '{court,emirate}'` → `Dubai` |
| `@>` | boolean, "contains" | `metadata @> '{"source":"scanned"}'` |
| `?` | boolean, "has key" | `metadata ? 'ocr_confidence'` |

**The single most common mistake:** using `->` when you want `->>`. `->` gives you JSON, so `metadata -> 'source'` returns `"scanned"` *with the quotes* — a JSON string. Comparing it to `'scanned'` fails. Use `->>` whenever you want a plain value to compare against.

```sql
-- Wrong
WHERE metadata -> 'source' = 'scanned'

-- Right
WHERE metadata ->> 'source' = 'scanned'
```

Casting numbers requires an explicit cast, because `->>` always gives text:

```sql
WHERE (metadata ->> 'ocr_confidence')::numeric < 0.85
```

That query — *find every scanned document where OCR confidence was poor* — is one you will genuinely need on the legal engine.

3.3 Arrays inside jsonb

```sql
-- Documents where Emaar is a party
SELECT title FROM documents
WHERE metadata -> 'parties' @> '["Emaar"]';

-- Expand the array into rows
SELECT d.title, party
FROM documents d,
jsonb_array_elements_text(d.metadata -> 'parties') AS party;
```

`jsonb_array_elements_text` turns a JSON array into a set of rows — the bridge from JSON-shaped data back into relational-shaped data.

3.4 Indexing jsonb

Without an index, every `@>` query scans the whole table.

```sql
-- General purpose: supports @>, ?, and friends
CREATE INDEX idx_documents_metadata ON documents USING GIN (metadata);

-- Faster and smaller, but only supports @>
CREATE INDEX idx_documents_metadata ON documents USING GIN (metadata jsonb_path_ops);
```

If you query one specific field constantly, a plain B-tree expression index beats GIN:

```sql
CREATE INDEX idx_documents_source ON documents ((metadata ->> 'source'));
```

3.5 Updating jsonb

```sql
-- Set or replace a field
UPDATE documents
SET metadata = jsonb_set(metadata, '{ocr_confidence}', '0.99')
WHERE id = 'a1b2...';

-- Merge in new fields (|| overwrites on conflict)
UPDATE documents
SET metadata = metadata || '{"reviewed": true, "reviewer": "Sara"}'::jsonb
WHERE id = 'a1b2...';

-- Remove a field
UPDATE documents
SET metadata = metadata - 'ocr_confidence'
WHERE id = 'a1b2...';
```

Note that jsonb updates rewrite the entire value. On very large JSON blobs updated frequently, that's a performance cost worth knowing about.

3.6 The design decision: column or jsonb?

This is the judgement call that actually matters, and it comes up immediately in your schema design.

**Use a real column when:**
- You filter, sort, or join on it regularly
- It exists on essentially every row
- You want the database to enforce its type or constraints
- It's a foreign key

**Use `jsonb` when:**
- The shape varies by document type or source
- It's write-often, read-rarely (raw OCR output, extraction provenance, model response payloads)
- You're storing something you don't want to migrate the schema for every time it changes
- It arrived as JSON from an external API and you want it kept whole

**The failure mode to avoid:** putting `firm_id` in jsonb. It's your tenancy key — it must be a real, indexed, `NOT NULL` column with a foreign key, because your entire RLS model depends on it. Anything security-critical or relationally-critical belongs in a column.

A sensible split for your legal engine:

```sql
CREATE TABLE documents (
-- Columns: queried, constrained, secured
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
firm_id uuid NOT NULL REFERENCES firms(id),
case_id uuid REFERENCES cases(id) ON DELETE SET NULL,
title text NOT NULL,
language text NOT NULL CHECK (language IN ('en','ar','mixed')),
source_type text NOT NULL CHECK (source_type IN ('native_pdf','scanned','docx')),
page_count integer,
status text NOT NULL DEFAULT 'pending',
created_at timestamptz NOT NULL DEFAULT now(),

-- jsonb: variable, provenance, pipeline output
extraction jsonb NOT NULL DEFAULT '{}'::jsonb,
metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);
```

`source_type` is a column, not a jsonb field, because it drives your OCR routing decision and you'll filter on it constantly. `extraction` holds OCR confidence scores, model versions, timing — things you inspect when debugging but never join on.

3.7 JSON on the way out

Postgres can build JSON for you, which means your API can return exactly the shape the frontend wants in a single round trip:

```sql
SELECT jsonb_build_object(
'id', d.id,
'title', d.title,
'chunks', (
SELECT jsonb_agg(
jsonb_build_object('index', c.chunk_index, 'text', c.content)
ORDER BY c.chunk_index
)
FROM chunks c WHERE c.document_id = d.id
)
) AS document
FROM documents d
WHERE d.id = 'a1b2...';
```

One query, fully nested result, no assembly in Node. This is often dramatically faster than fetching a document, then fetching its chunks, then stitching them together in application code.

---

Part 3 Exercises

1. Insert a document whose `metadata` contains a nested `court` object and a `parties` array. Then write a query finding all documents heard in Abu Dhabi.
2. Write a query returning documents where `extraction ->> 'ocr_confidence'` is below 0.8, ordered ascending. What index would make it fast?
3. You need to add `is_privileged` — a flag controlling whether junior associates can see a document. Column or jsonb? Justify it in one sentence.
4. Write a single query returning a case as JSON with a nested array of its documents.

---

PART 4: PUTTING IT TOGETHER

A realistic slice of the ingestion path, end to end.

**1. The worker receives a BullMQ job.** The payload is JSON:

```json
{ "document_id": "a1b2...", "firm_id": "f9e8...", "storage_path": "uploads/lease.pdf" }
```

**2. It processes and writes back, in a transaction:**

```javascript
const client = await pool.connect();
try {
await client.query('BEGIN');

await client.query(
`UPDATE documents
SET status = 'processed',
page_count = $2,
extraction = extraction || $3::jsonb
WHERE id = $1`,
[documentId, pageCount, JSON.stringify({
ocr_confidence: confidence,
ocr_engine: 'tesseract-5.3',
processed_at: new Date().toISOString()
})]
);

for (const [i, chunk] of chunks.entries()) {
await client.query(
`INSERT INTO chunks (document_id, chunk_index, content, embedding)
VALUES ($1, $2, $3, $4)`,
[documentId, i, chunk.text, toVector(chunk.embedding)]
);
}

await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
```

Three things to notice:

**3. Retrieval combines everything:**

```sql
SELECT
c.content,
d.title,
d.metadata ->> 'source' AS source,
1 - (c.embedding <=> $1) AS similarity
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.firm_id = $2
AND d.language = ANY($3)
AND (d.metadata ->> 'ocr_confidence')::numeric > 0.8
ORDER BY c.embedding <=> $1
LIMIT 20;
```

A vector search, a join, a jsonb filter, and tenant isolation — in one statement. That's the whole course in six lines.

---

Reference Card

**JSON**
```javascript
JSON.stringify(obj, null, 2) // object -> pretty string
JSON.parse(str) // string -> object
```
Six types. Double quotes on keys. No trailing commas. No comments. No dates.

**SQL essentials**
```sql
CREATE TABLE t (id uuid PRIMARY KEY, col text NOT NULL);
INSERT INTO t (col) VALUES ('x') RETURNING id;
SELECT ... FROM t WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT n;
UPDATE t SET col = 'y' WHERE id = 'z';
DELETE FROM t WHERE id = 'z';
BEGIN; ... COMMIT; / ROLLBACK;
CREATE INDEX idx ON t (col);
EXPLAIN ANALYZE <query>;
```

**jsonb**
```sql
-> JSON value ->> text value
#> deep JSON #>> deep text
@> contains ? has key
|| merge - remove key
jsonb_set(col, '{path}', 'value')
jsonb_build_object(...) jsonb_agg(...)
jsonb_array_elements_text(col -> 'arr')
CREATE INDEX ... USING GIN (col);
```

**Habits worth having**
- `timestamptz`, never `timestamp`
- `numeric`, never `float`, for money
- Index every foreign key
- `SELECT` before you `DELETE`
- Parameterised queries, always
- Tenancy keys are columns, not jsonb fields
- `service_role` never touches the browser

---

Where to go next

← KHDA Framework Training — Track A (All Teachers)Noor Knowledge Base: BT Properties Concierge →
Two years of working thought, indexed.
Ask me to present it in your conference room — WhatsApp +971 55 623 9111
Book Session →