Supplier Account Inventory

An internal tool for submitting, reviewing, assigning, and controlling access to supplier account credentials ("IDs"). There are three kinds of user, each with their own sign-in and their own screens:

Role	How the login is created	What they can do
Supplier	Signs up himself at /signup	Submit IDs; see his own dashboard — how many IDs he gave, and which were accepted or rejected (with the reason), day by day, and which IDs he has been paid for (with the date — never the amount). Only accepted IDs are paid. Never sees a stored password again, and never sees another supplier's IDs.
Media buyer	Added by an admin on Suppliers & buyers	See only the IDs assigned to him, with their login details (passwords, Outlook mail, temp mail, 2FA key) shown in plain text so he can check them, change a password / 2FA key if it changes, and mark each one active or rejected (a note saying why is required). The New today count and filter show which IDs he was given today.
Admin	Promoted by hand (see below)	See everything, credentials shown in plain text; change any ID's passwords / 2FA key; accept or reject IDs; assign IDs to a media buyer by name, one at a time or in bulk; add media buyers; see on which date each supplier gave how many IDs, pay for that day's accepted IDs (Mark paid with the amount), and see each month's IDs bought / pending / accepted / rejected / paid for and the total spent (dashboard and Supplier payments); export.

Life of an ID: a supplier submits it (pending) → an admin accepts it, or assigns it to a media buyer, which also accepts it (accepted) → the media buyer checks it and marks it active or rejected. An admin can also reject, archive or reassign at any point. Credentials are shown in plain text on an ID's page, to an admin or the media buyer it is assigned to only; every time that page is opened it is freshly authorized and audit-logged.

Stack: Next.js 16 (App Router) + TypeScript + Tailwind CSS v4, Supabase (Postgres + Auth). Credentials are stored as plain text — no encryption key is needed (see Credential storage).

Table of contents

• Quick start (demo mode)
• Setting up a real Supabase project
• Credential storage (plain text)
• Creating the first admin
• Role management
• Deployment
• Security model summary
• Data retention
• What works today vs. what needs configuration

Quick start (demo mode)

With no configuration at all, the app runs in demo mode: an in-memory, fictional data set with a demo admin, two demo media buyers and two demo suppliers. Nothing is persisted — it resets every time the server restarts — and this is loudly banner-labeled in the UI. The view/audit flow behaves like production, just without durability.

npm install
npm run dev

On Windows you can instead double-click start-app.bat, which runs those two commands and opens the browser.

Open http://localhost:3000 — you'll land on the sign-in page. In demo mode, pick any of the listed demo users to see that role's screens (a simulated sign-in, clearly marked as such — see src/app/actions/auth.ts), or use Create an account to try the supplier sign-up.

Setting up a real Supabase project

1. Create a project at supabase.com.
2. In the SQL editor, run the migrations in supabase/migrations/ in filename order:
   • 0001_schema.sql — tables, triggers, the profiles role sync trigger.
   • 0002_rls.sql — Row Level Security policies. This is the real authorization boundary; the app's server checks are a second layer, not a substitute.
   • 0003_secret_presence.sql — a narrow, presence-only RPC so the UI can show "a value is stored" without ever exposing ciphertext through it.
   • 0004_two_factor_secret.sql — allows storing an encrypted 2FA key.
   • 0005_roles_assignment.sql — supplier / media buyer / admin roles, which supplier an ID belongs to, which media buyer it is assigned to, the accepted / rejected statuses and the rejection note. If your database already exists, this is the one you still need to run. It is safe to run more than once; it renames the old team role to supplier and moves any needs_review IDs back to pending.
   • 0006_buyer_source.sql — lets a media buyer add IDs of his own.
   • 0007_supplier_payments.sql — the Supplier payments page: which supplier was paid for which day. Admin-only (RLS). Safe to run more than once.
   • 0008_paid_per_id.sql — only accepted IDs are paid; each paid ID gets its own paid_at. Run it before deploying the version that uses it. Safe to run more than once.
3. Optionally run supabase/seed.sql for fictional sample suppliers/accounts (no secrets — attach fictional secret values afterward through the app UI).
4. Copy .env.example to .env.local and fill in:
   • NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY — Project Settings → API.
   • SUPABASE_SERVICE_ROLE_KEY — same page. Server-only. Never expose this to the browser, never prefix with NEXT_PUBLIC_, never commit it.
   • ENCRYPTION_KEYS — optional, only to read credentials saved before they were stored as plain text; see the next section.
5. npm run dev. The app detects the Supabase env vars and leaves demo mode automatically.

