RedBridge Group CRM
Role: Sole architect & developer · Group-wide CRM replacing a 5,076-row manual spreadsheet · 4 business units · 2 countries · 5 of 7 stages delivered in 16 days against an 8-month plan
Business Impact
Zero per-seat licensing at any headcount, against a commercial CRM charging per user per month indefinitely — and one that would still need custom development, because more than half the client journey happens after the sale. Infrastructure runs on usage-based hosting rather than seats, so cost doesn't scale with hiring.
Retires a spreadsheet where all 5,076 client records were typed by hand, and the 101-field 四合一表 workbook the sales methodology lived in. Mandatory fields per stage, two- and 24-hour service deadlines and the evidence trail are now enforced by the database rather than by whoever remembered — and 状态 that used to be typed is derived, so a case cannot claim to be closed-won while its first payment is unverified.
Access is a property of the database, not the interface: 157 row-level security policies across 79 tables, with 33 permissions over 16 roles held as data and editable without a deploy. Territory is inherited from the org chart rather than tagged per case. Exceptions — one Finance staffer covering both countries — are recorded against the person with a written reason, not by widening everyone's access.
Four business units, two countries, two Australian states and one Chinese province live, with the structure held as configuration rather than code because the organisation is mid-restructure and will change again. Bilingual English and Chinese throughout — 1,923 translation keys at enforced parity, including database-stored values — since most staff work in Chinese.
Scale at a Glance
Project Summary
| Field | Details |
|---|---|
| Project Name | RedBridge Group CRM |
| 项目名称 | 红桥集团客户关系管理系统 |
| Period | 24 August 2026 – ongoing |
| 周期 | 2026年8月24日 至今 |
| Company | Siddeley Group / RedBridge Consulting |
| 公司 | Siddeley Group / 红桥咨询 |
| Role | Architecture, data modelling, full-stack development |
| 职能 | 系统架构、数据建模、全栈开发 |
| Scope | Group CRM — 4 business units, 2 countries, ~40 staff |
| 范围 | 集团 CRM — 4 个业务单元、2 个国家、约 40 名员工 |
| Stack | Next.js 16 · TypeScript · Supabase (Postgres 17) · Cloudflare Workers · next-intl |
| 技术栈 | Next.js 16 · TypeScript · Supabase（Postgres 17）· Cloudflare Workers · next-intl |
| Timeline | Planned ~8 months over 7 stages · 5 of 7 substantially delivered in 16 days |
| 周期规划 | 原计划约 8 个月、7 个阶段 · 16 天内完成其中 5 个阶段的主体建设 |
| Status | Sales pipeline, case management, documents and access control live |
| 当前状态 | 销售流程、商机管理、文件管控与权限体系已上线 |
Key Highlights
- Separated three things the old model treated as one — an enquiry is an event that is never destroyed, a lead is an unqualified person, a case is a commitment. A case exists only at 已留资, when a real WeChat ID has been captured, and only then is a 商机ID issued. One client on two visa pathways gets two cases, which makes the workbook's 一行一商机 rule structural rather than a convention people remember.
- Made status derived rather than typed — 当前阶段 is computed from stage, sub-stage and money; 已成交 requires a signed contract and a payment Finance has verified, so it can never mean 'signed but unpaid'; whose desk a case sits on follows from which functions are open, so nothing has to be switched at hand-off and no screen can contradict the data beneath it.
- Modelled the methodology, not a generic pipeline — the 四合一表's four stages share one anatomy: what you must know before, what happened, what you must record after, a clock, and evidence. 前端线上 fills two of those slots and 一次线下 fills all five, so it is one typed timeline rather than four screens. The 2-hour minutes deadline is measured from the meeting's end, and the person being measured holds no privilege to set that timestamp.
- Treated silent permission failures as the main risk — the recurring bug in this stack is not a logic error but a permission that fails and looks exactly like success: a table with RLS on and zero policies is indistinguishable from an empty one; an UPDATE filtered by a policy returns no error and no rows. Six such faults were found and fixed by probing production as a real authenticated user inside a rolled-back transaction. A schema-invariant suite now enforces the conventions.
- Built the guards that the mistakes earned — a check that every embedded query resolves against the live schema; enforced translation parity so a missing Chinese string fails the build rather than falling back to English; and a two-version failure convention, a plain sentence for the person and a structured line for the log, after finding 183 error paths that discarded the database's own diagnosis at the moment it became useful.
- Designed for how the staff actually work — mobile-first, because most of them are on a phone; every editable field a searchable dropdown, because a forty-name list is not a select; times rendered on the reader's own clock with the zone shown, so a Melbourne rep and a Chengdu rep read the same instant correctly. Nobody at RedBridge has a work mailbox, so onboarding asks for a username and builds the sign-in identity from it.
A group-wide CRM for RedBridge Group — four business units across Australia and China — replacing a 5,076-row manual spreadsheet and an earlier in-house portal. The sales pipeline, case management, document control and access model are live in production. Five of seven planned stages were substantially delivered in 16 days against an 8-month plan; the compression is real but so is what it bought — the stages delivered are the ones that could be built from a written methodology and a profiled dataset. Stage 6 and most of Stage 7 depend on people and legal advice, and no amount of pace moves those.
Delivery Stages
- Auth and identity: username-based sign-in, no email address required
- Core schema: 79 tables, full entity modelling — enquiry, lead, case, person, company, address, document
- RLS from the permissions table: 157 policies, 33 permissions over 16 roles held as editable data
- Bilingual wiring: 1,923 translation keys at enforced parity — missing Chinese string fails the build
- Audit in the data layer: two-version error convention; CI schema-invariant suite validates every embedded query before deploy
- RedNote intake: enquiry captured as an immutable event, never destroyed; repeat enquiries fully attributed
- Account provenance and campaign attribution tracked from first touch
- Dedupe and merge: duplicate person and company records resolved without data loss
- Geo normalisation: address and territory inheritance from the org chart, not per-case tagging
- Note: the 5,076-row migration from the live spreadsheet has not yet run — schema and tooling are ready
- Case file: 商机ID gated to 已留资 — real WeChat ID captured; one client on two pathways gets two independent cases
- War card: 当前阶段 derived from stage, sub-stage, and payment; 已成交 requires signed contract and verified first payment
- Documents: checklist gates per stage, upload verification, status derived from completion
- Client portal: self-service case view, access scoped by RLS to the client's own records
- Consultations, payments, receipts: 四合一表 timeline with 2-hour minutes deadline stamped by the database
- BU and territory dimensions: four business units and two-country structure held as configuration
- Dual ownership: cases can sit across BU boundaries without duplicating records
- Desk hand-off with least-loaded routing: case assignment follows workload, not manual allocation
- Outstanding: record ownership model still with the board — pending sign-off before final wiring
- Permissions matrix: 33 permissions over 16 roles, editable without a deploy
- Audit and error triage: structured error log with database-level diagnosis preserved at the point of failure
- Onboarding queue: new staff provisioned via username — no email address required
- Self-service profile and credentials: staff manage their own contact details and sign-in
- Bilingual throughout: all 1,923 translation keys enforced at parity, including database-stored values
- Self-hosted assets: no content served from external hosts that would be blocked in China
- Outstanding: DingTalk integration, WeChat and SMS notification channels, PIPL compliance review