Build a lead-capture app with a small private CRM

A small-business owner can ask an AI coding agent to build a public inquiry form and a private place for staff to manage the resulting leads. The form is only the front door. This guide supplies a complete build brief, a PostgreSQL migration and an API contract for the contact records, separate inquiries, assignment queue, pipeline, notes and history behind it. The components are drawn from official documentation checked on 22 October 2026. BIG CHANGE has not built or tested this app.

The big change

• What changed: Replit Agent can plan and generate an application from a detailed brief. An owner can give it explicit data, access and transaction rules alongside the screen descriptions.
• Why it matters: A second question from one email address should remain a separate inquiry. Staff need a durable contact and a single open lead to work, with ownership and history that survive a reload. Those outcomes depend on server and database behavior, not the appearance of the form.
• What to watch: A public host must let an anonymous prospect reach the form while the same app denies private routes to everyone outside the current staff roster. Repeated submissions, lock waits, quota boundaries and revoked access need actual checks before real intake.

Before you start

This is for an owner authorized to collect specific customer inquiries and enroll named staff. Decide what questions the business may collect, the exact public notice, who may respond, how long records and backups remain, and how a person can ask for content removal. Obtain the relevant privacy and contact-policy review before using real data. Start the build with synthetic names, addresses and questions. Handle each inquiry only under the owner-reviewed notice, contact policy and applicable permissions. Submission alone does not establish marketing consent or cause this app to send anything.

You need access to Replit Agent, its managed Clerk identity integration for staff, PostgreSQL, and a published host that admits anonymous form visitors. Check those features, data location, charges, terms and storage in your own account. Replit documents distinct Development and Production databases; inspect your project's actual bindings. Choose Node.js 22 or 24 only if the actual development and published runtimes and locked React, Express, @clerk/express and database-client versions support it. Node's release table lists those lines as LTS on this check date. Hold the build if no compatible supported combination is available.

The AI agent writes code during development. This design has no runtime language model, lead scoring, generated outreach, HubSpot integration or outbound message service. Staff record a pipeline stage manually; contacted and won are entries, not evidence of delivery or payment.

1. Give the agent the complete app task

Open Replit Agent in Plan mode. Supply Appendix A: copyable build brief, Appendix B: SQL migration and Appendix C: API, transaction and acceptance contract together. Ask for a React public form and staff interface, Express server, managed Clerk staff sign-in and PostgreSQL persistence. Review the plan and generated source before allowing data or release changes. A landing page, a form mockup or a private CRM screen alone does not complete the task.

The plan should show how an owner creates a form and enrolls agents, how an anonymous inquiry creates a contact and lead, how the owner assigns it, and how an agent reads distinct questions, adds manual notes and moves a lead through the pipeline. It must also identify the public and private routes, first-owner seed, database migration, conflict handling, redaction and recovery tests. If the generated plan omits one, revise it before running the build.

2. Establish the public and staff boundary

Use a host that actually exposes the prospect form to signed-out visitors. Replit's publishing guide distinguishes public access from invitation-restricted hosting. An invite-only host would stop the anonymous visitor before your form. On a public host, private staff pages and APIs need their own server-side checks.

Inspect the installed Replit-managed Clerk integration. Mount its exact generated proxy routes before Express body parsers. Keep sign-in, sign-up, callback, required authentication transport and static bootstrap accessible without app database membership or the app's custom staff CSRF token; retain the identity provider's own protections. Do not assume a proxy or callback path from a different template. Allow the public form GET and POST and a data-free landing. Fail closed for other paths, including direct staff HTML URLs and client-side route fallbacks.

For every staff route, verify the Clerk session, then look up the user's current active SQL membership and role before record access. A provider sign-up is only an identity, never a CRM enrollment. Seed the first owner through authenticated administration with an exact verified Clerk user ID for that environment. The owner enrolls named existing users only after backend user lookup. The owner sees the queue; an agent sees currently assigned leads and their linked contact, inquiries, notes and history. A revoked or reassigned agent loses access on the next request. Use the same gate for HTML and API responses.

Expected check: an anonymous visitor can reach the actual published form but not /app or /api/staff; a signed-in unlisted user sees an empty workspace list; an agent cannot open an unassigned lead by guessing its URL. Missing, foreign, inactive and revoked workspaces return the same generic 404 after authentication. A current member attempting an owner-only function gets 403. Staff mutations additionally require the exact allowed Origin and a session-bound CSRF token. The public form and managed sign-in transport do not receive that staff token.

3. Keep contacts, inquiries and leads distinct

Apply Appendix B only after inspecting it against a new, empty Development database. It stores a form, current member roster, contacts, submissions, leads, notes and restricted events. Each valid inquiry is its own submission. The contact match uses a deliberately limited normalized-email key: Unicode NFC local part with its case, plus signs and dots preserved, followed by a lowercased ASCII-serialized domain. The key is useful for an exact repeat under this policy; it does not verify the mailbox or prove two addresses belong to one person. Staff reconcile suspected aliases after checking the person and records.

