Build Knack

KINDAreplaces $59/mosaves $708/yrback to the verdict

0%0 of 16 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.

A schema-driven internal database app whose hard problems are generating physical names that survive the database's own limits, changing a field's type without losing the column, and permissions that are enforced by the database rather than by the interface that happens to be in front of it.

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

RuntimeNext.js 15 with TypeScript, run behind your own authenticationDatabasePostgreSQL, with one physical table per logical tablePermissionsRow-level security policies generated from the role definitionsMigrationsEvery schema change is a file, applied in order, never applied by handPackagingDocker Compose with the database, the app and a backup job

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 The database, the application and the backup job run together and the whole point of this build is that it starts with one command.
Get it Install Docker Desktop or the engine plus the compose plugin. open ↗
Verify docker compose version prints a version
installfree
Why Row-level security, forced policies and identifier limits are all PostgreSQL behaviours this build depends on directly.
Get it The official image, pinned to a major version in the compose file. open ↗
Verify A query confirms the version and that row security is available
decidefree
Why Generating policies from role definitions only works if the definitions exist, and inventing them while writing the policy generator produces roles that match the implementation rather than the organisation.
Get it Write the roles and their row scopes down before the schema builder exists.
Verify Each role has a test asserting what it cannot see
have readyfree
Why Name generation, type changes and view definitions all break on real column headings, and a clean demo schema will never show you the four-word heading with a slash in it.
Get it Export the spreadsheet that this application is meant to replace.
Verify Importing it produces valid physical names and a dry run that reports honestly

Data model

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

- bases(id, workspace_id, name), tables(id, base_id, label, physical_name, created_at) · physical_name is generated once, checked for collisions and stored
- fields(id, table_id, label, physical_name, type, options_json, required, ordinal) · the label is what people see and the physical name never changes with it
- one physical table per logical table, each carrying a row version column, a created and updated timestamp and an owner column
- views(id, table_id, kind, filters_json, sort_json, visible_fields_json, role_ids_json) · grid, form, kanban and detail are one definition rendered four ways
- roles(id, base_id, name), memberships(user_id, role_id), api_tokens(id, role_id, name, hash, created_at, last_used_at) · a token maps to a role, never to an exemption
- audit(id, table_id, record_id, actor_id, at, op, before_json, after_json) · both sides of every write, including the write that was rejected as stale
- trash(id, table_id, record_id, deleted_at, payload_json, purge_after) · deletion is a move rather than a removal, for a stated period

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://app:redacted@db:5432/base`	The application role. It owns no table and has no bypass attribute.
`MIGRATION_DATABASE_URL`secret	required	`postgres://owner:redacted@db:5432/base`	The owning role, used only to apply migrations and never by request handlers.
`TRASH_PURGE_DAYS`	required	`30`	How long a deleted record stays restorable before it is really gone.
`BACKUP_DIR`	required	`/backups`	Where the scheduled dump lands. The restore test reads from here.
`BACKUP_KEEP`	required	`14`	Generations retained. Cheap insurance against a bad migration.
`ATTACHMENT_DIR`	required	`./data/attachments`	Files attached to records, included in the export.
`MAX_IMPORT_ROWS`	required	`50000`	Above this the import is refused rather than run as a web request.

The build, in order

1
Names the database will actually accept
A field label becomes a physical name that is unique, valid and permanent.
1. The user's label is a display string and nothing else.
  Files src/schema/names.tstests/names.test.ts
2. Accented labels truncate earlier than they look.
  Files tests/names.test.ts
3. A builder with no migration history cannot be rolled forward twice.
  Files src/schema/migrate.tsmigrations/
