Vibecode Knack
track this build4 phases, 12 steps, beginner friendly0%KINDA · weekend project
The core loop is buildable, but a dependable replacement becomes a real weekend or multi-day project. For Knack, build a small hosted database app with pages, forms, and role-based views. The hard boundary is managed hosting, schema builder, permissions, integrations, and support, plus data model flexibility, collaboration, and integrations.
the project pack5 files · written for this build, step by step
You are building a lean indie version of Knack. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== README.md ===== # Knack · indie build 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-day**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | Next.js 15 with TypeScript, run behind your own authentication | the interface is the small half and it should stay that way | | Database | PostgreSQL, with one physical table per logical table | a generic key and value table gives up every constraint the database exists to provide | | Permissions | Row-level security policies generated from the role definitions | a permission enforced in a controller is a permission the next endpoint forgets | | Migrations | Every schema change is a file, applied in order, never applied by hand | a schema builder that edits the database live is a schema builder with no history | | Packaging | Docker Compose with the database, the app and a backup job | the thing you are replacing is hosted, and the hosting is most of the work | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Docker and Docker Compose** · free - 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. - Verify: docker compose version prints a version - [ ] **PostgreSQL, current major version** · free - 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. - Verify: A query confirms the version and that row security is available - [ ] **The roles and what each one may see** · free - 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 - [ ] **A real spreadsheet you are trying to retire** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Knack, and the README should say so rather than pretend. ===== BRIEF.md ===== # Build brief · Knack The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a small self-hosted database application where the tables, the views and the permissions all come from one definition to replace Knack. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: Next.js 15 with TypeScript, run behind your own authentication · the interface is the small half and it should stay that way - Database: PostgreSQL, with one physical table per logical table · a generic key and value table gives up every constraint the database exists to provide - Permissions: Row-level security policies generated from the role definitions · a permission enforced in a controller is a permission the next endpoint forgets - Migrations: Every schema change is a file, applied in order, never applied by hand · a schema builder that edits the database live is a schema builder with no history - Packaging: Docker Compose with the database, the app and a backup job · the thing you are replacing is hosted, and the hosting is most of the work ### Data model (create this before Phase 1) - 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 ### Phase 1 · Names the database will actually accept Build: Generate a physical name from a label: lowercase, transliterate, strip, truncate, and add a short suffix on collision within the table. Reject nothing back to the user; solve it in the generator. Store the mapping and never derive a name at query time. Renaming a label changes the label only. Every schema change writes a migration file. Trap: 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. Done when: Two labels that would truncate to the same identifier produce distinct physical names, tested with labels over the limit. A label containing multibyte characters produces a name within the byte limit, tested by measuring bytes rather than characters. Renaming a field changes its label and leaves its physical name and its data untouched, verified in the database. Every schema operation produces a migration file and applying the files to an empty database reproduces the schema exactly. Do not build yet: No type changes, permissions, views or API yet. ### Phase 2 · Changing a field without losing the column Build: Implement add, rename, reorder and type change. For a type change, run the conversion against every row first and report the count and sample of rows that would fail. Then require a choice: refuse, null the failures, or keep the original values in a shadow column. Never drop and recreate a column that holds data. Trap: 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. Done when: A type change that would fail on some rows reports the count and a sample before making any change, tested. Choosing to keep originals creates a shadow column containing every prior value, tested. A failed migration leaves the table exactly as it was, tested by forcing a failure mid-transaction. No code path drops a column that contains data, asserted by a test over the migration generator. Do not build yet: No permissions, views, concurrency or API yet. ### Phase 3 · Permissions the database enforces Build: Generate row-level security policies from the role and view definitions, and apply them to every generated table. Run the application as a database role that owns nothing, has no superuser and no bypass attribute. Set the tables to force row security so ownership cannot become an exemption. Write a test per role asserting both what it sees and what it does not. Trap: 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. Trap: 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. Done when: The application's database role owns no table and has neither the superuser nor the bypass attribute, asserted by a query in the suite. Every generated table has row security enabled and forced, asserted by a query over the catalogue after any schema change. Each role has a test asserting the rows it can read and, separately, that a row outside its scope returns nothing rather than an error. A table with row security enabled and no policy returns zero rows, tested. Do not build yet: No view rendering, concurrency, API or export yet. ### Phase 4 · Four views, one definition, and two people editing Build: Render the four view kinds from one definition with filters, sort and visible fields. Carry a row version on every read and reject a write whose version is stale, showing the current values beside the attempted ones. Write an audit row for every write including the rejected one. Deletion moves the record to trash with a purge date and restore puts it back with its identifier. Trap: 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. Done when: Two writes from the same read version produce one success and one rejection showing current and attempted values, tested. A rejected write appears in the audit with both sides, tested. The four view kinds render from one definition and changing the visible fields changes all four, tested. A deleted record is restorable with its original identifier until the purge date, tested at the boundary. Do not build yet: No API, import, export or backup work yet. ### Phase 5 · An API on the same rails, and a backup you restored Build: Generate a token-authenticated REST surface from the schema, where every token maps to a role and every query runs down the same policy path as the interface. CSV import with a dry run reporting rejected rows. Full export of schema, data, attachments and audit. A scheduled backup and a restore exercised in the test suite against a fresh database. Trap: 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. Done when: A token's reachable rows equal its role's reachable rows for every role, tested pairwise. CSV import dry run reports rejected rows with reasons and imports nothing until confirmed, tested. Export covers schema, data, attachments and audit, and reimports into an empty installation with identical counts and identical physical names, tested. The restore runs in the test suite against a fresh database rather than being documented. Do not build yet: Nothing after this. ### Out of scope (and why) - Real-time multiplayer editing with presence, which is a conflict resolution problem rather than a database problem and is weeks on its own. - A general spreadsheet formula language, which is a parser, an evaluator, a dependency graph and a decade of compatibility expectations. - Hundreds of integrations and an automation builder, which is what the subscription actually pays for and is permanent maintenance. - A hosted control plane and billing, which turns a self-hosted tool into a company. ### README must contain - That labels and physical names are separate, why, and that the physical name never changes - That permissions are enforced by database policies, that the application role owns nothing, and how to verify both - What happens during a field type change, including the dry run and the shadow column option - The concurrency model: row versions, what a rejected write looks like, and where to find it in the audit - The backup schedule, the exact restore command, and the fact that the restore is exercised in the test suite ===== AGENTS.md ===== # Agent instructions · Knack indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: Next.js 15 with TypeScript, run behind your own authentication, PostgreSQL, with one physical table per logical table, Row-level security policies generated from the role definitions, Every schema change is a file, applied in order, never applied by hand, Docker Compose with the database, the app and a backup job. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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. ===== BUILD_PLAN.md ===== # Build plan · Knack 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 1 · Names the database will actually accept A field label becomes a physical name that is unique, valid and permanent. ### Steps 1. Generate physical names, never pass labels through The user's label is a display string and nothing else. Files: `src/schema/names.ts`, `tests/names.test.ts` 2. Measure the limit in bytes Accented labels truncate earlier than they look. Files: `tests/names.test.ts` 3. Write a migration file for every change A builder with no migration history cannot be rolled forward twice. Files: `src/schema/migrate.ts`, `migrations/` ### Done when - [ ] Colliding truncations produce distinct names - [ ] Multibyte labels stay within the byte limit - [ ] A rename leaves the physical name and data alone - [ ] Migrations replay into an identical schema ### 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. ## Phase 2 · Changing a field without losing the column A type change reports what it will break before it does anything. ### Steps 1. Dry-run every conversion before committing Show the rows that would fail, with their values. Files: `src/schema/convert.ts`, `tests/convert.test.ts` 2. Offer the shadow column as a first-class option The old values are the only copy of the old values. Files: `src/schema/convert.ts` 3. Wrap the change in a transaction and test the failure A half-applied schema change is the worst state to be in. Files: `tests/migration-failure.test.ts` ### Done when - [ ] A lossy conversion reports failures before changing anything - [ ] Keeping originals creates a populated shadow column - [ ] A failed migration leaves the table unchanged - [ ] No column holding data is ever dropped ### 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. ## Phase 3 · Permissions the database enforces The rows a role cannot see are invisible to any query that role can run. ### Steps 1. Give the app a role that owns nothing Ownership is the quiet exemption everybody hits once. Files: `docker/init.sql`, `tests/role-attributes.test.ts` 2. Force row security on every generated table And assert it over the catalogue after each migration. Files: `src/schema/policies.ts`, `tests/rls-forced.test.ts` 3. Test the negative case for every role What a role cannot see is the part worth testing. Files: `tests/rls-roles.test.ts` ### Done when - [ ] The app role owns nothing and cannot bypass - [ ] Row security is enabled and forced on every table - [ ] Each role is tested for what it cannot see - [ ] A policy-less table returns zero rows ### 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. ## 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. ### Steps 1. Carry a row version through read and write Timestamps disagree between machines; a version does not. Files: `src/records/write.ts`, `tests/concurrency.test.ts` 2. Render four views from one definition Otherwise the kanban drifts from the grid within a month. Files: `src/views/render.tsx` 3. Make delete a move with a purge date A restore is the feature people discover under pressure. Files: `src/records/trash.ts`, `tests/trash.test.ts` ### Done when - [ ] A stale write is rejected with both value sets - [ ] Rejections appear in the audit - [ ] One definition drives all four view kinds - [ ] Deleted records restore with their identifier ### 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. ## Not in this build - Real-time multiplayer editing with presence. - A general spreadsheet formula language. - An automation builder and integration catalogue. - A hosted control plane and billing. ## 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 ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required · secret. The application role. It owns no table and has no bypass attribute. DATABASE_URL=postgres://app:redacted@db:5432/base # Required · secret. The owning role, used only to apply migrations and never by request handlers. MIGRATION_DATABASE_URL=postgres://owner:redacted@db:5432/base # Required. How long a deleted record stays restorable before it is really gone. TRASH_PURGE_DAYS=30 # Required. Where the scheduled dump lands. The restore test reads from here. BACKUP_DIR=/backups # Required. Generations retained. Cheap insurance against a bad migration. BACKUP_KEEP=14 # Required. Files attached to records, included in the export. ATTACHMENT_DIR=./data/attachments # Required. Above this the import is refused rather than run as a web request. MAX_IMPORT_ROWS=50000
You are building a lean indie version of Knack. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== README.md ===== # Knack · indie build 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-day**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | Next.js 15 with TypeScript, run behind your own authentication | the interface is the small half and it should stay that way | | Database | PostgreSQL, with one physical table per logical table | a generic key and value table gives up every constraint the database exists to provide | | Permissions | Row-level security policies generated from the role definitions | a permission enforced in a controller is a permission the next endpoint forgets | | Migrations | Every schema change is a file, applied in order, never applied by hand | a schema builder that edits the database live is a schema builder with no history | | Packaging | Docker Compose with the database, the app and a backup job | the thing you are replacing is hosted, and the hosting is most of the work | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Docker and Docker Compose** · free - 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. - Verify: docker compose version prints a version - [ ] **PostgreSQL, current major version** · free - 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. - Verify: A query confirms the version and that row security is available - [ ] **The roles and what each one may see** · free - 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 - [ ] **A real spreadsheet you are trying to retire** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Knack, and the README should say so rather than pretend. ===== BRIEF.md ===== # Build brief · Knack The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a small self-hosted database application where the tables, the views and the permissions all come from one definition to replace Knack. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: Next.js 15 with TypeScript, run behind your own authentication · the interface is the small half and it should stay that way - Database: PostgreSQL, with one physical table per logical table · a generic key and value table gives up every constraint the database exists to provide - Permissions: Row-level security policies generated from the role definitions · a permission enforced in a controller is a permission the next endpoint forgets - Migrations: Every schema change is a file, applied in order, never applied by hand · a schema builder that edits the database live is a schema builder with no history - Packaging: Docker Compose with the database, the app and a backup job · the thing you are replacing is hosted, and the hosting is most of the work ### Data model (create this before Phase 1) - 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 ### Phase 1 · Names the database will actually accept Build: Generate a physical name from a label: lowercase, transliterate, strip, truncate, and add a short suffix on collision within the table. Reject nothing back to the user; solve it in the generator. Store the mapping and never derive a name at query time. Renaming a label changes the label only. Every schema change writes a migration file. Trap: 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. Done when: Two labels that would truncate to the same identifier produce distinct physical names, tested with labels over the limit. A label containing multibyte characters produces a name within the byte limit, tested by measuring bytes rather than characters. Renaming a field changes its label and leaves its physical name and its data untouched, verified in the database. Every schema operation produces a migration file and applying the files to an empty database reproduces the schema exactly. Do not build yet: No type changes, permissions, views or API yet. ### Phase 2 · Changing a field without losing the column Build: Implement add, rename, reorder and type change. For a type change, run the conversion against every row first and report the count and sample of rows that would fail. Then require a choice: refuse, null the failures, or keep the original values in a shadow column. Never drop and recreate a column that holds data. Trap: 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. Done when: A type change that would fail on some rows reports the count and a sample before making any change, tested. Choosing to keep originals creates a shadow column containing every prior value, tested. A failed migration leaves the table exactly as it was, tested by forcing a failure mid-transaction. No code path drops a column that contains data, asserted by a test over the migration generator. Do not build yet: No permissions, views, concurrency or API yet. ### Phase 3 · Permissions the database enforces Build: Generate row-level security policies from the role and view definitions, and apply them to every generated table. Run the application as a database role that owns nothing, has no superuser and no bypass attribute. Set the tables to force row security so ownership cannot become an exemption. Write a test per role asserting both what it sees and what it does not. Trap: 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. Trap: 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. Done when: The application's database role owns no table and has neither the superuser nor the bypass attribute, asserted by a query in the suite. Every generated table has row security enabled and forced, asserted by a query over the catalogue after any schema change. Each role has a test asserting the rows it can read and, separately, that a row outside its scope returns nothing rather than an error. A table with row security enabled and no policy returns zero rows, tested. Do not build yet: No view rendering, concurrency, API or export yet. ### Phase 4 · Four views, one definition, and two people editing Build: Render the four view kinds from one definition with filters, sort and visible fields. Carry a row version on every read and reject a write whose version is stale, showing the current values beside the attempted ones. Write an audit row for every write including the rejected one. Deletion moves the record to trash with a purge date and restore puts it back with its identifier. Trap: 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. Done when: Two writes from the same read version produce one success and one rejection showing current and attempted values, tested. A rejected write appears in the audit with both sides, tested. The four view kinds render from one definition and changing the visible fields changes all four, tested. A deleted record is restorable with its original identifier until the purge date, tested at the boundary. Do not build yet: No API, import, export or backup work yet. ### Phase 5 · An API on the same rails, and a backup you restored Build: Generate a token-authenticated REST surface from the schema, where every token maps to a role and every query runs down the same policy path as the interface. CSV import with a dry run reporting rejected rows. Full export of schema, data, attachments and audit. A scheduled backup and a restore exercised in the test suite against a fresh database. Trap: 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. Done when: A token's reachable rows equal its role's reachable rows for every role, tested pairwise. CSV import dry run reports rejected rows with reasons and imports nothing until confirmed, tested. Export covers schema, data, attachments and audit, and reimports into an empty installation with identical counts and identical physical names, tested. The restore runs in the test suite against a fresh database rather than being documented. Do not build yet: Nothing after this. ### Out of scope (and why) - Real-time multiplayer editing with presence, which is a conflict resolution problem rather than a database problem and is weeks on its own. - A general spreadsheet formula language, which is a parser, an evaluator, a dependency graph and a decade of compatibility expectations. - Hundreds of integrations and an automation builder, which is what the subscription actually pays for and is permanent maintenance. - A hosted control plane and billing, which turns a self-hosted tool into a company. ### README must contain - That labels and physical names are separate, why, and that the physical name never changes - That permissions are enforced by database policies, that the application role owns nothing, and how to verify both - What happens during a field type change, including the dry run and the shadow column option - The concurrency model: row versions, what a rejected write looks like, and where to find it in the audit - The backup schedule, the exact restore command, and the fact that the restore is exercised in the test suite ===== AGENTS.md ===== # Agent instructions · Knack indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: Next.js 15 with TypeScript, run behind your own authentication, PostgreSQL, with one physical table per logical table, Row-level security policies generated from the role definitions, Every schema change is a file, applied in order, never applied by hand, Docker Compose with the database, the app and a backup job. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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. ===== BUILD_PLAN.md ===== # Build plan · Knack 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 1 · Names the database will actually accept A field label becomes a physical name that is unique, valid and permanent. ### Steps 1. Generate physical names, never pass labels through The user's label is a display string and nothing else. Files: `src/schema/names.ts`, `tests/names.test.ts` 2. Measure the limit in bytes Accented labels truncate earlier than they look. Files: `tests/names.test.ts` 3. Write a migration file for every change A builder with no migration history cannot be rolled forward twice. Files: `src/schema/migrate.ts`, `migrations/` ### Done when - [ ] Colliding truncations produce distinct names - [ ] Multibyte labels stay within the byte limit - [ ] A rename leaves the physical name and data alone - [ ] Migrations replay into an identical schema ### 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. ## Phase 2 · Changing a field without losing the column A type change reports what it will break before it does anything. ### Steps 1. Dry-run every conversion before committing Show the rows that would fail, with their values. Files: `src/schema/convert.ts`, `tests/convert.test.ts` 2. Offer the shadow column as a first-class option The old values are the only copy of the old values. Files: `src/schema/convert.ts` 3. Wrap the change in a transaction and test the failure A half-applied schema change is the worst state to be in. Files: `tests/migration-failure.test.ts` ### Done when - [ ] A lossy conversion reports failures before changing anything - [ ] Keeping originals creates a populated shadow column - [ ] A failed migration leaves the table unchanged - [ ] No column holding data is ever dropped ### 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. ## Phase 3 · Permissions the database enforces The rows a role cannot see are invisible to any query that role can run. ### Steps 1. Give the app a role that owns nothing Ownership is the quiet exemption everybody hits once. Files: `docker/init.sql`, `tests/role-attributes.test.ts` 2. Force row security on every generated table And assert it over the catalogue after each migration. Files: `src/schema/policies.ts`, `tests/rls-forced.test.ts` 3. Test the negative case for every role What a role cannot see is the part worth testing. Files: `tests/rls-roles.test.ts` ### Done when - [ ] The app role owns nothing and cannot bypass - [ ] Row security is enabled and forced on every table - [ ] Each role is tested for what it cannot see - [ ] A policy-less table returns zero rows ### 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. ## 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. ### Steps 1. Carry a row version through read and write Timestamps disagree between machines; a version does not. Files: `src/records/write.ts`, `tests/concurrency.test.ts` 2. Render four views from one definition Otherwise the kanban drifts from the grid within a month. Files: `src/views/render.tsx` 3. Make delete a move with a purge date A restore is the feature people discover under pressure. Files: `src/records/trash.ts`, `tests/trash.test.ts` ### Done when - [ ] A stale write is rejected with both value sets - [ ] Rejections appear in the audit - [ ] One definition drives all four view kinds - [ ] Deleted records restore with their identifier ### 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. ## Not in this build - Real-time multiplayer editing with presence. - A general spreadsheet formula language. - An automation builder and integration catalogue. - A hosted control plane and billing. ## 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 ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required · secret. The application role. It owns no table and has no bypass attribute. DATABASE_URL=postgres://app:redacted@db:5432/base # Required · secret. The owning role, used only to apply migrations and never by request handlers. MIGRATION_DATABASE_URL=postgres://owner:redacted@db:5432/base # Required. How long a deleted record stays restorable before it is really gone. TRASH_PURGE_DAYS=30 # Required. Where the scheduled dump lands. The restore test reads from here. BACKUP_DIR=/backups # Required. Generations retained. Cheap insurance against a bad migration. BACKUP_KEEP=14 # Required. Files attached to records, included in the export. ATTACHMENT_DIR=./data/attachments # Required. Above this the import is refused rather than run as a web request. MAX_IMPORT_ROWS=50000
You are building a production product version of Knack. Create the following project files first, then implement the application by following them. Keep the files updated as decisions change. Do not collapse this into a single README or prompt. ===== PRODUCT.md ===== # Knack · product brief ## Problem The core loop is buildable, but a dependable replacement becomes a real weekend or multi-day project. For Knack, build a small hosted database app with pages, forms, and role-based views. The hard boundary is managed hosting, schema builder, permissions, integrations, and support, plus data model flexibility, collaboration, and integrations. ## Product outcome A self-hosted database application whose generated names survive the database's limits, whose type changes report before they act, whose permissions are enforced by policies rather than by controllers, and whose restore has actually been run. ## Target user A builder who needs a maintainable product foundation, not a one-off demo. ## Required capabilities - Docker - PostgreSQL - self-hosted server - optional object storage for attachments ## Explicit non-goals for v1 - 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 ## Success criteria - Colliding truncated names are confirmed to produce distinct identifiers - A lossy type change is confirmed to report before acting - Row security is confirmed enabled and forced on every table - A stale write is confirmed rejected with both value sets - The restore is confirmed to run in the test suite ===== BRIEF.md ===== # Build brief · Knack The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a small self-hosted database application where the tables, the views and the permissions all come from one definition to replace Knack. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: Next.js 15 with TypeScript, run behind your own authentication · the interface is the small half and it should stay that way - Database: PostgreSQL, with one physical table per logical table · a generic key and value table gives up every constraint the database exists to provide - Permissions: Row-level security policies generated from the role definitions · a permission enforced in a controller is a permission the next endpoint forgets - Migrations: Every schema change is a file, applied in order, never applied by hand · a schema builder that edits the database live is a schema builder with no history - Packaging: Docker Compose with the database, the app and a backup job · the thing you are replacing is hosted, and the hosting is most of the work ### Data model (create this before Phase 1) - 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 ### Phase 1 · Names the database will actually accept Build: Generate a physical name from a label: lowercase, transliterate, strip, truncate, and add a short suffix on collision within the table. Reject nothing back to the user; solve it in the generator. Store the mapping and never derive a name at query time. Renaming a label changes the label only. Every schema change writes a migration file. Trap: 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. Done when: Two labels that would truncate to the same identifier produce distinct physical names, tested with labels over the limit. A label containing multibyte characters produces a name within the byte limit, tested by measuring bytes rather than characters. Renaming a field changes its label and leaves its physical name and its data untouched, verified in the database. Every schema operation produces a migration file and applying the files to an empty database reproduces the schema exactly. Do not build yet: No type changes, permissions, views or API yet. ### Phase 2 · Changing a field without losing the column Build: Implement add, rename, reorder and type change. For a type change, run the conversion against every row first and report the count and sample of rows that would fail. Then require a choice: refuse, null the failures, or keep the original values in a shadow column. Never drop and recreate a column that holds data. Trap: 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. Done when: A type change that would fail on some rows reports the count and a sample before making any change, tested. Choosing to keep originals creates a shadow column containing every prior value, tested. A failed migration leaves the table exactly as it was, tested by forcing a failure mid-transaction. No code path drops a column that contains data, asserted by a test over the migration generator. Do not build yet: No permissions, views, concurrency or API yet. ### Phase 3 · Permissions the database enforces Build: Generate row-level security policies from the role and view definitions, and apply them to every generated table. Run the application as a database role that owns nothing, has no superuser and no bypass attribute. Set the tables to force row security so ownership cannot become an exemption. Write a test per role asserting both what it sees and what it does not. Trap: 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. Trap: 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. Done when: The application's database role owns no table and has neither the superuser nor the bypass attribute, asserted by a query in the suite. Every generated table has row security enabled and forced, asserted by a query over the catalogue after any schema change. Each role has a test asserting the rows it can read and, separately, that a row outside its scope returns nothing rather than an error. A table with row security enabled and no policy returns zero rows, tested. Do not build yet: No view rendering, concurrency, API or export yet. ### Phase 4 · Four views, one definition, and two people editing Build: Render the four view kinds from one definition with filters, sort and visible fields. Carry a row version on every read and reject a write whose version is stale, showing the current values beside the attempted ones. Write an audit row for every write including the rejected one. Deletion moves the record to trash with a purge date and restore puts it back with its identifier. Trap: 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. Done when: Two writes from the same read version produce one success and one rejection showing current and attempted values, tested. A rejected write appears in the audit with both sides, tested. The four view kinds render from one definition and changing the visible fields changes all four, tested. A deleted record is restorable with its original identifier until the purge date, tested at the boundary. Do not build yet: No API, import, export or backup work yet. ### Phase 5 · An API on the same rails, and a backup you restored Build: Generate a token-authenticated REST surface from the schema, where every token maps to a role and every query runs down the same policy path as the interface. CSV import with a dry run reporting rejected rows. Full export of schema, data, attachments and audit. A scheduled backup and a restore exercised in the test suite against a fresh database. Trap: 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. Done when: A token's reachable rows equal its role's reachable rows for every role, tested pairwise. CSV import dry run reports rejected rows with reasons and imports nothing until confirmed, tested. Export covers schema, data, attachments and audit, and reimports into an empty installation with identical counts and identical physical names, tested. The restore runs in the test suite against a fresh database rather than being documented. Do not build yet: Nothing after this. ### Out of scope (and why) - Real-time multiplayer editing with presence, which is a conflict resolution problem rather than a database problem and is weeks on its own. - A general spreadsheet formula language, which is a parser, an evaluator, a dependency graph and a decade of compatibility expectations. - Hundreds of integrations and an automation builder, which is what the subscription actually pays for and is permanent maintenance. - A hosted control plane and billing, which turns a self-hosted tool into a company. ### README must contain - That labels and physical names are separate, why, and that the physical name never changes - That permissions are enforced by database policies, that the application role owns nothing, and how to verify both - What happens during a field type change, including the dry run and the shadow column option - The concurrency model: row versions, what a rejected write looks like, and where to find it in the audit - The backup schedule, the exact restore command, and the fact that the restore is exercised in the test suite ===== ARCHITECTURE.md ===== # Architecture · Knack ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | Next.js 15 with TypeScript, run behind your own authentication | the interface is the small half and it should stay that way | | Database | PostgreSQL, with one physical table per logical table | a generic key and value table gives up every constraint the database exists to provide | | Permissions | Row-level security policies generated from the role definitions | a permission enforced in a controller is a permission the next endpoint forgets | | Migrations | Every schema change is a file, applied in order, never applied by hand | a schema builder that edits the database live is a schema builder with no history | | Packaging | Docker Compose with the database, the app and a backup job | the thing you are replacing is hosted, and the hosting is most of the work | ## Modules Each module has one owner concern and a documented way to replace it. | Module | Owns | How to replace it | | --- | --- | --- | | Names | Label to physical name generation and the stored mapping. | Any generator, provided collisions are resolved before the database sees them. | | Migrations | Files, ordering, dry runs and transactional application. | Any migration tool, provided no column with data is dropped. | | Policies | Generated row security and the roles it runs under. | Not swappable for controller checks, which is the failure this phase exists to prevent. | | Views | One definition rendered four ways, with row versions. | Any front end, provided stale writes are rejected rather than merged. | | API | Token to role mapping and the same policy path. | Any protocol, provided token reach equals role reach. | ## Configuration Every runtime setting is an environment variable documented in `.env.example`, validated at startup, with a safe local default wherever one exists. - `DATABASE_URL` · required, secret · The application role. It owns no table and has no bypass attribute. - `MIGRATION_DATABASE_URL` · required, secret · The owning role, used only to apply migrations and never by request handlers. - `TRASH_PURGE_DAYS` · required · How long a deleted record stays restorable before it is really gone. - `BACKUP_DIR` · required · Where the scheduled dump lands. The restore test reads from here. - `BACKUP_KEEP` · required · Generations retained. Cheap insurance against a bad migration. - `ATTACHMENT_DIR` · required · Files attached to records, included in the export. - `MAX_IMPORT_ROWS` · required · Above this the import is refused rather than run as a web request. ## Production baseline - Security: least privilege, input validation at every boundary, secret redaction in logs, rate limits on abuse-prone paths, no invented security primitives. - Data: explicit schema and migrations, transactional writes where integrity matters, backup and restore procedures that have been exercised. - Integrations: adapters around third-party providers, idempotent webhook or job processing, bounded retries, timeouts. - Observability: structured logs with request or operation ids, an error-tracking hook, and health and readiness checks where a server exists. - Quality: unit tests for domain rules, integration tests at module boundaries, one end-to-end test of the critical path. ## Decision records For each dependency in the stack table, keep a short note: why it was chosen, its failure mode, and how it is replaced. Do not add infrastructure until a requirement in `PRODUCT.md` justifies it. ===== AGENTS.md ===== # Agent instructions · Knack product build - Read `PRODUCT.md` and `ARCHITECTURE.md` before changing code. The stack is fixed: Next.js 15 with TypeScript, run behind your own authentication, PostgreSQL, with one physical table per logical table, Row-level security policies generated from the role definitions, Every schema change is a file, applied in order, never applied by hand, Docker Compose with the database, the app and a backup job. - Implement milestone by milestone from `MILESTONES.md`; keep each change reviewable and leave the application runnable at every commit. - Treat authentication, payments, encryption, imports, webhooks and destructive actions as high-risk boundaries when present. - Never invent cryptography or silently weaken a requirement to make a check pass. - Put every external service behind an interface with a deterministic fake for tests. - Add migrations and rollback or recovery notes for every persistent data change. - Log useful operational context without credentials, tokens, passwords or personal data. - Update documentation and run every check before completing a milestone. ## Known traps - 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. - 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. - 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. - 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. - 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. ===== MILESTONES.md ===== # Delivery milestones · Knack Estimated effort: **multi-day** for the indie phases; the production-only milestones add the trust and operability layer. ## M1 · Names the database will actually accept A field label becomes a physical name that is unique, valid and permanent. ### Steps 1. Generate physical names, never pass labels through The user's label is a display string and nothing else. Files: `src/schema/names.ts`, `tests/names.test.ts` 2. Measure the limit in bytes Accented labels truncate earlier than they look. Files: `tests/names.test.ts` 3. Write a migration file for every change A builder with no migration history cannot be rolled forward twice. Files: `src/schema/migrate.ts`, `migrations/` ### Done when - [ ] Colliding truncations produce distinct names - [ ] Multibyte labels stay within the byte limit - [ ] A rename leaves the physical name and data alone - [ ] Migrations replay into an identical schema ### 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. ## M2 · Changing a field without losing the column A type change reports what it will break before it does anything. ### Steps 1. Dry-run every conversion before committing Show the rows that would fail, with their values. Files: `src/schema/convert.ts`, `tests/convert.test.ts` 2. Offer the shadow column as a first-class option The old values are the only copy of the old values. Files: `src/schema/convert.ts` 3. Wrap the change in a transaction and test the failure A half-applied schema change is the worst state to be in. Files: `tests/migration-failure.test.ts` ### Done when - [ ] A lossy conversion reports failures before changing anything - [ ] Keeping originals creates a populated shadow column - [ ] A failed migration leaves the table unchanged - [ ] No column holding data is ever dropped ### 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. ## M3 · Permissions the database enforces The rows a role cannot see are invisible to any query that role can run. ### Steps 1. Give the app a role that owns nothing Ownership is the quiet exemption everybody hits once. Files: `docker/init.sql`, `tests/role-attributes.test.ts` 2. Force row security on every generated table And assert it over the catalogue after each migration. Files: `src/schema/policies.ts`, `tests/rls-forced.test.ts` 3. Test the negative case for every role What a role cannot see is the part worth testing. Files: `tests/rls-roles.test.ts` ### Done when - [ ] The app role owns nothing and cannot bypass - [ ] Row security is enabled and forced on every table - [ ] Each role is tested for what it cannot see - [ ] A policy-less table returns zero rows ### 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. ## M4 · 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. ### Steps 1. Carry a row version through read and write Timestamps disagree between machines; a version does not. Files: `src/records/write.ts`, `tests/concurrency.test.ts` 2. Render four views from one definition Otherwise the kanban drifts from the grid within a month. Files: `src/views/render.tsx` 3. Make delete a move with a purge date A restore is the feature people discover under pressure. Files: `src/records/trash.ts`, `tests/trash.test.ts` ### Done when - [ ] A stale write is rejected with both value sets - [ ] Rejections appear in the audit - [ ] One definition drives all four view kinds - [ ] Deleted records restore with their identifier ### 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. ## M5 · An API on the same rails, and a backup you restored (production only) Tokens obey the same policies as people, and a restore has been performed rather than described. ### Steps 1. Map every token to a role, never to an exemption The API is another user, not another privilege level. Files: `src/api/auth.ts`, `tests/token-parity.test.ts` 2. Dry-run the import and report rejects A half-imported CSV is worse than a refused one. Files: `src/import/csv.ts`, `tests/import.test.ts` 3. Exercise the restore in the suite A backup nobody has restored is a hope with a cron entry. Files: `tests/restore.test.ts`, `docker/backup.sh` ### Done when - [ ] Token reach equals role reach for every role - [ ] Import dry run reports rejects and imports nothing - [ ] Export and reimport preserve counts and names - [ ] Restore is exercised in the test suite ### 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. ===== OPERATIONS.md ===== # Operations · Knack ## Backup A scheduled database dump plus the attachment directory, with generations kept. The audit table is part of the dump and is the record of who changed what, which is usually the thing somebody needs from a backup rather than the rows themselves. ## Restore Restore into a fresh database, apply no migrations, and confirm that row security is enabled and forced on every table and that the role tests still pass. A restore that comes back with policies missing is a restore that quietly opened every table. Do a restore drill before the first real user, and write the date here when it passes. ## Monitoring Migration failures, rejected stale writes per day, trash purge volume, backup age, and a periodic check over the catalogue that row security is still enabled and forced on every generated table. The last one catches the migration that created a table without its policy. ## Incident checklist The incident is data somebody could see and should not have. The catalogue check tells you whether a table lost its policy and when, the audit tells you what was read-modified in that window, and the token table tells you which non-human roles were active. 1. Contain the issue without destroying evidence or user data. 2. Record the timeline and affected scope. 3. Rotate exposed secrets and revoke compromised sessions or credentials. 4. Restore from a verified backup when needed. 5. Document the root cause, the remediation and the regression test. ## Release gate - [ ] Colliding truncated names are confirmed to produce distinct identifiers - [ ] A lossy type change is confirmed to report before acting - [ ] Row security is confirmed enabled and forced on every table - [ ] A stale write is confirmed rejected with both value sets - [ ] The restore is confirmed to run in the test suite ## Launch constraint Do not market omitted Knack capabilities as implemented. The non-goals in `PRODUCT.md` remain user-visible limitations until they are deliberately delivered. ===== .env.example ===== # Copy to .env and fill in. Never commit .env; this file documents it. # Required · secret. The application role. It owns no table and has no bypass attribute. DATABASE_URL=postgres://app:redacted@db:5432/base # Required · secret. The owning role, used only to apply migrations and never by request handlers. MIGRATION_DATABASE_URL=postgres://owner:redacted@db:5432/base # Required. How long a deleted record stays restorable before it is really gone. TRASH_PURGE_DAYS=30 # Required. Where the scheduled dump lands. The restore test reads from here. BACKUP_DIR=/backups # Required. Generations retained. Cheap insurance against a bad migration. BACKUP_KEEP=14 # Required. Files attached to records, included in the export. ATTACHMENT_DIR=./data/attachments # Required. Above this the import is refused rather than run as a web request. MAX_IMPORT_ROWS=50000
README.md
# Knack · indie build 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-day**. Work `BUILD_PLAN.md` top to bottom · every phase ends in a check that has to pass before the next one starts. ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | Next.js 15 with TypeScript, run behind your own authentication | the interface is the small half and it should stay that way | | Database | PostgreSQL, with one physical table per logical table | a generic key and value table gives up every constraint the database exists to provide | | Permissions | Row-level security policies generated from the role definitions | a permission enforced in a controller is a permission the next endpoint forgets | | Migrations | Every schema change is a file, applied in order, never applied by hand | a schema builder that edits the database live is a schema builder with no history | | Packaging | Docker Compose with the database, the app and a backup job | the thing you are replacing is hosted, and the hosting is most of the work | ## Before you start Have every one of these ready. The plan assumes them from step one. - [ ] **Docker and Docker Compose** · free - 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. - Verify: docker compose version prints a version - [ ] **PostgreSQL, current major version** · free - 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. - Verify: A query confirms the version and that row security is available - [ ] **The roles and what each one may see** · free - 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 - [ ] **A real spreadsheet you are trying to retire** · free - 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 ## Quick start Follow Phase 1 in `BUILD_PLAN.md`; it creates the project. Then copy `.env.example` to `.env` and fill in the values it documents. ## Honest limits This build deliberately 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 If one of those is essential to you, that is the reason to keep paying for Knack, and the README should say so rather than pretend.
BRIEF.md
# Build brief · Knack The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a small self-hosted database application where the tables, the views and the permissions all come from one definition to replace Knack. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: Next.js 15 with TypeScript, run behind your own authentication · the interface is the small half and it should stay that way - Database: PostgreSQL, with one physical table per logical table · a generic key and value table gives up every constraint the database exists to provide - Permissions: Row-level security policies generated from the role definitions · a permission enforced in a controller is a permission the next endpoint forgets - Migrations: Every schema change is a file, applied in order, never applied by hand · a schema builder that edits the database live is a schema builder with no history - Packaging: Docker Compose with the database, the app and a backup job · the thing you are replacing is hosted, and the hosting is most of the work ### Data model (create this before Phase 1) - 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 ### Phase 1 · Names the database will actually accept Build: Generate a physical name from a label: lowercase, transliterate, strip, truncate, and add a short suffix on collision within the table. Reject nothing back to the user; solve it in the generator. Store the mapping and never derive a name at query time. Renaming a label changes the label only. Every schema change writes a migration file. Trap: 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. Done when: Two labels that would truncate to the same identifier produce distinct physical names, tested with labels over the limit. A label containing multibyte characters produces a name within the byte limit, tested by measuring bytes rather than characters. Renaming a field changes its label and leaves its physical name and its data untouched, verified in the database. Every schema operation produces a migration file and applying the files to an empty database reproduces the schema exactly. Do not build yet: No type changes, permissions, views or API yet. ### Phase 2 · Changing a field without losing the column Build: Implement add, rename, reorder and type change. For a type change, run the conversion against every row first and report the count and sample of rows that would fail. Then require a choice: refuse, null the failures, or keep the original values in a shadow column. Never drop and recreate a column that holds data. Trap: 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. Done when: A type change that would fail on some rows reports the count and a sample before making any change, tested. Choosing to keep originals creates a shadow column containing every prior value, tested. A failed migration leaves the table exactly as it was, tested by forcing a failure mid-transaction. No code path drops a column that contains data, asserted by a test over the migration generator. Do not build yet: No permissions, views, concurrency or API yet. ### Phase 3 · Permissions the database enforces Build: Generate row-level security policies from the role and view definitions, and apply them to every generated table. Run the application as a database role that owns nothing, has no superuser and no bypass attribute. Set the tables to force row security so ownership cannot become an exemption. Write a test per role asserting both what it sees and what it does not. Trap: 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. Trap: 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. Done when: The application's database role owns no table and has neither the superuser nor the bypass attribute, asserted by a query in the suite. Every generated table has row security enabled and forced, asserted by a query over the catalogue after any schema change. Each role has a test asserting the rows it can read and, separately, that a row outside its scope returns nothing rather than an error. A table with row security enabled and no policy returns zero rows, tested. Do not build yet: No view rendering, concurrency, API or export yet. ### Phase 4 · Four views, one definition, and two people editing Build: Render the four view kinds from one definition with filters, sort and visible fields. Carry a row version on every read and reject a write whose version is stale, showing the current values beside the attempted ones. Write an audit row for every write including the rejected one. Deletion moves the record to trash with a purge date and restore puts it back with its identifier. Trap: 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. Done when: Two writes from the same read version produce one success and one rejection showing current and attempted values, tested. A rejected write appears in the audit with both sides, tested. The four view kinds render from one definition and changing the visible fields changes all four, tested. A deleted record is restorable with its original identifier until the purge date, tested at the boundary. Do not build yet: No API, import, export or backup work yet. ### Phase 5 · An API on the same rails, and a backup you restored Build: Generate a token-authenticated REST surface from the schema, where every token maps to a role and every query runs down the same policy path as the interface. CSV import with a dry run reporting rejected rows. Full export of schema, data, attachments and audit. A scheduled backup and a restore exercised in the test suite against a fresh database. Trap: 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. Done when: A token's reachable rows equal its role's reachable rows for every role, tested pairwise. CSV import dry run reports rejected rows with reasons and imports nothing until confirmed, tested. Export covers schema, data, attachments and audit, and reimports into an empty installation with identical counts and identical physical names, tested. The restore runs in the test suite against a fresh database rather than being documented. Do not build yet: Nothing after this. ### Out of scope (and why) - Real-time multiplayer editing with presence, which is a conflict resolution problem rather than a database problem and is weeks on its own. - A general spreadsheet formula language, which is a parser, an evaluator, a dependency graph and a decade of compatibility expectations. - Hundreds of integrations and an automation builder, which is what the subscription actually pays for and is permanent maintenance. - A hosted control plane and billing, which turns a self-hosted tool into a company. ### README must contain - That labels and physical names are separate, why, and that the physical name never changes - That permissions are enforced by database policies, that the application role owns nothing, and how to verify both - What happens during a field type change, including the dry run and the shadow column option - The concurrency model: row versions, what a rejected write looks like, and where to find it in the audit - The backup schedule, the exact restore command, and the fact that the restore is exercised in the test suite
AGENTS.md
# Agent instructions · Knack indie build - Read `README.md` and `BUILD_PLAN.md` before writing code. The stack is fixed: Next.js 15 with TypeScript, run behind your own authentication, PostgreSQL, with one physical table per logical table, Row-level security policies generated from the role definitions, Every schema change is a file, applied in order, never applied by hand, Docker Compose with the database, the app and a backup job. Do not substitute. - Work one phase at a time, in order. Do not start a phase until every "Done when" item of the previous one passes. - Prefer the fewest moving parts that satisfy the step. No frameworks, services or dependencies the plan does not name. - Secrets live in `.env`, never in source or logs. Keep `.env.example` current when a variable is introduced. - Do not invent cryptography, security guarantees, APIs or compliance claims. - Add a focused test for every destructive, security-sensitive or data-loss path the plan names. - Run the project checks before declaring a phase complete, and record any deliberate shortcut in the README under "Tradeoffs". ## Known traps - 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. - 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. - 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. - 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.
BUILD_PLAN.md
# Build plan · Knack 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. Phases are in dependency order. Each ends in a "Done when" list; treat an unticked item as a blocker, not a note. ## Phase 1 · Names the database will actually accept A field label becomes a physical name that is unique, valid and permanent. ### Steps 1. Generate physical names, never pass labels through The user's label is a display string and nothing else. Files: `src/schema/names.ts`, `tests/names.test.ts` 2. Measure the limit in bytes Accented labels truncate earlier than they look. Files: `tests/names.test.ts` 3. Write a migration file for every change A builder with no migration history cannot be rolled forward twice. Files: `src/schema/migrate.ts`, `migrations/` ### Done when - [ ] Colliding truncations produce distinct names - [ ] Multibyte labels stay within the byte limit - [ ] A rename leaves the physical name and data alone - [ ] Migrations replay into an identical schema ### 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. ## Phase 2 · Changing a field without losing the column A type change reports what it will break before it does anything. ### Steps 1. Dry-run every conversion before committing Show the rows that would fail, with their values. Files: `src/schema/convert.ts`, `tests/convert.test.ts` 2. Offer the shadow column as a first-class option The old values are the only copy of the old values. Files: `src/schema/convert.ts` 3. Wrap the change in a transaction and test the failure A half-applied schema change is the worst state to be in. Files: `tests/migration-failure.test.ts` ### Done when - [ ] A lossy conversion reports failures before changing anything - [ ] Keeping originals creates a populated shadow column - [ ] A failed migration leaves the table unchanged - [ ] No column holding data is ever dropped ### 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. ## Phase 3 · Permissions the database enforces The rows a role cannot see are invisible to any query that role can run. ### Steps 1. Give the app a role that owns nothing Ownership is the quiet exemption everybody hits once. Files: `docker/init.sql`, `tests/role-attributes.test.ts` 2. Force row security on every generated table And assert it over the catalogue after each migration. Files: `src/schema/policies.ts`, `tests/rls-forced.test.ts` 3. Test the negative case for every role What a role cannot see is the part worth testing. Files: `tests/rls-roles.test.ts` ### Done when - [ ] The app role owns nothing and cannot bypass - [ ] Row security is enabled and forced on every table - [ ] Each role is tested for what it cannot see - [ ] A policy-less table returns zero rows ### 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. ## 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. ### Steps 1. Carry a row version through read and write Timestamps disagree between machines; a version does not. Files: `src/records/write.ts`, `tests/concurrency.test.ts` 2. Render four views from one definition Otherwise the kanban drifts from the grid within a month. Files: `src/views/render.tsx` 3. Make delete a move with a purge date A restore is the feature people discover under pressure. Files: `src/records/trash.ts`, `tests/trash.test.ts` ### Done when - [ ] A stale write is rejected with both value sets - [ ] Rejections appear in the audit - [ ] One definition drives all four view kinds - [ ] Deleted records restore with their identifier ### 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. ## Not in this build - Real-time multiplayer editing with presence. - A general spreadsheet formula language. - An automation builder and integration catalogue. - A hosted control plane and billing. ## 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
.env.example
# Copy to .env and fill in. Never commit .env; this file documents it. # Required · secret. The application role. It owns no table and has no bypass attribute. DATABASE_URL=postgres://app:redacted@db:5432/base # Required · secret. The owning role, used only to apply migrations and never by request handlers. MIGRATION_DATABASE_URL=postgres://owner:redacted@db:5432/base # Required. How long a deleted record stays restorable before it is really gone. TRASH_PURGE_DAYS=30 # Required. Where the scheduled dump lands. The restore test reads from here. BACKUP_DIR=/backups # Required. Generations retained. Cheap insurance against a bad migration. BACKUP_KEEP=14 # Required. Files attached to records, included in the export. ATTACHMENT_DIR=./data/attachments # Required. Above this the import is refused rather than run as a web request. MAX_IMPORT_ROWS=50000
PRODUCT.md
# Knack · product brief ## Problem The core loop is buildable, but a dependable replacement becomes a real weekend or multi-day project. For Knack, build a small hosted database app with pages, forms, and role-based views. The hard boundary is managed hosting, schema builder, permissions, integrations, and support, plus data model flexibility, collaboration, and integrations. ## Product outcome A self-hosted database application whose generated names survive the database's limits, whose type changes report before they act, whose permissions are enforced by policies rather than by controllers, and whose restore has actually been run. ## Target user A builder who needs a maintainable product foundation, not a one-off demo. ## Required capabilities - Docker - PostgreSQL - self-hosted server - optional object storage for attachments ## Explicit non-goals for v1 - 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 ## Success criteria - Colliding truncated names are confirmed to produce distinct identifiers - A lossy type change is confirmed to report before acting - Row security is confirmed enabled and forced on every table - A stale write is confirmed rejected with both value sets - The restore is confirmed to run in the test suite
BRIEF.md
# Build brief · Knack The one-shot brief this plan expands. `BUILD_PLAN.md` (or `MILESTONES.md`) is the same sequence broken into steps and checks; where the two disagree, the plan wins. Build me a small self-hosted database application where the tables, the views and the permissions all come from one definition to replace Knack. Build it in phases, in the order below. Do not write the whole thing in one pass. Finish a phase, run its "Done when" check, fix what fails, and only then start the next phase. ### Stack (fixed, do not substitute) - Runtime: Next.js 15 with TypeScript, run behind your own authentication · the interface is the small half and it should stay that way - Database: PostgreSQL, with one physical table per logical table · a generic key and value table gives up every constraint the database exists to provide - Permissions: Row-level security policies generated from the role definitions · a permission enforced in a controller is a permission the next endpoint forgets - Migrations: Every schema change is a file, applied in order, never applied by hand · a schema builder that edits the database live is a schema builder with no history - Packaging: Docker Compose with the database, the app and a backup job · the thing you are replacing is hosted, and the hosting is most of the work ### Data model (create this before Phase 1) - 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 ### Phase 1 · Names the database will actually accept Build: Generate a physical name from a label: lowercase, transliterate, strip, truncate, and add a short suffix on collision within the table. Reject nothing back to the user; solve it in the generator. Store the mapping and never derive a name at query time. Renaming a label changes the label only. Every schema change writes a migration file. Trap: 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. Done when: Two labels that would truncate to the same identifier produce distinct physical names, tested with labels over the limit. A label containing multibyte characters produces a name within the byte limit, tested by measuring bytes rather than characters. Renaming a field changes its label and leaves its physical name and its data untouched, verified in the database. Every schema operation produces a migration file and applying the files to an empty database reproduces the schema exactly. Do not build yet: No type changes, permissions, views or API yet. ### Phase 2 · Changing a field without losing the column Build: Implement add, rename, reorder and type change. For a type change, run the conversion against every row first and report the count and sample of rows that would fail. Then require a choice: refuse, null the failures, or keep the original values in a shadow column. Never drop and recreate a column that holds data. Trap: 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. Done when: A type change that would fail on some rows reports the count and a sample before making any change, tested. Choosing to keep originals creates a shadow column containing every prior value, tested. A failed migration leaves the table exactly as it was, tested by forcing a failure mid-transaction. No code path drops a column that contains data, asserted by a test over the migration generator. Do not build yet: No permissions, views, concurrency or API yet. ### Phase 3 · Permissions the database enforces Build: Generate row-level security policies from the role and view definitions, and apply them to every generated table. Run the application as a database role that owns nothing, has no superuser and no bypass attribute. Set the tables to force row security so ownership cannot become an exemption. Write a test per role asserting both what it sees and what it does not. Trap: 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. Trap: 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. Done when: The application's database role owns no table and has neither the superuser nor the bypass attribute, asserted by a query in the suite. Every generated table has row security enabled and forced, asserted by a query over the catalogue after any schema change. Each role has a test asserting the rows it can read and, separately, that a row outside its scope returns nothing rather than an error. A table with row security enabled and no policy returns zero rows, tested. Do not build yet: No view rendering, concurrency, API or export yet. ### Phase 4 · Four views, one definition, and two people editing Build: Render the four view kinds from one definition with filters, sort and visible fields. Carry a row version on every read and reject a write whose version is stale, showing the current values beside the attempted ones. Write an audit row for every write including the rejected one. Deletion moves the record to trash with a purge date and restore puts it back with its identifier. Trap: 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. Done when: Two writes from the same read version produce one success and one rejection showing current and attempted values, tested. A rejected write appears in the audit with both sides, tested. The four view kinds render from one definition and changing the visible fields changes all four, tested. A deleted record is restorable with its original identifier until the purge date, tested at the boundary. Do not build yet: No API, import, export or backup work yet. ### Phase 5 · An API on the same rails, and a backup you restored Build: Generate a token-authenticated REST surface from the schema, where every token maps to a role and every query runs down the same policy path as the interface. CSV import with a dry run reporting rejected rows. Full export of schema, data, attachments and audit. A scheduled backup and a restore exercised in the test suite against a fresh database. Trap: 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. Done when: A token's reachable rows equal its role's reachable rows for every role, tested pairwise. CSV import dry run reports rejected rows with reasons and imports nothing until confirmed, tested. Export covers schema, data, attachments and audit, and reimports into an empty installation with identical counts and identical physical names, tested. The restore runs in the test suite against a fresh database rather than being documented. Do not build yet: Nothing after this. ### Out of scope (and why) - Real-time multiplayer editing with presence, which is a conflict resolution problem rather than a database problem and is weeks on its own. - A general spreadsheet formula language, which is a parser, an evaluator, a dependency graph and a decade of compatibility expectations. - Hundreds of integrations and an automation builder, which is what the subscription actually pays for and is permanent maintenance. - A hosted control plane and billing, which turns a self-hosted tool into a company. ### README must contain - That labels and physical names are separate, why, and that the physical name never changes - That permissions are enforced by database policies, that the application role owns nothing, and how to verify both - What happens during a field type change, including the dry run and the shadow column option - The concurrency model: row versions, what a rejected write looks like, and where to find it in the audit - The backup schedule, the exact restore command, and the fact that the restore is exercised in the test suite
ARCHITECTURE.md
# Architecture · Knack ## Stack | Part | Choice | Why | | --- | --- | --- | | Runtime | Next.js 15 with TypeScript, run behind your own authentication | the interface is the small half and it should stay that way | | Database | PostgreSQL, with one physical table per logical table | a generic key and value table gives up every constraint the database exists to provide | | Permissions | Row-level security policies generated from the role definitions | a permission enforced in a controller is a permission the next endpoint forgets | | Migrations | Every schema change is a file, applied in order, never applied by hand | a schema builder that edits the database live is a schema builder with no history | | Packaging | Docker Compose with the database, the app and a backup job | the thing you are replacing is hosted, and the hosting is most of the work | ## Modules Each module has one owner concern and a documented way to replace it. | Module | Owns | How to replace it | | --- | --- | --- | | Names | Label to physical name generation and the stored mapping. | Any generator, provided collisions are resolved before the database sees them. | | Migrations | Files, ordering, dry runs and transactional application. | Any migration tool, provided no column with data is dropped. | | Policies | Generated row security and the roles it runs under. | Not swappable for controller checks, which is the failure this phase exists to prevent. | | Views | One definition rendered four ways, with row versions. | Any front end, provided stale writes are rejected rather than merged. | | API | Token to role mapping and the same policy path. | Any protocol, provided token reach equals role reach. | ## Configuration Every runtime setting is an environment variable documented in `.env.example`, validated at startup, with a safe local default wherever one exists. - `DATABASE_URL` · required, secret · The application role. It owns no table and has no bypass attribute. - `MIGRATION_DATABASE_URL` · required, secret · The owning role, used only to apply migrations and never by request handlers. - `TRASH_PURGE_DAYS` · required · How long a deleted record stays restorable before it is really gone. - `BACKUP_DIR` · required · Where the scheduled dump lands. The restore test reads from here. - `BACKUP_KEEP` · required · Generations retained. Cheap insurance against a bad migration. - `ATTACHMENT_DIR` · required · Files attached to records, included in the export. - `MAX_IMPORT_ROWS` · required · Above this the import is refused rather than run as a web request. ## Production baseline - Security: least privilege, input validation at every boundary, secret redaction in logs, rate limits on abuse-prone paths, no invented security primitives. - Data: explicit schema and migrations, transactional writes where integrity matters, backup and restore procedures that have been exercised. - Integrations: adapters around third-party providers, idempotent webhook or job processing, bounded retries, timeouts. - Observability: structured logs with request or operation ids, an error-tracking hook, and health and readiness checks where a server exists. - Quality: unit tests for domain rules, integration tests at module boundaries, one end-to-end test of the critical path. ## Decision records For each dependency in the stack table, keep a short note: why it was chosen, its failure mode, and how it is replaced. Do not add infrastructure until a requirement in `PRODUCT.md` justifies it.
AGENTS.md
# Agent instructions · Knack product build - Read `PRODUCT.md` and `ARCHITECTURE.md` before changing code. The stack is fixed: Next.js 15 with TypeScript, run behind your own authentication, PostgreSQL, with one physical table per logical table, Row-level security policies generated from the role definitions, Every schema change is a file, applied in order, never applied by hand, Docker Compose with the database, the app and a backup job. - Implement milestone by milestone from `MILESTONES.md`; keep each change reviewable and leave the application runnable at every commit. - Treat authentication, payments, encryption, imports, webhooks and destructive actions as high-risk boundaries when present. - Never invent cryptography or silently weaken a requirement to make a check pass. - Put every external service behind an interface with a deterministic fake for tests. - Add migrations and rollback or recovery notes for every persistent data change. - Log useful operational context without credentials, tokens, passwords or personal data. - Update documentation and run every check before completing a milestone. ## Known traps - 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. - 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. - 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. - 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. - 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.
MILESTONES.md
# Delivery milestones · Knack Estimated effort: **multi-day** for the indie phases; the production-only milestones add the trust and operability layer. ## M1 · Names the database will actually accept A field label becomes a physical name that is unique, valid and permanent. ### Steps 1. Generate physical names, never pass labels through The user's label is a display string and nothing else. Files: `src/schema/names.ts`, `tests/names.test.ts` 2. Measure the limit in bytes Accented labels truncate earlier than they look. Files: `tests/names.test.ts` 3. Write a migration file for every change A builder with no migration history cannot be rolled forward twice. Files: `src/schema/migrate.ts`, `migrations/` ### Done when - [ ] Colliding truncations produce distinct names - [ ] Multibyte labels stay within the byte limit - [ ] A rename leaves the physical name and data alone - [ ] Migrations replay into an identical schema ### 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. ## M2 · Changing a field without losing the column A type change reports what it will break before it does anything. ### Steps 1. Dry-run every conversion before committing Show the rows that would fail, with their values. Files: `src/schema/convert.ts`, `tests/convert.test.ts` 2. Offer the shadow column as a first-class option The old values are the only copy of the old values. Files: `src/schema/convert.ts` 3. Wrap the change in a transaction and test the failure A half-applied schema change is the worst state to be in. Files: `tests/migration-failure.test.ts` ### Done when - [ ] A lossy conversion reports failures before changing anything - [ ] Keeping originals creates a populated shadow column - [ ] A failed migration leaves the table unchanged - [ ] No column holding data is ever dropped ### 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. ## M3 · Permissions the database enforces The rows a role cannot see are invisible to any query that role can run. ### Steps 1. Give the app a role that owns nothing Ownership is the quiet exemption everybody hits once. Files: `docker/init.sql`, `tests/role-attributes.test.ts` 2. Force row security on every generated table And assert it over the catalogue after each migration. Files: `src/schema/policies.ts`, `tests/rls-forced.test.ts` 3. Test the negative case for every role What a role cannot see is the part worth testing. Files: `tests/rls-roles.test.ts` ### Done when - [ ] The app role owns nothing and cannot bypass - [ ] Row security is enabled and forced on every table - [ ] Each role is tested for what it cannot see - [ ] A policy-less table returns zero rows ### 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. ## M4 · 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. ### Steps 1. Carry a row version through read and write Timestamps disagree between machines; a version does not. Files: `src/records/write.ts`, `tests/concurrency.test.ts` 2. Render four views from one definition Otherwise the kanban drifts from the grid within a month. Files: `src/views/render.tsx` 3. Make delete a move with a purge date A restore is the feature people discover under pressure. Files: `src/records/trash.ts`, `tests/trash.test.ts` ### Done when - [ ] A stale write is rejected with both value sets - [ ] Rejections appear in the audit - [ ] One definition drives all four view kinds - [ ] Deleted records restore with their identifier ### 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. ## M5 · An API on the same rails, and a backup you restored (production only) Tokens obey the same policies as people, and a restore has been performed rather than described. ### Steps 1. Map every token to a role, never to an exemption The API is another user, not another privilege level. Files: `src/api/auth.ts`, `tests/token-parity.test.ts` 2. Dry-run the import and report rejects A half-imported CSV is worse than a refused one. Files: `src/import/csv.ts`, `tests/import.test.ts` 3. Exercise the restore in the suite A backup nobody has restored is a hope with a cron entry. Files: `tests/restore.test.ts`, `docker/backup.sh` ### Done when - [ ] Token reach equals role reach for every role - [ ] Import dry run reports rejects and imports nothing - [ ] Export and reimport preserve counts and names - [ ] Restore is exercised in the test suite ### 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.
OPERATIONS.md
# Operations · Knack ## Backup A scheduled database dump plus the attachment directory, with generations kept. The audit table is part of the dump and is the record of who changed what, which is usually the thing somebody needs from a backup rather than the rows themselves. ## Restore Restore into a fresh database, apply no migrations, and confirm that row security is enabled and forced on every table and that the role tests still pass. A restore that comes back with policies missing is a restore that quietly opened every table. Do a restore drill before the first real user, and write the date here when it passes. ## Monitoring Migration failures, rejected stale writes per day, trash purge volume, backup age, and a periodic check over the catalogue that row security is still enabled and forced on every generated table. The last one catches the migration that created a table without its policy. ## Incident checklist The incident is data somebody could see and should not have. The catalogue check tells you whether a table lost its policy and when, the audit tells you what was read-modified in that window, and the token table tells you which non-human roles were active. 1. Contain the issue without destroying evidence or user data. 2. Record the timeline and affected scope. 3. Rotate exposed secrets and revoke compromised sessions or credentials. 4. Restore from a verified backup when needed. 5. Document the root cause, the remediation and the regression test. ## Release gate - [ ] Colliding truncated names are confirmed to produce distinct identifiers - [ ] A lossy type change is confirmed to report before acting - [ ] Row security is confirmed enabled and forced on every table - [ ] A stale write is confirmed rejected with both value sets - [ ] The restore is confirmed to run in the test suite ## Launch constraint Do not market omitted Knack capabilities as implemented. The non-goals in `PRODUCT.md` remain user-visible limitations until they are deliberately delivered.
.env.example
# Copy to .env and fill in. Never commit .env; this file documents it. # Required · secret. The application role. It owns no table and has no bypass attribute. DATABASE_URL=postgres://app:redacted@db:5432/base # Required · secret. The owning role, used only to apply migrations and never by request handlers. MIGRATION_DATABASE_URL=postgres://owner:redacted@db:5432/base # Required. How long a deleted record stays restorable before it is really gone. TRASH_PURGE_DAYS=30 # Required. Where the scheduled dump lands. The restore test reads from here. BACKUP_DIR=/backups # Required. Generations retained. Cheap insurance against a bad migration. BACKUP_KEEP=14 # Required. Files attached to records, included in the export. ATTACHMENT_DIR=./data/attachments # Required. Above this the import is refused rather than run as a web request. MAX_IMPORT_ROWS=50000
$ choose a build depth, inspect the files, then open the complete pack in your agent
People still pay for Knack because teams pay when a flexible database becomes a shared operational system with integrations and years of accumulated business logic. The recurring cost buys schema changes, formulas, permissions, imports, attachments, views, APIs, concurrency, backups, and migrations, not just the visible interface.
xmanaged hosting, schema builder, permissions, integrations, and support
xreal-time multiplayer
xautomation and integration breadth
xscale and formula depth
xenterprise permissions
share on X ↗"I just replaced Knack ($59/mo) with one prompt"
Don't feel like building it? These folks already made it free.
BudibaseInternal apps, forms, automations, and portals in one box; much closer to Knack than its developer branding suggests.open source↗
NocoBaseA database-backed app builder with pages, forms, roles, workflows, and enough plugins to become your next maintenance hobby.open source↗
REI3A sober business-app builder with forms, reports, roles, workflows, and no per-user meter.open source↗all 5 free alternatives to Knack →· no votes, no pay-to-list · just what's real
Knack pricing
| plan | monthly | annual (per mo) | what you get |
|---|---|---|---|
| starter | $59/workspace | $49/workspace | 20,000 records; 3 apps; 2 GB storage; 25 AI credits; 500 Flows transactions; 1,000 API calls/day; 1 shared builder. |
| pro | $130/workspace | $110/workspace | 50,000 records; unlimited apps; 10 GB storage; 1-month history; 50 AI credits; 2,000 Flows transactions; 5,000 API calls/day. |
| corporate | $300/workspace | $250/workspace | Starts with 125,000 records and scales to 2.5 million; 50 GB storage; 2-month history; 100 AI credits; 5,000 Flows transactions; 10,000 API calls/day. |
| enterprise | custom | — | 2.5 million+ records; 3-month logs; custom storage, API and usage limits. |
free tierno permanent free tier; free trial offered with no card, but the live pricing page does not state its duration
billingmonthly month-to-month + annual; annual rates are about 17% lower and billed for the year
hidden costsRecord/storage add-ons cost $42/month for 15,000 records + 5 GB on Pro (maximum 2) or $90/month for 40,000 records + 20 GB on Corporate (maximum 2); Pro SSO is $55/month; paid plans can auto-add Flows capacity at unpublished prices; each app user consumes a database record.
verified 2026-08-11 · source ↗
Vibecode Knack
Kinda. The core of Knack is buildable in a weekend with the prompt on this page, but there are real gaps: managed hosting, schema builder, permissions, integrations, and support, real-time multiplayer. Read the honest list above before committing.
How much does Knack cost?
Knack costs about $59/month (Starter, checked 2026-07-31), which is $708 per year.
What do I lose by replacing Knack?
Honestly: managed hosting, schema builder, permissions, integrations, and support; real-time multiplayer; automation and integration breadth; scale and formula depth; enterprise permissions. If any of those are load-bearing for you, keep paying.
Is there an open-source alternative to Knack?
Yes: Budibase (Internal apps, forms, automations, and portals in one box; much closer to Knack than its developer branding suggests.) NocoBase (A database-backed app builder with pages, forms, roles, workflows, and enough plugins to become your next maintenance hobby.) REI3 (A sober business-app builder with forms, reports, roles, workflows, and no per-user meter.) All 5 curated free alternatives are at vibecodeit.com/knack/alternatives. The prompt is for when you want it exactly your way.