For a different submission key with the same normalized address, keep the new question and link it to the same contact's one open lead. If the previous lead is closed, create a fresh unassigned new lead without changing the old one. The database's unique contact and partial open-lead constraints help enforce this when requests overlap. PostgreSQL documents unique constraints and conflict handling, but this particular workflow is the proposed app contract, not a tested vendor feature.

The public form must show the approved purpose and notice before the prospect sends anything. Save that exact notice text and version with each submission. Editing the form later changes what a new visitor sees without rewriting earlier inquiries. Keep prospect text out of immutable event records, request URLs, analytics and logs. Show it only to authorized staff.

4. Check retries and the hourly limit under contention

The browser creates one random UUID request key per attempted inquiry and reuses it when a response is uncertain. The server binds it to the normalized form version, name, email key and question. The same key and binding returns the same generic HTTP 202 confirmation; changing the binding under that key returns 409. A fresh key is a new inquiry even from an existing contact.

Check the order in Appendix C carefully. A first key lookup happens before mutable admission checks. The server then takes the workspace lock and repeats the lookup immediately after that wait, and takes the form lock and repeats it immediately after that wait. Each repeat precedes form activation, version, hourly quota, contact or lead checks. Thus an identical retry returns its already committed 202 even when the first request exhausted the cap or a form edit landed while the retry waited. A changed binding remains 409. PostgreSQL row locks provide the serialization mechanism; the specific order is this guide's requirement.

For a genuinely new request, read the database clock once after both locks and retry checks. Derive one UTC-hour bucket from that clock_timestamp() value, and use that same bucket for quota admission and increment in the transaction. A transaction-start timestamp could describe the hour before a lock wait. PostgreSQL distinguishes its timestamp functions. A rollback leaves no accepted submission and no quota charge. The cap counts committed new submissions; it is not a claim of spam prevention and may reject legitimate traffic.

Use synthetic tests: replay one key after a simulated lost response; change its payload; hold a retry behind workspace and form locks while the first request commits, then change cap, version and active state in separate cases. Cross a UTC hour with a new key waiting on a lock and check the chosen bucket. Race two different keys for the same normalized email: expect two submissions, one contact and one open lead. Inspect database rows as well as the confirmation. If a live response is lost, reuse the original key or have authorized staff inspect the record before starting another attempt.

5. Work the lead through a private pipeline

The owner assigns an open lead to an active member and can reassign it. The assigned agent sees only that lead's current contact, distinct inquiries, notes and restricted history. The agent advances one step at a time from new to qualified, contacted and proposal, and can add a manual note. The owner can correct an open stage and close it as won or lost. Each change checks the expected version and current role under the shared lock order, updates the row and appends a metadata event in one transaction. If two editors act on the same version, one succeeds and the other receives 409 with unsaved text retained for comparison.

Check both directions of a reassignment: the new agent gets the lead and the previous agent loses direct read and write access. Test an agent write waiting while the owner revokes that agent. Once revocation commits first, the waiting write must recheck membership and fail. Read the event order after a restart. An event can omit question and note text while its IDs and staff references remain linkable and restricted.

6. Check redaction, persistence and the actual host

The owner-only redaction route, after a real retention or rights decision, clears direct contact name and email fields, submission name, email and question, and linked note bodies in one transaction. The schema retains contact, lead and submission IDs, request keys and binding digests, staff IDs, notice snapshots and restricted chronology. Those fields may still identify or link people. Field redaction is neither complete anonymization nor a legal-erasure certification; decide backup expiry and restoration handling separately with the relevant reviewer.

Reload and restart the app. Check that permitted records, assignment, versions, distinct inquiries, note and stage history, notice snapshots and redaction state persist. Repeat direct URL access checks on the actual public published host with signed-out, unlisted, assigned, unassigned, owner, revoked and foreign-workspace identities. Inspect Development/Production separation, owner seed, secrets, backup restoration, body limits, quotas and actual charges. Record each observed result. Hold real inquiry collection if the public form or private route boundary fails, a required transaction check is unobserved, or the business has not approved its data and response policy. Nothing in this guide records a successful application or release test.

The real-estate flyer and inquiry form guide covers an existing form for a listing. The Notion and HubSpot follow-up guide creates a checked draft from existing records. This guide builds the reusable intake and staff CRM app behind new inquiries.

Appendix A: copyable build brief

Give the agent this brief with Appendices B and C. Substitute only your own authorized business purpose, notice, staff and environment details. Keep customer data and credentials out of the planning prompt.

