Multi-level Account and User Design: One User, One Account

Most billing bugs are not arithmetic. They are identity: whose balance pays for this request. Get the identity model wrong and every table downstream grows a second nullable column and an if.

This is the account and user model behind a three-level system — legal entity → account → user — and specifically why we ended up with a rule that sounds arbitrary until you see the failure it prevents: every user belongs to exactly one account, and that membership can never change.

The interesting part is that we rejected this exact design two months before adopting it. What changed was not the schema. It was a product rule.

Conclusions first

• Three levels: legal entity → account → user. Wallets hang off the account level only.
• A lone individual gets a personal account, not a null one. “Personal” means no company, not no account.
• account_id and account_type are immutable. That single rule is what makes the whole design safe.
• Almost every invariant is declarative — a composite foreign key, a partial unique index, a check constraint. Only immutability needs a trigger, because SQL has no way to say “this column never updates”.
• A column derived from another table should be filled by the database, not by every insert site.
• Expand/contract does not protect you when a migration moves data rather than schema.

The three levels

Two things hang off this spine:

• billing.wallets — owned by an account
• authentication.api_credentials — carries both account_id (who pays) and user_id (who acted)

That split matters and is easy to blur: attribution is who made the request, settlement is whose balance is debited. A company with 20 engineers has 20 attributions and 1 settlement. Keeping them on separate columns is what lets per-person usage reporting coexist with a single shared balance.

Attempt 1: let the account be null

The first version had account_users.account_id NOT NULL — everyone is in an account. That breaks the moment one person wants to use the platform without a company behind them.

The fix looked obvious: make account_id nullable. A user with no account is “standalone” and owns their wallet directly. The wallet table already modelled both shapes:

1
2

CONSTRAINT wallets_exactly_one_owner_check
  CHECK ((account_id IS NOT NULL)::int + (user_id IS NOT NULL)::int = 1)

Exactly one owner — account XOR user. Clean, symmetric, and it shipped.

The cost: everything hangs twice

The XOR is elegant in the wallet table and expensive everywhere else. Any table that needs to answer “who pays for this?” now carries two nullable columns and a branch. In our credentials table:

Ten rows out of 302 were the entire reason for the dual shape. Every join, every query, every code path that resolves a payer had to handle a case that applied to 3% of the data — and the handling was easy to get subtly wrong, because the “wrong” branch usually returns nothing rather than erroring.

Attempt 2: a solo account per user — rejected

The obvious alternative is to give each standalone person their own one-member account, so everything hangs off accounts uniformly. When we first considered it, we wrote down why it fails:

Giving each standalone user a solo account instead looks equivalent, but breaks the moment they accept an invite: with one membership row per user, joining another account can only overwrite account_id, leaving the solo account with zero users while it still owns the wallet holding their balance. billing.wallets.account_id references customer_accounts ON DELETE RESTRICT, so that account cannot even be deleted — the money is stranded and unreachable.

Trace it concretely:

1. Alice has a solo account A. Her wallet holds $200 and belongs to A.
2. Alice accepts an invite to company account B.
3. There is one membership row per user (PRIMARY KEY (user_id)), so joining B overwrites account_id.
4. Account A now has zero members — nobody can reach it — but still owns the wallet with Alice’s $200.
5. ON DELETE RESTRICT means you cannot even delete A to clean up.

$200 in a bucket with no handle. That is a real defect, and it is why the null-account design won at the time.

What actually changed: a product rule

Two months later we adopted the rejected design. The schema argument had not improved — the premise changed.

Look again at the failure. Every step depends on step 2: Alice accepts an invite. The whole scenario requires a personal → organizational transition to exist.

So we removed it. The product rule became:

A personal user can never join an organization. Organizational users are only ever created as organizational — through self-signup or by an org admin.

With no transition, there is no reparenting; with no reparenting, there is no orphaned account; with no orphaned account, there is no stranded wallet. The objection is not mitigated, it is removed. Steps 2 through 5 cannot be expressed.

This is the part worth generalizing: a schema objection is only as strong as the operations you allow. We spent a while looking for a cleverer schema when the answer was to delete a capability we had never actually shipped.

The cost is real and should be stated plainly: a person cannot hold a personal workspace and a company membership at once. With one email meaning one user, they would need a second address. For a B2B API platform that is acceptable. For a consumer product it would not be.

Encoding it: what the database enforces

Rules that live only in application code are suggestions. Almost all of these are declarative:

1
2
3
4
5
6
7
8
9
10
11
12
13

-- Two shapes, and only two
account_type text NOT NULL CHECK (account_type IN ('personal','organizational'))

-- The membership row caches its account's type; the composite FK stops it drifting
UNIQUE (account_id, account_type)                    -- on customer_accounts
FOREIGN KEY (account_id, account_type)               -- on account_users
  REFERENCES customer_accounts (account_id, account_type)

