# Under the hood

> Two representations of the same sheet - a live collaborative document and a relational projection - and the rules that keep them from disagreeing

Source: https://aipril.dev/docs/spreadsheets/under-the-hood

A shared spreadsheet has to be two things that pull in opposite directions. While people are typing
it is a live document with no single writer, where two edits to neighbouring cells must both
survive. To everything downstream it is a table of rows to be queried, routed and reported on.

Doing one well usually means doing the other badly. Here they are kept as two representations of
the same sheet, with a defined relationship between them.

```mermaid
flowchart LR
    U["People editing<br/>at the same time"] --> DOC["Collaborative document<br/>CRDT, one authoritative server"]
    DOC -->|"incrementally"| STORE["Stored history"]
    DOC -->|"changed rows only"| ROWS["Relational projection<br/>rows as data"]
    ROWS --> READ["Analytics, processes,<br/>automations, the ledger"]
```

## Concurrent editing

Editing is resolved with a CRDT - a document type where concurrent changes merge by construction
instead of by a rule someone has to get right. There is one authoritative server for the document,
running as its own process over a websocket, and the document is persisted incrementally as changes
arrive instead of rewritten.

That last point is a design constraint, not an implementation detail: the full document is never
sent back over the REST surface. A sheet that grows past a request-size limit is a normal sheet, and
raising the limit would only move the failure further out. Incremental is the only shape that
survives.

Row order is held as fractional positions rather than integer indexes, so two people inserting rows
in the same place at the same time do not collide over a number, and nobody has to renumber a
thousand rows to make room for one.

## The relational projection

Alongside the live document, the sheet's rows are maintained as ordinary relational data - which is
what lets a process, an automation, the ledger and analytics read a sheet without going through the
editor.

The projection accepts **only the rows that changed**, never the whole document. That is what keeps
it cheap enough to run continuously, and it is also a safety property: a client that has lost its
state cannot present emptiness as a change. A "delete everything" shape is guarded against
explicitly and not trusted, and the row write and the event that claims it happen in one
transaction, so a half-applied update is not a state the system can be left in.

Rows are scoped to the organisation the way everything else is - a sheet is not a shared surface
with a filter in front of it.

## Formulas, and why the sandbox is stricter here

Formula evaluation is sandboxed on the same principles as a calculated field in the exploration
table, and then tightened, because the threat is different.

In a private table a bad formula hurts the person who wrote it. In a shared sheet the formula
travels: it is part of the document, so it reaches everybody with the sheet open. A formula that
escapes its sandbox is not a broken cell - it is stored code running in other people's sessions.

So the defence is layered instead of a single check. Dangerous identifiers are refused before
evaluation; the same globals are additionally shadowed to `undefined` inside the evaluation scope,
so a bypass of the first layer resolves to nothing; and `this` is bound to a frozen object with no
prototype, which closes the route back to the global object through a constructor chain. Each layer
would be adequate on a good day. Together they are what makes a shared formula safe to share.

## Where a sheet's data comes from

A sheet is one of three kinds, and the kind decides everything about how it behaves.

| Kind | Where the rows live | Editable | When it changes |
|---|---|---|---|
| **Native** | In the sheet | Yes | When somebody types |
| **Linked** | In the source | No - it is a view | Every time it is opened |
| **Imported** | In the sheet, taken from a source once | Yes | Only by editing, or by importing again |