This brief is an original documentation-only reference for a small-business owner to give an AI coding agent. No app, account, customer form, lead, email, permission test, deduplication race, release or backup was run by BIG CHANGE. Supply permitted synthetic contacts and questions for the build; do not put customer data or secrets into a planning prompt. The complete public package is this brief, Appendices B and C with the numbered reader checks above. A copied form mockup alone does not deliver the task.

Build a persistent lead-capture app with CRM lite. A prospect opens a public, business-approved inquiry form and submits name, email and a question. A private staff workspace stores each inquiry, links exact-repeat identities to one contact, keeps one open lead per contact, shows an owner-controlled assignment queue, supports agent notes and a reviewable pipeline from new through qualification/contact/proposal to won or lost, and retains restricted stage/assignment history without direct prospect-content fields. Closed leads remain historical; a genuinely new later inquiry can create another lead. Staff can search/filter their authorized leads and read separate submissions after reload. The owner can adjust the form/notice and member roster, correct a contact under human review, and perform governed field redaction. This is an AI-assisted app build; no runtime model is needed to classify prospects, infer intent, send a message or score sales likelihood.

Use Replit Agent Plan mode with the whole public contract to build a React UI, Express server, Replit-managed Clerk for staff identity, and Replit SQL/PostgreSQL for durable records. Use an Active/Maintenance LTS Node release (22 or 24 as checked on 22 October 2026) only if the actual Replit development/published runtime and the locked React, Express, @clerk/express and PostgreSQL-client versions are compatible. Inspect the generated code, package lock, managed identity setup, database separation, hosting mode, usage charges, storage, backup/retention and privacy terms in the reader's real account. HOLD if a required integration or compatible runtime is unavailable. No package was installed and no account capacity or price was measured here.

The public form and private CRM are different surfaces

The one-host recipe needs a publicly reachable form, so select a host mode that actually permits an anonymous prospect to open it. The signed-out landing, Clerk sign-in/sign-up, their actual installed provider callback and authentication transport, required static bootstrap, exact managed proxy paths and form GET/POST are narrowly public at the app layer. Discover those exact generated paths; do not invent a fixed callback or use a public wildcard. Keep provider security in place while exempting that transport from app SQL membership and the custom staff CSRF token. The managed Clerk proxy must mount before body parsers. The public form contains no staff data and shows the current owner-reviewed purpose/privacy notice. It has field and UTF-8 request-size bounds, an exact form version, a durable per-form quota and an idempotent submission key. Anonymous form access never grants a staff session or SQL query path. A private host invitation is not a substitute for CRM membership, and an invite-only host would block a public prospect. All staff HTML, API and SPA fallback routes require a verified Clerk session and current SQL workspace membership/role. Test both halves at the actual published host.

The first workspace owner is seeded through authenticated database/project administration using an exact verified Clerk user ID, separately in Development and Production; no public bootstrap. The owner enrolls named existing Clerk users after checking the humans. Owner sees the whole queue; an agent sees only currently assigned leads and their contact/submissions/history. An unlisted or revoked Clerk account has no staff data. A foreign, absent or inactive workspace receives the same generic 404 after authentication, before any record lookup. No browser-provided owner ID or hidden field controls access.

Public submissions are inquiries, not marketing consent. The notice tells visitors what the business will store and that a human may respond to this specific question. The app does not send email/SMS, add the contact to a mailing list, create a campaign, draft a message, synchronize HubSpot or automate follow-up. A human checks the real request and applicable permissions before using a separate communication channel. The form snapshots its exact notice text and version with each inquiry, so a later form edit does not rewrite that record. Do not use an invented prospect, prechecked marketing choice or proxy for legal approval. The owner obtains the actual privacy/retention wording and contact policy for its jurisdiction and business before collecting real inquiries.

Exact records and history

The server's documented normalization keeps the local part's case and plus/dot characters, normalizes Unicode NFC, and lowercases the ASCII-serialized domain. The resulting workspace/email key is unique for a non-erased contact. That is an explicit limited match rule, not mailbox verification or a judgment that two spellings identify one human. A second valid submission from the same key remains a separate inquiry linked to the same open lead. A new inquiry after a won/lost close creates a fresh unassigned lead; it does not rewrite the old pipeline. The client-generated submission key only deduplicates transport retries of the same payload. After each workspace/form lock wait, the server re-reads that key and immutable payload binding before current active, version, quota, contact or lead checks: an identical retry returns its existing generic 202 even if those conditions changed, while a changed binding is 409. A truly new admission uses one database clock reading after the locks to select its UTC-hour quota bucket for both check and increment; rollback leaves no charge. A timeout is unknown until a same-key readback or authorized staff inspection. The app never merges case variants, aliases or contacts from a name match automatically.