New sign-ups are always the supplier role (enforced by the handle_new_user trigger and by RLS — a user can never change their own role). Suppliers sign up at /signup; the login is created by the server with the email already confirmed, so sign-up does not depend on the Supabase project's email settings, and there is no email verification — anyone who knows the address of the site can create a supplier account. Media buyers are added by an admin inside the app. For the first admin, see Creating the first admin.

Credential storage (plain text)

Passwords, email passwords and 2FA keys are stored in account_secrets as plain text (the value is in the ciphertext column, key_version = -1). No encryption key is needed to save or read them. Access still goes only through the server: RLS denies all direct client access to account_secrets, and every view or export is authorized and audit-logged. But anyone who can read that table — a database backup, the Supabase dashboard, or whoever holds SUPABASE_SERVICE_ROLE_KEY — can read every credential, so protect those accordingly.

Older, encrypted rows. Credentials saved before this change are AES-256-GCM encrypted (key_version >= 1). They can still be read as long as their key stays in ENCRYPTION_KEYS (e.g. ENCRYPTION_KEYS={"1":"<key>"}); without it they show as "could not decrypt". Changing such a value in the app re-saves it as plain text. npm run check-key tells you whether a key opens them.

Converting them all at once. Deploy this version first (the older one cannot read plain-text rows). Then, with NEXT_PUBLIC_SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY and ENCRYPTION_KEYS in .env.local (or the environment):

npm run decrypt-secrets

This is a dry run: it checks that every encrypted row can be read and lists any that can't, without changing anything. Then convert them:

npm run decrypt-secrets -- --apply

It never prints a credential value, skips rows that are already plain text, leaves any row it can't read as it is, and writes one secret.bulk_convert_plaintext audit entry. Converted rows get a new updated_at. It is safe to run again. When it reports Still encrypted: 0, remove ENCRYPTION_KEYS and ENCRYPTION_KEY_VERSION from every environment and redeploy. Keep a private copy of the key for as long as you might restore a database backup taken before the conversion.

Creating the first admin

There's no bootstrap UI for this on purpose (an admin-creation button reachable by anyone would defeat the point). After a user has signed in at least once (so a profiles row exists via the handle_new_user trigger), promote them from the Supabase SQL editor:

update public.profiles set role = 'admin' where email = '[email protected]';

Role management

• profiles.role is the single source of truth for authorization, checked fresh on every server action, route handler, and RLS policy — never cached from a JWT claim.
• A user can never change their own role from the client (profiles_update_self_limited RLS policy enforces this at the database level, independent of the app code).
• Media buyers are added in the app: Admin → Suppliers & buyers → Add a media buyer (name, email, password). Give them that email and password to sign in.
• Suppliers create their own login at /signup.
• Admins are never created from the app. To promote/demote, either use the SQL above or have an existing admin run the equivalent update through the Supabase dashboard's table editor.
• Taking a media buyer's access away: change their role back to supplier with the same kind of SQL update (they immediately lose the media-buyer screens and can no longer see any credential), and ban the user in Supabase → Authentication if they should not be able to sign in at all. IDs that were assigned to them show "Former media buyer" in the admin list until you reassign them.

Deployment

1. Set all of .env.example's variables in your host's environment configuration (e.g. Vercel → Project → Settings → Environment Variables). Never commit them.
2. Make sure SUPABASE_SERVICE_ROLE_KEY and ENCRYPTION_KEYS are marked server-only / "sensitive" if your host distinguishes that; they must never end up in a client bundle.
3. Run the Supabase migrations against your production project (same steps as above).
4. npm run build && npm run start, or deploy via your platform's normal Next.js flow.
5. Promote your first admin (see above) before inviting the team.

Security model summary

• RLS is the enforced boundary, not the UI. Every table has Row Level Security enabled; hiding a button never substitutes for a policy. See supabase/migrations/0002_rls.sql.
• account_secrets denies all direct client access. The only way to reach it is the server's service-role client inside src/lib/data/secrets.ts, which re-checks the caller's authentication and role from scratch on every single call — view or write. An admin can see any ID's credentials; a media buyer only those of an ID assigned to him at that moment (unassigning cuts him off immediately); a supplier never.
• A supplier never sees who handled his ID. His dashboard shows only pending / accepted / rejected and the rejection reason. Which media buyer an ID is assigned to, who accepted or rejected it, and even whether it has been assigned at all are never read for, or sent to, a supplier, and the pages he sees do not mention media buyers. (The reason text is passed on exactly as written — whoever rejects should not put their own name in it.)
• Suppliers and media buyers have no RLS policy on accounts at all. Their screens go through server code (src/lib/data/accounts.ts) that checks the role and then filters by supplier_id / assigned_to itself, using the id from the session — never one from the URL or a form. Directly against the database, with their own login, they can read nothing but their own profile row.
• Credentials are shown in plain text, with no "Reveal" step. An ID's page (admin or the assigned media buyer) decrypts its passwords and 2FA key on the server and shows them with a Copy button — see getSecretsForViewer in src/lib/data/secrets.ts and src/components/CredentialsList.tsx. Anyone looking at that screen can read them.
• Every view is audit-logged (secret.view) — actor, record, which fields were shown, outcome — with the secret values themselves never included.
• Changing a password / Outlook password / 2FA key from an ID's page (the Change button) is allowed for an admin on any ID and for a media buyer only on an ID assigned to him at that moment — checked again on the server in changeSecret (src/lib/data/secrets.ts). The new value is stored as plain text like any other, and every attempt is audit-logged (secret.update) without the value.
• The Excel export contains every credential in plain text. The admin-only "Export Excel (with credentials)" button (src/app/api/export/accounts/route.ts) decrypts every password and 2FA key into the file, one sheet per day. Every export is audit-logged and the filename contains — treat the downloaded file as being as sensitive as the whole database.

