Build Grist

KINDAreplaces $8/mosaves $96/yrback to the verdict

0%0 of 20 items done

Saved on this device only. Tick prerequisites first, then work the phases in order · do not start one until the checks above it pass.

Tables with typed columns and formulas that reference records rather than cell addresses, several views over one table, an access rule layer that filters rows and hides columns on the server, and an API that respects the same rules as the interface.

estimated effort multi-daythe files for this build are in the project pack

RuntimePython 3.12 with PostgreSQLFormulasA small expression language over records, with a written grammarAccessRules compiled into query conditions, applied server sideViewsGrid, card and filtered views as queries over one tableAPIThe same rule layer as the interface

Before step 1

Everything below is assumed from the first step. Tick each one when you actually have it, not when you plan to.

installfree
Why Runs the application and the formula evaluator.
Get it python.org or your package manager, then a virtual environment.
Verify python3 --version prints 3.12 or higher
installfree
Why Access rules become query conditions, which is exactly what a relational database is good at.
Get it Install locally or run it in Docker. open ↗
Verify psql connects and reports 16 or higher
have readyfree
Why Phase 1's type handling is only meaningful against real data, which has mixed columns, inconsistent dates and numbers stored as text.
Get it Use one you actually maintain. The messier the better for this purpose.
Verify It imports and at least one mixed column is reported
decidefree
Why Access rules are the reason to build this, and they can only be designed against a real case: who sees which rows, and which column must stay hidden from most people.
Get it Write down the actual case: the table, the principals and what each may see. If there is no such case, a spreadsheet is the better tool and this build has no purpose.
Verify The case is written down before Phase 3
decidefree
Why A formula language grown by accretion becomes unpredictable, and unpredictable formulas over shared data is worse than no formulas.
Get it Half a page before Phase 2: literals, references, precedence and a closed function list.
Verify The grammar file exists and predates the parser

Data model

Create these before the first phase that stores anything. Changing a table later is the expensive kind of change.

- docs(id, name, owner_id), tables(id, doc_id, name)
- columns(id, table_id, name, kind, ordinal, formula, reference_table_id) · kind includes reference, which is what makes it relational
- records(id, table_id, ordinal), cells(record_id, column_id, value_json, computed_json)
- views(id, table_id, kind, filter_json, sort_json, visible_columns_json)
- access_rules(id, table_id, principal, row_condition_json, column_visibility_json, permission) · compiled into query conditions
- deps(from_column_id, to_column_id) · the formula dependency graph
- An access rule is data compiled into a query condition. If it is ever evaluated after fetching, it has stopped being access control.

Environment variables

These go in a .env file the app reads at startup. The pack's .env.example is this table as a file · copy it, never commit the filled-in version.

Variable	Needed	Example	Where the value comes from
`DATABASE_URL`secret	required	`postgres://user:pass@localhost:5432/tables`	Your PostgreSQL connection string.
`MAX_FORMULA_DEPTH`	required	`20`	Maximum dependency chain length, catching pathological documents before they hang.
`API_WRITE_RATE_LIMIT`	required	`60`	Writes per minute per token.
`IMPORT_MAX_ROWS`	required	`200000`	Ceiling on an import, so a mistaken file does not consume the database.
`RULE_REPORT_PATH`	required	`./reports`	Where rule reports are written for review.

The build, in order

1
Typed columns and real imports
A real spreadsheet imports with its types recognised and its awkward values handled.
1. Inference is a suggestion. The correction interface is what makes it safe.
  Files src/import.py
2. With the values that do not conform. Silent coercion loses data nobody notices.
  Files src/types.py
3. Your own spreadsheet, not a generated one. Real files have all three problems.
done when · tick each as it passes
watch out
- A column that is ninety-nine percent numbers and one percent text will be inferred as numeric and the text rows silently dropped or zeroed, which loses data quietly. Report mixed columns, show the offending values, and make the user decide rather than guessing.
next phase
2
Formulas over records
A formula references a column rather than a cell address, so reordering rows changes nothing.
1. Closed function list, column and reference syntax, precedence. In a file, first.
  Files docs/formula-grammar.md
2. Row order becomes presentation. This is the whole advantage over a spreadsheet.
  Files src/formula.py
3. Recompute dependents only, with a cycle check at save time.
  Files src/deps.py
done when · tick each as it passes
watch out
- Cell address references are what make spreadsheet formulas break when rows move, and reproducing them here throws away the advantage of having records. Reference columns and related records, and reordering becomes a display concern rather than a correctness one.
next phase
3
Access rules that run on the server
Someone with restricted access cannot read rows they should not, even through the API.
1. Row condition and column visibility per principal. Readable and reviewable.
  Files src/rules.py
2. Applied in the database query. Never after fetching.
  Files src/compile.py
3. Raw API response with a restricted token. The interface is not the test.
  Files tests/test_access.py
done when · tick each as it passes
watch out
- Filtering in the client means the full dataset was sent and anyone can read it in the network inspector, which is the difference between hiding and protecting. Compile rules into the query, and prove it by checking what the server actually returned rather than what the interface displayed.
next phase
4
Views over one table
Grid, card and filtered views all read the same records and never diverge.
1. Filter, sort and column selection at read time.
  Files src/views.py
2. Rules apply first, then the view filter. A view can never widen access.
  Files src/views.py
3. A record edited out of a filter. This is where a materialised view reveals itself.
  Files tests/test_views.py
done when · tick each as it passes
watch out
- A view that materialises its own rows makes editing ambiguous and diverges within a day, and worse, a view defined with a filter is not access control even though it looks like one. Views are convenience; rules are protection, and conflating them is how data leaks.
next phase
5
An API with the same rules
Everything the interface can do is available programmatically, under identical restrictions.
1. The same code path as the interface. Two implementations diverge.
  Files src/api.py
2. Document and principal, revocable individually.
  Files src/tokens.py
3. Read and write. Both must be refused where the rules say so.
  Files tests/test_api.py
done when · tick each as it passes
watch out
- An API that bypasses access rules because it is for internal use is the gap that gets found later, usually by whoever integrated with it and did not realise. One rule layer, applied to both surfaces, tested with a restricted token.
6
Export, backups and rule reviewproduct builder
Data is portable, recoverable, and the rules are reviewed rather than assumed.
1. Spreadsheets for the values, a readable schema for types and formulas.
  Files src/export.py
2. Including rules. A restore where rules are missing is worse than no restore.
  Files scripts/backup.shREADME.md
3. Per principal, what they can see. Verify one against a hand check.
  Files src/rule_report.py
done when · tick each as it passes
watch out
- Access rules accumulate and interact, and nobody can hold the combined effect in their head after the fifth rule. A report showing what each principal can actually see is the only way to review them, and it is worth building before the rules get complicated rather than after.

what this build does not replace

A mature general formula engine.
Real-time multiplayer editing.
Automation and integration breadth.
Managed hosting and operations.
mature formula engine, access rules, collaboration, integrations, and hosting
real-time multiplayer
automation and integration breadth
scale and formula depth
enterprise permissions

after v1, if you want it

Column-level formulas that reference aggregates across a related table
A rule simulator that answers what a given principal would see before you save a rule
Change history per record, which shared data always eventually needs

Need the files? The project pack on the verdict page hands your agent the whole brief · more databases.