An owner assigns an open lead to one active staff member. The assigned agent can move it through the documented forward stages and add a manual note; an owner can review/correct an open stage, reassign it or close it won/lost. Each mutation uses a state-version check and a workspace/member/record lock order, then appends a fixed metadata event in the same transaction. Two simultaneous changes with the same expected version give one commit and one 409; the losing UI keeps its unsaved text for human comparison. contacted, proposal and won are human-entered CRM states, not proof that a message was delivered, a proposal accepted or revenue received. Keep direct prospect content in gated contact/submission/note tables, not immutable events or logs. Owner-approved erasure redacts the named contact/submission/note content fields transactionally; contact/lead/submission IDs, request bindings/digests, staff Clerk IDs, notice snapshots and restricted event chronology remain potentially linkable or personal. This is not complete anonymization or a legal-erasure certification. The owner must decide retention and backup expiry with the relevant reviewer before real intake.

Screens and release check

The UI needs a document-free landing and staff sign-in; an active form with visible notice and submission confirmation; private workspace selection; owner unassigned/all-lead views, form and member settings, assignment/close controls and audit; agent assigned-lead list/detail, distinct inquiries, manual notes and pipeline action; contact details only to authorized staff. Show exact stage, owner, timestamps and history. Display received only after a committed 202, and saved edits only after committed version readback. Errors must retain unsaved form/note text. Escape all public/staff strings; never render visitor HTML or load visitor-supplied remote assets.

Before real intake, the owner checks the public form on the selected public host and every protected staff route by direct URL with signed-out, unlisted, assigned, unassigned, owner, revoked and foreign-workspace identities. Check duplicate and concurrent submissions, stage conflicts, form changes/quota, readback after restart, Development/Production separation, secrets, restricted staff data, backup/restore and owner-approved privacy/retention. If an actual account cannot provide a public form and isolated staff data at that host, HOLD this full app outcome and choose a newly verified architecture; do not silently publish a private-only form or expose the CRM to make the form work. All test results and costs remain for the reader to observe.

Sources: Replit Agent, Replit managed Clerk, Replit SQL environments, Replit publishing access, Clerk Express session auth, Clerk user lookup, PostgreSQL INSERT/ON CONFLICT, PostgreSQL row locks, PostgreSQL constraints, PostgreSQL date functions, Node domainToASCII and Node release status. These manuals document components, not the success of this proposed app.

Appendix B: SQL migration for a new PostgreSQL database

Inspect this complete migration before using it with an empty Development database. Verify the separate Production environment and owner seed before release.

-- Reference migration for a NEW empty PostgreSQL database. The reader must
-- inspect Development and Production separately before applying it. IDs are
-- generated in trusted server code with crypto.randomUUID(), not by clients.
BEGIN;

CREATE TABLE crm_workspace (
  id uuid PRIMARY KEY,
  slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,79}$'),
  name text NOT NULL CHECK (char_length(name) BETWEEN 1 AND 120),
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE crm_member (
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  clerk_user_id text NOT NULL,
  role text NOT NULL CHECK (role IN ('owner','agent')),
  active boolean NOT NULL DEFAULT true,
  joined_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,clerk_user_id)
);

CREATE TABLE crm_form (
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  id uuid NOT NULL,
  slug text NOT NULL CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,79}$'),
  title text NOT NULL CHECK (char_length(title) BETWEEN 1 AND 120),
  description text NOT NULL DEFAULT '' CHECK (char_length(description) <= 500),
  notice_version bigint NOT NULL DEFAULT 1 CHECK (notice_version > 0),
  notice_text text NOT NULL CHECK (char_length(notice_text) BETWEEN 20 AND 2000),
  active boolean NOT NULL DEFAULT false,
  max_submissions_per_hour integer NOT NULL DEFAULT 100 CHECK (max_submissions_per_hour BETWEEN 1 AND 10000),
  state_version bigint NOT NULL DEFAULT 1 CHECK (state_version > 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,id),
  UNIQUE (slug)
);

CREATE TABLE crm_form_window (
  workspace_id uuid NOT NULL,
  form_id uuid NOT NULL,
  window_start timestamptz NOT NULL,
  submission_count integer NOT NULL CHECK (submission_count BETWEEN 0 AND 10000),
  PRIMARY KEY (workspace_id,form_id,window_start),
  FOREIGN KEY (workspace_id,form_id) REFERENCES crm_form(workspace_id,id) ON DELETE RESTRICT
);

CREATE TABLE crm_contact (
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  id uuid NOT NULL,
  email_display text,
  email_key text,
  name text,
  state_version bigint NOT NULL DEFAULT 1 CHECK (state_version > 0),
  erased_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id,id),
  UNIQUE (workspace_id,email_key),
  CHECK ((erased_at IS NULL AND email_display IS NOT NULL AND email_key IS NOT NULL
          AND name IS NOT NULL AND char_length(name) BETWEEN 1 AND 120
          AND char_length(email_display) BETWEEN 3 AND 254
          AND char_length(email_key) BETWEEN 3 AND 254)
      OR (erased_at IS NOT NULL AND email_display IS NULL AND email_key IS NULL AND name IS NULL))
);

