# Relational data modelling

Relational modelling is the discipline of keeping one fact in one place and letting keys join the rest together. The interesting part of any real schema is where it breaks that rule deliberately. Connect keeps mail twice — once as the provider stored it, once in canonical form — and snapshots settings onto records so a later change cannot rewrite what happened.

- **Status:** Reference
- **Audience:** both, developer
- **Last verified:** 2026-09-10
- **Canonical:** https://connectbyjbrh.com/docs/technology/relational-data/

## One fact, one place

Normalisation gets taught as a numbered ladder and is better understood as a single instruction: store each fact once, and refer to it everywhere else. A customer's name lives on the customer, not on every order. When it changes, one row changes, and nothing anywhere disagrees.

The failures it prevents are unglamorous and expensive: two spellings of the same company, an address updated in three of five places, a total that no longer matches the rows it was computed from. Most *data quality* work in a young product is repairing exactly these.

**Primary key** — The one column, or set of columns, that identifies a row and never changes meaning.
**Foreign key** — A column that points at another table's primary key, with the database refusing values that do not exist there.
**Surrogate key** — An identifier with no business meaning, so a business fact changing does not change identity. Preferable to a natural key almost every time — an email address looks unique until somebody changes theirs.
**Junction table** — A row per relationship, when two things relate many-to-many. One address on one channel resolving to a person is exactly this shape.

## What a foreign key is really buying

Not tidiness. A foreign key is a promise the database keeps when the application forgets: no message belonging to a conversation that does not exist, no commitment attached to a deleted person, no orphan discovered two years later by an aggregate that quietly excludes it.

The cost is that deletion becomes a decision rather than a statement. What happens to the children — refuse, cascade, or detach — has to be chosen, and choosing *cascade* everywhere is how a single mistaken delete removes a year of history. Where the record is a customer's own history, refusing the delete and marking the row instead is usually the better answer, which is why deletion here is frequently a timestamp on the row rather than an absence of one.

## Does Connect use relational modelling?

**Used throughout, with `workspaces` as the root of the graph.** Nearly every table carries a workspace reference and is filtered on it automatically. People, companies, conversations, messages, calls, commitments, opportunities and cases are all rows with keys between them, which is what makes one person's whole history across every channel answerable as a query rather than as a report job.

Some of the more interesting decisions are about what was **not** made a column. A person's own triage — priority, starred, deleted — lives directly on the conversation and call rows, because the engine reads priority when it chooses what to work on, so a human correction changes behaviour rather than only appearance.

Blocking went the other way. It is deliberately not a column at all: a block is a memory row held against the contact and tagged per channel, because the directive reader looks only at the tag list. Held as a column it would have applied to one record; held as memory it survives across channels and into conversations that have not started yet.

## Where a second copy is kept on purpose

Denormalisation has two legitimate reasons and they behave differently. Copying for **speed** produces a cache, which must be invalidated when the source changes. Copying for **history** produces a snapshot, which must *not* be updated, because its whole value is that it records what was true then. Confusing the two produces a snapshot that helpfully rewrites the past.

| What is duplicated | Why | Which kind |
|---|---|---|
| Mail: provider-shaped tables alongside canonical conversations and messages | The engine and the interface read one shape regardless of provider, so adding a provider changes nothing downstream | Translation, kept in step by a bridging step |
| The voice settings that were resolved for a call, written onto the call | A review judges what actually ran, not what is configured now | Snapshot — never updated |
| A human disposition kept apart from the engine's outcome | A late provider callback must not overwrite what a person decided | Separation, not duplication — and the reason is the same one |

The first row is worth dwelling on because it looks wasteful and is not. The engine never reads the provider-shaped tables at all. That single rule is what makes a new mail provider an adapter rather than a migration.

## Where a table was deliberately not added

- **A phone line is a row in the existing channel-routes table**, not a new table. That table already answered *whose endpoint is this and what is it for* for every channel, so the telephone facts joined it — and a person's browser line is a row there too.
- **Files sit on the existing media table** through one shared service, which is already workspace-scoped and already under row-level security, so no new table and no second isolation story.
- **The spreadsheet-style grid is not a second data model.** It reads thirteen views over existing records, and every change it makes goes through the service that owns that record rather than writing rows directly.

The pattern behind all three: a new table is a new isolation surface, a new set of policies, a new place for a bug to live. Reusing a generalisation that already exists is usually the cheaper correctness decision, not merely the cheaper build.

## Questions

### Is denormalising always a compromise?

No. Copying for speed is a compromise you maintain. Copying for history is correct by design — a record of what was true at a moment has to stop tracking its source, or it is not a record. The mistake is applying the maintenance rules of the first to the second.

### Why store two versions of the same mail?

So that the engine and the interface never see a provider's shape. Provider tables hold what the adapter stored; canonical conversations and messages are what everything downstream reads. That boundary is what makes adding a provider a local change.

### Why is blocking not a flag on the contact?

Because a flag applies to a record and a block is about a relationship. Held as a tagged memory directive it applies across channels and across future conversations, and it is visible and removable in the same place as everything else Connect knows about that person.

## Related

- [PostgreSQL](https://connectbyjbrh.com/docs/technology/postgresql/)
- [Row-level security](https://connectbyjbrh.com/docs/technology/row-level-security/)
- [Identifiers](https://connectbyjbrh.com/docs/technology/uuid-identifiers/)
- [Schema migrations](https://connectbyjbrh.com/docs/technology/migrations/)
- [Multi-tenancy patterns](https://connectbyjbrh.com/docs/technology/multi-tenancy/)
- [Memory in Connect](https://connectbyjbrh.com/docs/memory/)
- [Audit logs](https://connectbyjbrh.com/docs/technology/audit-log/)

## What this page is based on

- `docs-source/sources/GENERAL.md` §3 — the data model and deliberate duplication
- `docs-source/sources/GENERAL.md` §9 — files and the data grid
- `docs-source/sources/PHONE.md` §1 — channel_routes and the call record