Suriyakumarvijayanayagam
3f9fb33b24
A tenant had exactly the users somebody had created with a command on the
server. That is not a missing screen: a shop with an owner and four staff
either shared one password or raised a ticket per person, and a phone app
for the shop floor could not exist while there was one account to sign in
as.
Registration is by invitation, never open signup - the same line already
drawn around creating a company. The code carries the address and the role
and the request carries only a password, so a code that gets forwarded
cannot become somebody else's account, and a staff invitation cannot be
redeemed as an owner. Single use lives in the UPDATE and the account is
created in the same transaction.
Deactivating a member revokes their sessions in that transaction too. An
access token lives twelve hours, so without it "remove their access"
removed it sometime tomorrow. The session list and revoke that go with it
are the benefit of opaque tokens the product had been paying for and never
collecting: nothing could say what was signed in, let alone stop one.
Face images now work on a deployment with no object storage, which was
every local install and every self-hosted site - the arrivals feed said
"not storing customer photos" for every customer forever, on the screen
whose whole job is to show a face. Bounded to one row per visitor, so it
grows with the customer base and not with footfall; the bucket stays
primary wherever one exists.
Image.auth says whether a URL needs the session, because a browser img
cannot load one that does, a mobile image view can, and a webview can do
neither - the desktop client resolves those to a data URI in Go.
Found by running it, not by tests:
* UPDATE ... RETURNING gives the value AFTER the update, so the prune
read back empty keys, deleted nothing, and the table grew with
footfall exactly as if it were not there. The fake agreed with either
version; only the live Postgres test caught it.
* Trusting only the auth flag broke every shop card, because Sites.jsx
rebuilt a partial snapshot object and dropped it. A relative URL is
now sufficient on its own.
* ago() renders a future time as "just now", so a code valid for a week
read "expires just now".
Verified live against real Postgres: invite, preview, escalation refused,
register into a session, replay 404, staff forbidden, device revoked and
401 at once, last owner refused, and a 92,405-byte camera JPEG stored,
served to its owner, 401 with no session, 404 to another tenant, and
rendered in a browser.
Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HViLj9gYNRtSr7YVZmW5sn
61 lines
3.1 KiB
PL/PgSQL
61 lines
3.1 KiB
PL/PgSQL
-- Face images for a deployment that has no object storage.
|
|
--
|
|
-- 009 did this for camera snapshots and its own comment says why face images
|
|
-- are different: "Face images grow with every visitor who ever walks in, which
|
|
-- is why they stay in a bucket." That is true of face images kept PER VISIT,
|
|
-- and it is the reason this table is bounded to one row per visitor instead.
|
|
--
|
|
-- The problem it fixes is the one 009 fixed one level up. With no bucket the
|
|
-- API answers "This system is not storing customer photos" for every arrival,
|
|
-- forever - including on the mobile feed, whose entire purpose is to put a face
|
|
-- in front of somebody so they can recognise the customer walking towards them.
|
|
-- A shop that turned `app.store_faces` on and has no S3 account got nothing.
|
|
--
|
|
-- What makes this bounded, which is the only reason it is acceptable here:
|
|
--
|
|
-- * The engine still gates capture. `app.store_faces` is false by default and
|
|
-- no crop is written without it, so this table changes what happens to an
|
|
-- image that already exists - it does not change whether one is taken.
|
|
-- * ONE ROW SURVIVES PER VISITOR. `RecordVisit` prunes the previous row when
|
|
-- it links a newer one, so storage is (customers x ~20 KB) and grows with
|
|
-- the customer base, not with footfall. A shop seen by 5,000 people holds
|
|
-- about 100 MB whether they visit once or a thousand times.
|
|
-- * Nothing reads a superseded face anyway. Every surface - the arrivals
|
|
-- feed, the customer record, the mobile app - shows the customer's latest
|
|
-- view, which is what `VisitorImageKey` has always returned.
|
|
--
|
|
-- Where a bucket IS configured this table is never written: the presigned path
|
|
-- stays primary, because it never passes the bytes through the API at all,
|
|
-- which is what makes it the right route at estate scale.
|
|
--
|
|
-- Keys are prefixed `db:` in `visits.image_key` so one column can name an
|
|
-- object in either place and the read path can tell which without a second
|
|
-- lookup or a nullable column.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS visit_faces (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
-- Denormalised like every other table here: a cross-tenant read should
|
|
-- require a wrong WHERE clause rather than a forgotten join.
|
|
client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
site_id uuid NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
|
|
image bytea NOT NULL,
|
|
bytes integer NOT NULL,
|
|
captured_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS visit_faces_client_idx
|
|
ON visit_faces (client_id);
|
|
|
|
-- An agent uploads a face BEFORE the server has decided who it is, so a row can
|
|
-- exist for a few milliseconds with no visit pointing at it - and permanently,
|
|
-- if the visit that would have claimed it never arrives because the queue was
|
|
-- dropped. That is a leak of exactly one image per lost visit, so it is swept
|
|
-- rather than left: anything older than a day with no visit referencing it is
|
|
-- an orphan, and this index is what makes finding them cheap.
|
|
CREATE INDEX IF NOT EXISTS visit_faces_age_idx
|
|
ON visit_faces (captured_at);
|
|
|
|
COMMIT;
|