A **linked** sheet points at one of three things: a dataset in the platform's own
[data lake](https://aipril.dev/docs/engine/sources-and-storage.md), a semantic model, or an inline SQL view. The
definition carries its own filter, sort, row limit and key field, so the sheet is a saved question
rather than a copy of an answer.

Its rows are fetched when it is opened, and deliberately not cached at that layer: whether a caller
may see a row depends on their own access to the underlying dataset or model, and a datum whose
visibility depends on who is asking may only be cached by the layer that computes that decision.
The engine's own result cache - keyed with the access decision as part of the key - carries the hot
path instead.

An **imported** sheet takes those rows once and becomes ordinary editable rows. The distinction is
deliberate: a plan people are editing should not change underneath them because a source refreshed.

### Getting outside data in

Anything from outside becomes a dataset first, and a sheet then links to or imports from it. That
one hop is what keeps the sheet's contract simple and the source list open-ended.

- **Files** - Excel and CSV uploads, or collection on a schedule from S3-compatible storage, an SFTP
  server, an SMB network share, or a local folder beside the installation.
- **Google Sheets by URL.** Paste the link and the sheet is pulled in. The URL is kept with the
  dataset, so refreshing from source later is one action rather than a repeat of the setup - which
  is the practical answer for the department that keeps its master list in Google Sheets and is not
  going to stop this quarter.
- **Databases and REST services** - registered as sources and queried in place, or extracted on a
  schedule.

**Refresh frequency is a property of the dataset, not of the sheet.** How often a source is
collected is set per dataset, from minutes to overnight, with an on-demand refresh; every dataset
carries when it was last updated. A linked sheet inherits that freshness automatically, because it
reads the dataset rather than a copy of it.

## Column types, and why there are so many

A business application needs more than text and number, and the gap between those two and what the
work actually requires is where spreadsheets usually degrade into conventions people maintain by
hand.

The type set is therefore wide on purpose: alongside text, long text, number, currency, percent,
date and date-time there are checkbox, rating, email, URL, phone, attachment, barcode, icon and a
code block; single and multiple select with their own colour-coded options; a formula column, an
autonumber, and the audit columns that fill themselves - created and last modified, and by whom.

Then the reference types, which carry configuration rather than a value:

- **`dictRef`** binds the column to a shared dictionary by its code, so the offered values are the
  ones the company actually uses.
- **`apiDict`** resolves its options from a remote service through a server-side proxy, with the
  fields to use as identifier and label named in the column's own configuration.
- **`datasetRef`** stores a row identifier from a dataset and displays a chosen label column - so
  the cell holds the real key while a person sees a name.
- **`employeeRef`, `unitRef`, `positionRef`, `legalEntityRef`** point into the org structure, and
  can **cascade**: the column narrows its choices by the nearest matching org column in the same
  row, so choosing a unit leaves only the positions inside it.
- **`task`** holds an actual task in a process; **`linkedRecord`** points at a row in another sheet.

Columns can also be marked required or read-only, and carry validation rules applied when a record
is edited - which is what makes a sheet usable as an entry surface and not only a viewing one.

## Lookup: the value you never have to copy

The lookup column is the quiet one that changes how a sheet is designed.

A lookup does not store a value. It stores a route: *follow this column, and show that field from the
other side.*

- Through a **`linkedRecord`** column, it reads a field from the linked row in the target sheet. The
  contract's price, the client's region, the product's category - shown where they are needed,
  living where they belong.
- Through a **`task`** column, it reads that task's **live current status** from the process itself.
  This is what puts a working status into a grid without anybody updating it.

Because a lookup is a route and not a copy, it is read-only by nature, and it cannot go stale.
The classic spreadsheet failure - the same fact typed into four sheets, three of which are now wrong
- stops being available as a way to work.

## Loading and limits

The grid is a working surface, not a bulk viewer, and it is built accordingly.

A linked sheet fetches under a row limit that is part of its own definition, and says plainly when
the result was truncated instead of quietly showing a prefix. Reference pickers - a dictionary, a
list of employees, a target sheet for a linked record - do not load their contents into a dropdown:
they search server-side and page with a cursor, so a dictionary with fifty thousand entries behaves
the same as one with fifty.

**And heavy analysis over sheet data does not happen in the sheet.** The relational projection makes
those rows an ordinary source for the [data engine](https://aipril.dev/docs/engine.md), which is where aggregation
belongs: millions of rows, columnar execution, the same semantic model and the same access rules as
any other data. The grid stays what it is good at - reading and editing what is in front of you -
and questions of scale are answered by the plane built to answer them. A sheet is not a small
database pretending to be big; it is an entry and working surface with a real engine behind it.

A workbook can also arrive as an artifact of an application: declared in the app's repository
alongside its data model and processes, materialised on install, and versioned with the rest. That
is the path by which "the planning workbook" becomes a defined object instead of a file - see
[AI in the data layer](https://aipril.dev/docs/engine/ai.md) for what that repository is and why it matters.

## Where the data goes

Everything the projection produces is read through the ordinary data plane: the same models, the
same query contract, the same access rules as any other source. There is no spreadsheet-shaped
exception in the engine - which is the reason a sheet can be joined to a database table at all. That
plane is the [Data engine](https://aipril.dev/docs/engine.md).