-- A personal account holds exactly one member — nobody can be invited in
CREATE UNIQUE INDEX ON account_users (account_id) WHERE account_type = 'personal';

-- That member is always its admin
CHECK (account_type <> 'personal' OR role = 'admin')

That third one is a partial index — a unique constraint that applies to a subset of rows. It is the cleanest way to say “this rule holds for personal accounts only”.

Two details worth stealing:

The composite foreign key. Denormalizing account_type onto the membership row is what makes the partial unique index possible — an index can only see one table. Normally denormalization means drift. Here the composite FK makes drift unrepresentable: the pair (account_id, account_type) must exist in the parent, so a membership row cannot claim a type its account does not have.

“One personal account per user” needs no constraint at all. It falls out of PRIMARY KEY (user_id) — one membership row per user means one account per user, full stop. The best constraint is the one you do not have to write.

The one thing Postgres cannot say

Immutability. There is no ALTER COLUMN … SET IMMUTABLE, and immutability is the load-bearing rule of this design. So, two BEFORE UPDATE triggers:

1
2
3

CREATE TRIGGER account_users_membership_immutable
  BEFORE UPDATE ON customer.account_users
  FOR EACH ROW EXECUTE FUNCTION customer.reject_membership_reparent();

Verified against a copy of production:

1
2

UPDATE customer.account_users SET account_id = <org> WHERE account_type = 'personal';
-- ERROR: account_users.account_id is immutable (user 02aba239-…)

That error message is the design. The failure mode the earlier decision feared can no longer be typed into a SQL prompt.

A derived column belongs to the database

account_users.account_type is a cache of the account’s type. The first version required every INSERT to supply it — and immediately broke nine test fixtures and every future insert site, each one a fresh chance to write the wrong value.

A plain DEFAULT cannot help: it would have to read another table. So the database fills it:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE FUNCTION customer.fill_account_user_account_type() RETURNS trigger AS $$
BEGIN
    IF NEW.account_type IS NULL THEN
        SELECT a.account_type INTO NEW.account_type
          FROM customer.customer_accounts a
         WHERE a.account_id = NEW.account_id;
    END IF;
    RETURN NEW;
END $$ LANGUAGE plpgsql;

Callers may omit it and get the right value; pass a wrong value and the composite FK still rejects it. The rule of thumb: if a column is derived, the database should derive it. Pushing that into callers converts one invariant into N opportunities to violate it.

What the tests caught that review did not

The migration was reviewed carefully. Then we ran the integration suite against a branched copy of production, and it found four things reading the code had not.

1. Expand/contract does not protect a data move. The plan was textbook parallel change: one migration adds, a later one drops, code ships in between. Keep wallets.user_id so existing readers keep working — except the migration moves the data. The column survived; every row’s value became NULL. Every WHERE user_id = $1 matched nothing.

And it failed silently — returning “no wallet” rather than raising. Expand/contract protects you when schema changes ahead of code. It does nothing when the data moves out from under a query that still parses.

2. A unique constraint two tables away. Personal accounts were named after the person, mirrored into a legacy customers.name column carrying UNIQUE. The second “John Smith” to sign up gets a 409. Now named after the email, which is unique by construction.

3. A missing column in a SELECT. The new “is this a personal user?” check read a field the query did not select. It was undefined, the branch never fired, and the fallback path looked plausible. Type-checking cannot catch this: the row type says the field exists, and the SQL string is opaque.

4. A partial unique index on lower(contact_email). Mirroring the person’s email into the legacy customer table collides when they are already a company’s billing contact.

The through-line is that all four fail quietly. None throws. I ran into the same shape from a different direction in Little’s Law and vLLM autoscaling, where the load signal was not wrong so much as quietly measuring the wrong thing — a system at 100% capacity reported less load, and the autoscaler dutifully scaled down. A wrong-but-parseable query returns an empty set, and an empty set looks exactly like “no data” — which is why they survived review and died in five minutes against real data.

Takeaways

• Separate attribution from settlement. Who acted and whose balance pays are different questions; give them different columns.
• A schema objection is only as strong as the operations you permit. Before engineering around a failure mode, check whether you can delete the capability that causes it.
• Prefer constraints that fall out of existing keys. “One personal account per user” cost nothing because a primary key already said it.
• Denormalize with a composite FK. It converts drift from something you monitor into something you cannot represent.
• Derived columns belong to the database. N insert sites is N chances to be wrong.
• Silent-failure bugs need real data. All four defects returned an empty result rather than an error, and none was visible in review.

The migration created 10 personal accounts, moved 10 wallets to account ownership, and left 140 users with exactly one account each. If you are designing an account and user model of your own, the rule worth copying is not the schema — it is asking which operations you can refuse to support. The most valuable line in it is the one that makes something impossible.

• ← Previous Post
• Next Post →