Data retention

This template does not implement automatic retention/deletion — decide policy for your organization and enforce it operationally (e.g. a scheduled job) or ask for scoped follow-up work:

• Audit log: grows indefinitely by default. Recommend deciding a retention window (e.g. 1–2 years) and deleting older rows on a schedule; audit rows never contain secret values, so retention risk here is about actor/record metadata, not credentials.
• Archived accounts: once a supplier relationship ends, archive the account (keeps the audit trail) and consider a separate hard-delete pass after your organization's retention window, which should also delete the matching account_secrets rows.
• Fields collected are intentionally minimal — only what's listed in the "Account fields" spec. Don't add fields (e.g. broader PII) without a specific need.

What works today vs. what needs configuration

Works out of the box (demo mode, no setup):

• Sign-in for all three roles, each sent to their own area; wrong-role access is refused.
• Supplier: sign-up, submit IDs, dashboard with totals, a day-by-day table and the reason for each rejection.
• Media buyer: list of assigned IDs with a New today count and filter, credentials and temp mail shown in plain text with Copy buttons, mark active or reject with a note.
• Admin: search/filter IDs, accept/reject, assign to a media buyer per row or in bulk, add media buyers, per-supplier and per-buyer totals, per-record activity trail, and Supplier payments: IDs given per supplier per date, marked paid / not paid.
• Excel export (admin only, includes decrypted credentials).
• Light and dark theme: follows the device setting until someone uses the toggle (top bar, sign-in and sign-up pages); the choice is remembered in that browser.
• Phone layout: below desktop width the wide tables become cards, and the admin's accept / reject / assign bar stays in view while scrolling.

Works once you configure Supabase (see setup above):

• Durable Postgres storage, real Supabase Auth sessions, RLS-enforced authorization.

Needs your decision / follow-up work, not included by default:

• Protection on the public sign-up form. Anyone can create a supplier account and there is no email verification, captcha or rate limit. A supplier account can only submit IDs and see its own, but junk sign-ups and junk submissions are possible — worth adding an approval step or a captcha before sharing the link widely.
• Removing or disabling a user from inside the app, resetting a password, and promoting an admin (all currently done in the Supabase dashboard — see Role management).
• Automated retention/deletion jobs (see Data retention).
• Anomaly detection on credential views (every view is logged, but nothing currently alerts on a burst of them — worth adding if this scales beyond a small trusted team).

Project structure

src/
  app/
    actions/         Server Actions (auth, accounts, secrets, users) — the only write paths
    api/export/       Admin-only Excel export (includes decrypted credentials)
    admin/           Admin pages (dashboard, all IDs, suppliers & buyers)
    buyer/           Media buyer pages (my IDs, check one ID)
    supplier/        Supplier pages (my dashboard, submit an ID)
    signup/          Supplier sign-up
    login/           Sign-in for every role (real or simulated demo sign-in)
    team/            Old links only — redirects to the supplier area
  components/        Shared UI (CredentialsList, AccountForm, AccountTable, AssignSelect, RoleShell, ...)
  lib/
    crypto.ts        credential storage format (plain text; reads old AES rows) — server-only
    auth.ts          getCurrentUser/requireUser/requireRole/requireAdmin — fresh check every call
    data/            Data-access layer (accounts, profiles, secrets, audit)
    demo/            In-memory demo-mode store (never used when Supabase is configured)
    supabase/        RLS-bound client (server.ts) and service-role client (admin.ts)
supabase/
  migrations/        Schema + RLS policies, run in order
  seed.sql           Fictional sample data (no secrets)
scripts/generate-key.mjs   Prints a new base64 AES-256 key
scripts/decrypt-secrets.mjs  One-time: converts old encrypted credentials to plain text