Janady13/missionmatch / db/migrations/0046_org_branding.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0046_org_branding.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0046_org_branding.sql
Original SHA-256	d167fe6ad2b3cb07854670c1cebf3b30bde909e665e6c2620ec3985b9f86f7b1
Git blob SHA	483694b8106c07571fc515d4cdd68252ac32f6fa
Reading-copy SHA-256	d167fe6ad2b3cb07854670c1cebf3b30bde909e665e6c2620ec3985b9f86f7b1

Original source ↗

Every recorded occurrence

These 2 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0046_org_branding.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0046_org_branding.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓72 lines · 3.5 KB

-- 0046 org branding. Per organization white labeling so the application carries each client's mark.
--
-- missionMATCH is sold to more than one client. FG Talent Partners is the first, not the only one, so
-- branding is tenant data on the organizations row rather than anything hard coded.
--
-- The logo bytes live in Postgres rather than object storage on purpose. A logo is tens of kilobytes and
-- there is exactly one per tenant, so a column inherits the existing RLS isolation for free and needs no
-- new infrastructure. Wiring object storage into the API would mean building the AWS adapter that does
-- not exist yet, for a few kilobytes per client. internal/platform/storage remains available if this ever
-- outgrows a column.
--
-- Every constraint here is defense in depth. The application validates the same rules in Go before it
-- ever reaches the database, decoding the bytes to prove the declared format is real. These CHECKs make a
-- bad row impossible even if that code is bypassed or a future caller forgets.

-- +goose Up
-- +goose StatementBegin

ALTER TABLE organizations
    ADD COLUMN display_name     text,
    ADD COLUMN logo             bytea,
    ADD COLUMN logo_mime        text,
    ADD COLUMN logo_updated_at  timestamptz;

-- SVG is deliberately absent. An uploaded SVG served from the application origin is stored cross site
-- scripting: it can carry a script element that executes for every user of that organization. Only raster
-- formats the Go standard library can decode are permitted, so the server can prove a file is really an
-- image before storing it.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_mime_chk
    CHECK (logo_mime IS NULL OR logo_mime IN ('image/png', 'image/jpeg'));

-- The bytes and the type travel together or not at all. A logo with no type could not be served safely,
-- and a type with no logo would make has_logo lie.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_pair_chk
    CHECK ((logo IS NULL AND logo_mime IS NULL) OR (logo IS NOT NULL AND logo_mime IS NOT NULL));

-- 512 KiB. Far beyond any real logo and small enough that a tenant cannot bloat the row or the backups.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_logo_size_chk
    CHECK (logo IS NULL OR octet_length(logo) <= 524288);

-- A blank display name would render an empty brand slot. NULL means not set and falls back to name.
ALTER TABLE organizations
    ADD CONSTRAINT organizations_display_name_chk
    CHECK (display_name IS NULL OR length(btrim(display_name)) BETWEEN 1 AND 120);

-- Widen the column grant to exactly the branding columns and nothing else. status and slug stay
-- unwritable by the application role, so a tenant still cannot un suspend itself or seize another slug.
GRANT UPDATE (display_name, logo, logo_mime, logo_updated_at) ON organizations TO missionmatch_app;

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

REVOKE UPDATE (display_name, logo, logo_mime, logo_updated_at) ON organizations FROM missionmatch_app;

ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_display_name_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_size_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_pair_chk;
ALTER TABLE organizations DROP CONSTRAINT IF EXISTS organizations_logo_mime_chk;

ALTER TABLE organizations
    DROP COLUMN IF EXISTS logo_updated_at,
    DROP COLUMN IF EXISTS logo_mime,
    DROP COLUMN IF EXISTS logo,
    DROP COLUMN IF EXISTS display_name;

-- +goose StatementEnd