CREATE TABLE crm_lead (
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  id uuid NOT NULL,
  contact_id uuid NOT NULL,
  owner_user_id text,
  status text NOT NULL CHECK (status IN ('open','closed')),
  stage text NOT NULL CHECK (stage IN ('new','qualified','contacted','proposal','won','lost')),
  state_version bigint NOT NULL DEFAULT 1 CHECK (state_version > 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  closed_at timestamptz,
  PRIMARY KEY (workspace_id,id),
  UNIQUE (workspace_id,id,contact_id),
  FOREIGN KEY (workspace_id,contact_id) REFERENCES crm_contact(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,owner_user_id) REFERENCES crm_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
  CHECK ((status='open' AND stage IN ('new','qualified','contacted','proposal') AND closed_at IS NULL)
      OR (status='closed' AND stage IN ('won','lost') AND closed_at IS NOT NULL))
);
CREATE UNIQUE INDEX crm_one_open_lead_per_contact ON crm_lead(workspace_id,contact_id) WHERE status='open';
CREATE INDEX crm_lead_owner_stage ON crm_lead(workspace_id,owner_user_id,status,stage,updated_at DESC);

-- Raw public submissions are retained as separate inquiries. Request keys
-- deduplicate delivery retries only; they do not collapse distinct questions.
-- PII is mutable solely for the owner-controlled erasure operation.
CREATE TABLE crm_submission (
  workspace_id uuid NOT NULL,
  id uuid NOT NULL,
  form_id uuid NOT NULL,
  contact_id uuid NOT NULL,
  lead_id uuid NOT NULL,
  request_key uuid NOT NULL,
  binding_sha256 char(64) NOT NULL CHECK (binding_sha256 ~ '^[0-9a-f]{64}$'),
  submitted_name text,
  submitted_email text,
  question text,
  form_title_at_submit text NOT NULL CHECK (char_length(form_title_at_submit) BETWEEN 1 AND 120),
  notice_version_at_submit bigint NOT NULL CHECK (notice_version_at_submit > 0),
  notice_text_at_submit text NOT NULL CHECK (char_length(notice_text_at_submit) BETWEEN 20 AND 2000),
  communication_scope text NOT NULL DEFAULT 'inquiry_response_only'
    CHECK (communication_scope='inquiry_response_only'),
  submitted_at timestamptz NOT NULL DEFAULT now(),
  redacted_at timestamptz,
  PRIMARY KEY (workspace_id,id),
  UNIQUE (workspace_id,id,lead_id),
  UNIQUE (workspace_id,form_id,request_key),
  FOREIGN KEY (workspace_id,form_id) REFERENCES crm_form(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,contact_id) REFERENCES crm_contact(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,lead_id,contact_id) REFERENCES crm_lead(workspace_id,id,contact_id) ON DELETE RESTRICT,
  CHECK ((redacted_at IS NULL AND submitted_name IS NOT NULL AND submitted_email IS NOT NULL
          AND char_length(submitted_name) BETWEEN 1 AND 120
          AND char_length(submitted_email) BETWEEN 3 AND 254
          AND question IS NOT NULL AND char_length(question) BETWEEN 1 AND 2000)
      OR (redacted_at IS NOT NULL AND submitted_name IS NULL AND submitted_email IS NULL AND question IS NULL))
);
CREATE INDEX crm_submission_lead_time ON crm_submission(workspace_id,lead_id,submitted_at DESC);

CREATE TABLE crm_note (
  workspace_id uuid NOT NULL,
  id uuid NOT NULL,
  lead_id uuid NOT NULL,
  author_user_id text NOT NULL,
  body text,
  created_at timestamptz NOT NULL DEFAULT now(),
  redacted_at timestamptz,
  PRIMARY KEY (workspace_id,id),
  FOREIGN KEY (workspace_id,lead_id) REFERENCES crm_lead(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,author_user_id) REFERENCES crm_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
  CHECK ((redacted_at IS NULL AND body IS NOT NULL AND char_length(body) BETWEEN 1 AND 2000)
      OR (redacted_at IS NOT NULL AND body IS NULL))
);

-- Event data contains IDs and fixed enum values only, never a prospect's
-- name, email, question, free-text note or copied provider payload.
CREATE TABLE crm_event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  lead_id uuid NOT NULL,
  actor_user_id text,
  action text NOT NULL CHECK (action IN ('intake','assign','stage','note')),
  submission_id uuid,
  old_stage text CHECK (old_stage IS NULL OR old_stage IN ('new','qualified','contacted','proposal','won','lost')),
  new_stage text CHECK (new_stage IS NULL OR new_stage IN ('new','qualified','contacted','proposal','won','lost')),
  old_owner_user_id text,
  new_owner_user_id text,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (workspace_id,lead_id) REFERENCES crm_lead(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,actor_user_id) REFERENCES crm_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,submission_id,lead_id) REFERENCES crm_submission(workspace_id,id,lead_id) ON DELETE RESTRICT
);
CREATE INDEX crm_event_lead_time ON crm_event(workspace_id,lead_id,id DESC);