done when · tick each as it passes
watch out
- Handing a user's label to the database as a column name and hoping means two fields named for accounts payable and accounts receivable collapse into the same identifier, because PostgreSQL uses no more than NAMEDATALEN minus one bytes of an identifier, by default 63, and truncates longer names rather than refusing them. The limit is in bytes, so a label with accented characters truncates earlier than its length suggests. Generate a short name, check it against the names already used in that table, store the mapping, and check the current limit for your version rather than trusting this number.
next phase
2
Changing a field without losing the column
A type change reports what it will break before it does anything.
1. Show the rows that would fail, with their values.
  Files src/schema/convert.tstests/convert.test.ts
2. The old values are the only copy of the old values.
  Files src/schema/convert.ts
3. A half-applied schema change is the worst state to be in.
  Files tests/migration-failure.test.ts
done when · tick each as it passes
watch out
- Implementing a type change as drop and recreate is the shortest path and silently destroys a column of real data, and the person who triggered it was only tidying a text field into a number. Convert in place inside a transaction, dry-run the conversion first and show the rows that would fail, and make keeping the original values in a shadow column one of the options rather than an afterthought.
next phase
3
Permissions the database enforces
The rows a role cannot see are invisible to any query that role can run.
1. Ownership is the quiet exemption everybody hits once.
  Files docker/init.sqltests/role-attributes.test.ts
2. And assert it over the catalogue after each migration.
  Files src/schema/policies.tstests/rls-forced.test.ts
3. What a role cannot see is the part worth testing.
  Files tests/rls-roles.test.ts
done when · tick each as it passes
watch out
- Enabling row security and then connecting as the role that owns the tables means every policy you wrote does nothing at all, because PostgreSQL documents that superusers, roles with the bypass attribute and table owners normally bypass row security, and an owner becomes subject to it only after the table is altered to force it. Give the application a role that owns nothing, force row security on every generated table, and assert all three properties of that role in the test suite rather than in a setup document.
- Enabling row security with no policy attached is a default-deny and returns no rows, which reads at first like a broken query and is in fact the failure direction you want. Test that case explicitly so the next person does not fix it by disabling the feature.
next phase
4
Four views, one definition, and two people editing
Grid, form, kanban and detail come from the same view row, and a concurrent edit never disappears.
1. Timestamps disagree between machines; a version does not.
  Files src/records/write.tstests/concurrency.test.ts
2. Otherwise the kanban drifts from the grid within a month.
  Files src/views/render.tsx
3. A restore is the feature people discover under pressure.
  Files src/records/trash.tstests/trash.test.ts
done when · tick each as it passes
watch out
- Taking the last write on a shared record means two people editing the same customer in a grid produce a row where one person's changes are gone and neither of them saw anything happen, and it will be weeks before somebody notices a phone number reverted. Send the row version with every read, reject a stale write, show both sets of values, and record the rejection in the audit so the pattern is visible.
5
An API on the same rails, and a backup you restoredproduct builder
Tokens obey the same policies as people, and a restore has been performed rather than described.
1. The API is another user, not another privilege level.
  Files src/api/auth.tstests/token-parity.test.ts
2. A half-imported CSV is worse than a refused one.
  Files src/import/csv.tstests/import.test.ts
3. A backup nobody has restored is a hope with a cron entry.
  Files tests/restore.test.tsdocker/backup.sh
done when · tick each as it passes
watch out
- Generating the API to run on an administrative connection because it was easier means your permissions exist only in the interface, and the read-only token you gave a contractor quietly reads every table in the base. Map each token to a role, set that role on the connection for the request, and write a test asserting that the rows a token can reach are exactly the rows its role can reach.

what this build does not replace

Real-time multiplayer editing with presence.
A general spreadsheet formula language.
An automation builder and integration catalogue.
A hosted control plane and billing.
managed hosting, schema builder, permissions, integrations, and support
real-time multiplayer
automation and integration breadth
scale and formula depth
enterprise permissions

after v1, if you want it

Computed fields with a tiny expression language, deliberately smaller than a spreadsheet
A public form endpoint writing through a role with insert only, which is the safest thing to expose
A schema diff between two installations, once you have a second one

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