PivotAdmin

Overview

The pivot-internal repo includes PivotAdmin, an internal tool for managing the Pivot Cloud Platform environment.

Features include:

• View and handle moderation reports
• View list of users and their usage, ban/unban users
• View list of organizations and their usage, with Stripe details, and modify certain organization properties
  • Add/remove custom domains
  • Change subscription tier
  • Add/remove manual billing toggle
  • Add organizations
• Manage subscription tiers and subscribable features.
• View logs of all actions taken by users of PivotAdmin

PivotAdmin is a Next.js app deployed to Cloudflare Workers behind Cloudflare Access. Data fetching is primarily server-side as the app is mostly lists of views with discrete button and form actions. The Supabase Auth service is used to authenticate with the Pivot JumpCloud user directory via SAML. It stores data in a Postgres database managed by Supabase, using the Supabase JS library, not an ORM or SQL client.

PivotAdmin runs 'publicly' at admin.hellopivot.com. Cloudflare Access serves as a 'second line of defense' so that we aren't actually relying on Supabase Auth or our Next.js auth middleware to be perfect to avoid breaches. And visa-versa, if there is a bug in the Cloudflare Access stack, we are still secure behind Supabase's Auth service.

Of course, none of those auth methods prevent a specific user's Pivot Technologies directory account from being compromised, which is where MFA via JumpCloud comes in.

Auth and Logging via Supabase Postgres

The PivotAdmin Postgres database has two three tables:

• logs, to log all actions taken in PivotAdmin
• hosts, to store the URL of all Tunnel instances for all Pivot deployments
• users, which stores a PivotAdmin user's email address and role (SUPPORT, SUPER_ADMIN, ADMIN, SALES, or VIEW_ONLY).

The Next.js server-side code checks the the user's role when performing any operation. All routes require a role and will error unless the user has an allowed role. Every data fetch operation also adds a log row with the user's id to Postgres. All users then have read access to all log rows within the PivotAdmin UI, for transparency.

Table Schema

users

• id - Unique ID
• name
• email
• status
• role
• created_at
• updated_at

logs

• id - Unique ID
• user_id - ID of associated user
• timestamp
• type - Log type (CRUD or ERROR)
• value - Json info about the affected data (e.g. organizationId and organizationName, for readability)

hosts

• id - Unique ID
• hostname
• friendly_name

Queries and Mutations

The Next.js app's backend (using Cloudflare Workers) GETs and POSTs from tnl.pivot.app/admin/v1/** routes (and accesses the Tunnel endpoint for each private deployment in the same way via the hosts table).

The /admin route handlers in Tunnel query and mutate data via downstream services, serving as the authentication layer for PivotAdmin's queries, by verifying that the request signature calculated by hashing the request body and hasing it with a PivotAdmin-specific private key is valid. Tunnel does not have the private key just the public key.

Why not use Retool or something? Having custom control of roles, no specific per-user charges, and a logically simple implementation is preferable in the long run, even if the speed of getting started with a PaaS option would be useful. Next.js on Cloudflare Pages + Supabase Auth and Database is a really clean stack which adds minimal operational overhead and hosting costs. Plus, where PaaS internal tool products really excel is all the different ways you can fetch data, but we don't want to directly connect to the Pivot service databases nor do we want to poke holes in our purpose-built Friend or REST APIs, which means we would just be making the same Tunnel HTTP hooks/admin/** requests anyway.