CREATE TABLE crm_admin_event (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  workspace_id uuid NOT NULL REFERENCES crm_workspace(id) ON DELETE RESTRICT,
  actor_user_id text NOT NULL,
  action text NOT NULL CHECK (action IN ('member_add','member_change','form_create','form_change','contact_change','erase')),
  target_user_id text,
  form_id uuid,
  contact_id uuid,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (workspace_id,actor_user_id) REFERENCES crm_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,form_id) REFERENCES crm_form(workspace_id,id) ON DELETE RESTRICT,
  FOREIGN KEY (workspace_id,contact_id) REFERENCES crm_contact(workspace_id,id) ON DELETE RESTRICT
);
CREATE INDEX crm_admin_event_time ON crm_admin_event(workspace_id,id DESC);

CREATE FUNCTION crm_reject_event_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'CRM event history is append-only';
END;
$$;
CREATE TRIGGER crm_event_immutable BEFORE UPDATE OR DELETE ON crm_event
  FOR EACH ROW EXECUTE FUNCTION crm_reject_event_change();
CREATE TRIGGER crm_admin_event_immutable BEFORE UPDATE OR DELETE ON crm_admin_event
  FOR EACH ROW EXECUTE FUNCTION crm_reject_event_change();
COMMIT;

-- Seed the first workspace/owner through authenticated administration using
-- an exact verified Clerk user ID in THIS environment; no HTTP bootstrap.
-- INSERT INTO crm_workspace(id,slug,name) VALUES ($1,$2,$3);
-- INSERT INTO crm_member(workspace_id,clerk_user_id,role) VALUES ($1,$4,'owner');

Appendix C: API, transaction and reader acceptance contract

This is a documentation-only implementation contract, not an executed app. Use Appendix B in a new database after checking the generated server, locked dependencies and environment. All SQL is parameterized on one checked-out connection per transaction. The server generates UUIDs with Node crypto.randomUUID(); browser-supplied team, contact, lead, actor or ownership IDs never confer access. Render submitted strings as escaped text, never HTML. Do not put contact fields, questions, secrets or error stack traces in URLs, analytics or logs. Private HTML/API responses use Cache-Control: private, no-store.

Route mounting, identity and public boundary

At a public Replit host the public inquiry form can be reached by prospects. Replit-host identity and the managed Clerk app identity are separate: a private/invite-only host would block non-invited prospects before the app form, so this one-host recipe uses public hosting plus private app-level staff routes. The owner must check the actual publishing mode and domain; if public submission and protected staff routing cannot both be verified, HOLD real intake. Mount the Replit-managed Clerk proxy at its installed exact paths before Express body parsers. Then mount bounded JSON parsers, clerkMiddleware(), and explicit routes. App-level unauthenticated allowlist: document-free /, /sign-in, /sign-up, their actual installed provider callback and authentication transport, required static bootstrap and exact managed proxy paths, plus GET /f/:formSlug and POST /api/public/forms/:formSlug/submissions. Discover the generated paths rather than assuming a fixed callback; use no broad public wildcard. The provider callback, proxy and sign-in transports must work without app SQL membership or the staff custom CSRF token while retaining the installed identity integration's security controls. The public form shows only its title, description, question/name/email fields and current notice; it never embeds staff JSON, contact IDs, source maps or a private SPA bootstrap. Unknown routes and SPA fallbacks fail closed.

Every /app and /api/staff route, including direct HTML URLs and client-router fallbacks, calls getAuth(req, { acceptsToken: 'session_token' }) after clerkMiddleware() and requires authenticated user and session IDs. Missing/invalid session or machine token is 401. GET /api/staff/workspaces shows only active workspaces with active SQL membership; an authenticated unlisted user gets an empty list. For :workspaceId routes, validate UUID syntax (400), then check active workspace joined to current active crm_member for the server-derived Clerk user before any lead/contact/form/note lookup. Absent, foreign, inactive workspace and revoked membership share one generic 404 status/body. An active member on an owner-only route gets 403. An agent who does not currently own a lead gets generic 404 just like an absent/foreign lead. Do not reveal counts, titles or existence before the gate. Clerk profile claims are not CRM roles. Seed the first workspace/owner through authenticated administration using a verified Production-or-Development Clerk user ID; no browser bootstrap or self-promotion.

