Customers

Crate: platyn-shop · Migration: 0004_shop.sql · Status: Schema built API planned

The table exists and is applied. The HTTP surface is planned.

What a customer is

A customer is a person at a company. Companies get billed; customers get emailed.

The split falls out of how a job actually moves:

• The booster club president picks the shirts and approves the art.
• The athletic director signs the purchase order.
• The office administrator answers the phone in July.

Three people, one company, and only one of them can approve a proof. Collapsing them into one record means the approval email goes to whoever was typed in first.

A customer is not a user

app.customers is a contact record owned by a shop. app.users is someone who logs into Platyn. A customer has no password, no session, and no permissions.

Customer-facing proof approval already works this way in the schema: app.approvals carries a token_hash for a scoped, account-less link and a decided_by_customer_id. The customer proves who they are by holding the token, not by having an account. See design.

Model

erDiagram
    TENANTS   ||--o{ CUSTOMERS : owns
    COMPANIES ||--o{ CUSTOMERS : employs
    CUSTOMERS ||--o{ DEALS : "is the contact for"
    CUSTOMERS ||--o{ APPROVALS : "decides"
    CUSTOMERS ||--o{ APPROVAL_COMMENTS : "writes"

app.customers

Column	Type	Notes
id	UUID PK
tenant_id	UUID → app.tenants	RLS key. ON DELETE CASCADE.
company_id	UUID nullable → app.companies	ON DELETE SET NULL — see below.
first_name, last_name	TEXT nullable	Both nullable. A record may start as just an email.
email	CITEXT nullable	Case-insensitive at the database. Not unique — see below.
phone, title	TEXT
is_primary	BOOLEAN	Default false.
notes	TEXT
created_at, updated_at, archived_at	TIMESTAMPTZ

SELECT app.apply_tenant_rls('app.customers');

CREATE INDEX customers_by_company ON app.customers (tenant_id, company_id)
    WHERE archived_at IS NULL;
CREATE INDEX customers_by_email   ON app.customers (tenant_id, email)
    WHERE email IS NOT NULL;

Three deliberate choices

company_id is nullable, and deleting a company sets it to NULL rather than cascading. A walk-in ordering twelve shirts for a bachelor party is not a company, and forcing the CSR to invent one produces a database full of companies named after individuals. ON DELETE SET NULL means removing a company orphans its contacts rather than destroying them — the contact history and their deals survive.

email is nullable and not unique. Some contacts are phone-only. And the same address genuinely appears twice: a person who works for two client companies, or a shared orders@ inbox used by several departments.

Uniqueness that is wrong is worse than none

A UNIQUE (tenant_id, email) looks tidy and breaks the first time a CSR enters a second contact at a company that uses one shared inbox. They will work around it — orders+2@company.com — and now the data is worse than if the constraint had never existed. Duplicate detection belongs in the UI as a warning.

is_primary has no uniqueness constraint. A partial unique index on (company_id) WHERE is_primary would enforce one primary contact per company, and it is not there. Today that is the application’s job. It is a reasonable thing to add later; it is listed as an open question rather than described as a guarantee, because right now it is not one.

API surface (planned)

Method	Path	Permission
GET	/v1/customers	customer.read
GET	/v1/customers/{id}	customer.read
POST	/v1/customers	customer.write
PATCH	/v1/customers/{id}	customer.write
POST	/v1/customers/{id}/archive	customer.write
GET	/v1/customers/{id}/deals	deal.read

Listing supports q over name and email, company_id, and keyset pagination.

production deliberately holds no customer.read — a press operator does not need the customer’s phone number to run a job, and the narrower the default grant, the less a compromised shop-floor terminal is worth.

Planned

• A primary-contact constraint, if the product decides one primary per company is a rule rather than a convention:

  CREATE UNIQUE INDEX customers_one_primary ON app.customers (company_id)
      WHERE is_primary AND archived_at IS NULL;

• An interaction timeline. customers.notes is a standing description, not a log. Calls, emails, and system events (“proof approved”, “deal moved to production”) want their own append-only table — much of which app.audit_log already captures and could project from.
• Merge, with the same tombstone requirement as company merge.
• Bounce and unsubscribe handling, so a hard bounce marks the address rather than silently failing to deliver every future proof.
• Marketing consent, tracked separately from transactional email. Legally, and ethically.
• CSV import, because every shop arrives with a spreadsheet.