Browser staff POST/PATCH requests also require exact allowed Origin and a short-lived HMAC-signed CSRF token bound to verified Clerk session ID, user ID, expiry and nonce; no secret in browser code. GET /api/staff/csrf serves active members only in a no-store response. The public form has no privileged session or app-CSRF grant: its POST is bounded, checks same-origin browser headers when present, exposes no CORS credential access, and uses the durable quota below. Origin is not bot proof; no anti-spam success is claimed. Staff JSON requests cap serialized UTF-8 at 32 KiB and public submissions at 16 KiB before parsing; field character limits are additional, so not every maximum-code-point field combination fits the serialized byte cap. Return 413 and preserve unsaved client text. Unknown JSON fields, malformed UUIDs, invalid strings and missing versions get 400. Use only server-side environment secrets, HTTPS and the actual selected production database.

Public intake and exact deduplication

An owner creates and activates a form with the exact owner-approved privacy notice. The server generates a globally unique public slug as f- plus 32 lowercase random hexadecimal characters from crypto.randomBytes(16); it is a locator, not an authorization secret. The form says the submitted name, email and question are retained for a human response to this inquiry; it does not present record creation as marketing consent. No promotional checkbox or mailing-list enrollment, autoresponse, email, SMS, calendar invite, HubSpot synchronization or provider send is implemented. A human must separately decide whether and how the business may reply under its policy and applicable law. The public GET returns current formStateVersion and noticeVersion; a prospect can read the notice before submitting. Public POST body is exactly {formStateVersion,requestKey,name,email,question}. The client makes one random UUID requestKey per submission attempt and reuses it after an uncertain response. Name is 1–120 Unicode code points; question 1–2,000; email at most 254; no attachments or rich text.

The server trims surrounding Unicode whitespace and accepts an intentionally bounded, unquoted email subset: split at exactly one @; local part 1–64 Unicode code points after NFC, with no whitespace/control, < > ( ) , ; : \\ " [ ], leading/trailing dot or doubled dot; domain serialized with Node domainToASCII(), lowercased, 2 or more labels of 1–63 ASCII letters/digits/hyphens each, no leading/trailing hyphen, total domain at most 253 bytes. Empty/invalid conversion or total displayed/key length over 254 is 400; unsupported quoted-address forms get an explicit validation error. Store the displayed email separately. This is an explicit contact-match policy, not a deliverability check or proof that two addresses belong to one person. Case-different local parts and separate aliases stay separate pending human reconciliation. The exact email_key is NFC(local) + '@' + lower(domainToASCII(domain)); unique (workspace_id,email_key) identifies one non-erased contact. A repeated, distinct inquiry with that key becomes its own submission. It links to the contact's one open lead, or creates a new unassigned new lead if earlier leads are closed. It does not overwrite the contact's name/email, reopen a closed lead, erase earlier questions or infer consent. An owner resolves a suspected collision or alias with the person and source records; the app does not auto-merge contacts.

For transport idempotency, compute SHA-256 of UTF-8 JSON with keys in this order and normalized strings: {formId,formStateVersion,name,email,question}. Validate request shape and resolve the stable form slug to its IDs without treating current active/version/quota as admission yet. In a READ COMMITTED transaction, read the unique (workspace_id,form_id,request_key) before mutable admission checks. An existing key with the same normalized binding returns the same generic {status:"received"} with HTTP 202, without contact/lead/submission IDs; a different binding is 409. This replay decision survives later form edits, quota exhaustion or deactivation. For an apparently new key, lock crm_workspace FOR UPDATE, repeat the exact key/binding read immediately after this wait, lock crm_form FOR UPDATE, and repeat it again immediately after that wait. At either post-wait read, return existing 202 or changed-binding 409 before checking current workspace/form state, version, cap, contact or lead. Only a still-new key proceeds to active workspace/form and exact formStateVersion checks. Then capture one clock_timestamp() from the database after those locks and reads; derive the UTC-hour bucket as to_timestamp(floor(extract(epoch from admitted_at) / 3600) * 3600) where admitted_at is that captured value, and use this same bucket for the cap check and increment of crm_form_window. transaction_timestamp() is unsuitable because it remains at transaction start during a lock wait. Count only committed new submissions and roll back the bucket with any failed intake. A stale form/notice is 409 refresh_form with no storage; missing/inactive form is generic 404; at the configured per-form cap return 429 with no storage. This global bucket limits accepted new submissions per form/hour but is not spam prevention and can reject legitimate traffic. The owner reviews actual traffic before changing the cap. Under the held locks, find or insert the exact contact, find or create its open lead, insert one submission with the form-title/notice-version/notice-text snapshot and one direct-prospect-content-free intake event, then increment that captured bucket and commit. The event remains restricted and potentially linkable through its IDs. Snapshotting the notice preserves what the visitor actually saw after later form edits. A unique collision or deadlock rolls back the failed transaction before a bounded full retry and exact-key re-read; on that retry repeat the post-wait key reads before any mutable denial. Never create two submissions or charge a second quota unit for one key. A lost response remains unknown until the same-key readback or an authorized staff inspection, not an invitation to send a second key.

For the binding and stored fields, normalize name and question to NFC after trimming surrounding Unicode whitespace, while preserving interior whitespace and line breaks. The binding's email value is the canonical email_key defined above, not the raw display spelling; a domain-case-only change is the same normalized attempt. Keep the visitor's trimmed submitted email spelling in submitted_email. Code-point limits apply after normalization, and the independent 16 KiB serialized-byte cap still applies. All branch responses retain unsaved browser input.

Staff API and state changes

All staff reads are filtered by current SQL membership and role on every request. Owner sees all leads and unassigned queue; agent sees only currently assigned leads, their linked contact, submissions, notes and event history. Owner-only member/form/contact administration is separate. Bounded lists use keyset cursors tied to the same workspace/owner filters; no table-wide debug route or arbitrary export. Current privacy notice and submission snapshots are viewable only by authorized staff. HTML uses the same server gate as JSON; a hidden client control is not authorization.

For every staff write use READ COMMITTED and locks in this order: workspace, affected member rows in Clerk-ID order, then form/contact/lead rows by ID as applicable. Public intake takes workspace then form then contact then lead; the shared workspace lock serializes intake, form changes, staff edits, role changes and field redaction. Recheck all active-state, actor role, target role, lead owner, expected version and object-workspace bindings after any wait and before mutation. The public path has the additional exact-key/binding rechecks immediately after its workspace and form waits, before any mutable admission denial. An agent revoked or reassigned before its waiting write takes the workspace lock cannot commit; an already-lock-holding write may commit first. Version mismatch is 409 with unsaved UI text preserved. Never trust a stale UI owner badge. On a unique conflict roll back before re-reading authorization and database state. Log only fixed event enum/IDs/timestamps, not prospect content. crm_event and crm_admin_event are append-only by trigger; redaction changes separate direct-content fields without rewriting these restricted, potentially personal metadata events.

Check in the reader's environment

Use synthetic prospects and two workspaces. On the public host, anonymous visitors can open only the document-free auth shell, the installed provider callback/authentication transport and required static bootstrap, and the active form; they can submit a valid synthetic inquiry but cannot open /app, staff APIs, SPA fallbacks or a contact URL. An authenticated Clerk identity with no SQL membership still sees no leads. Absent/foreign/inactive/revoked workspace requests return the same generic 404; an active agent's owner route is 403; an unassigned lead is 404 to that agent. Direct URL requests matter as much as UI visibility. Verify exact proxy ordering, callback/bootstrap and login/signup on the actual published host, with provider security retained and no app SQL membership or custom staff CSRF barrier on auth transport. Do not infer these paths from a local preview.

Submit one key twice after a lost-response simulation: one submission/event and one contact/lead only. Change the payload under the same key: 409. Hold a second same-key request behind the workspace or form lock while the first commits; before the waiter resumes, change cap to exhausted, form version or active state in separate synthetic cases. The waiter must repeat the binding lookup before each mutable check, return existing 202 for identical binding or 409 for changed binding, and create no second row or quota charge. For a genuinely new key waiting across a UTC-hour boundary, check that one post-lock database clock_timestamp() selects the new hour for both quota admission and increment. Send two different keys with the same normalized email: two submissions on one open lead; case-different local parts and plus variants remain distinct; closing the lead then receiving a new key creates a new unassigned lead without altering the closed history. Race two distinct keys for one email and check one contact/open lead with two inquiries; race two staff updates at one expected lead version and observe one commit/one 409. Have the owner assign then reassign a lead and verify the old agent loses direct access. Commit deactivation before a waiting edit acquires the workspace lock and verify it cannot commit. Check stage/assignment event order, note readback, close status, form notice snapshot after edit, stale form 409, inactive/missing form 404, form cap 429 and oversized JSON 413. Review all records after reload/restart. Staff must not be able to send or infer marketing permission from a contact or submission. Verify owner-approved transactional redaction of the named direct-content fields, restricted retained IDs/digests/notice/history and a separately governed backup restore/expiry policy; do not call the result anonymous or legally erased. Verify Production Clerk users, database, first-owner seed, host mode, public form and protected staff routes before real data. Record actual outcomes or HOLD; BIG CHANGE has not run these checks.

Sources & further reading

The official manuals document the components; the app contract and acceptance cases above are original design instructions. See the linked Replit, Clerk, PostgreSQL and Node documentation in the brief and contract. Check installed versions and account behavior before use. BIG CHANGE did not install packages, run a database, generate an app or measure a bill, conversion, delivery, spam resistance or security outcome.