7/18-The Global Rules Every New Project Should Have-Isolate by default

// DON'T: tenant comes from the path, so any caller can name another tenant
app.get('/orgs/:orgId/invoices', (c) =>
  c.json(listInvoices(c.req.param('orgId')))); // forge orgId, read anyone
// DO: tenant comes from the verified JWT, the URL cannot override it
app.get('/invoices', requireAuth, (c) => {
  const orgId = c.get('claims').org_id; // signed by the IdP, not caller-supplied
  return c.json(listInvoices(orgId));
});

// DON'T: trust a client-set header to choose the tenant
@GET public List<Invoice> list(@HeaderParam("X-Org-Id") String orgId) {
    return repo.byOrg(orgId); // attacker sets any org id
}
// DO: read the tenant from the verified security identity
@GET @Authenticated
public List<Invoice> list(@Context SecurityIdentity id) {
    String orgId = id.getAttribute("org_id"); // from the validated token claim
    return repo.byOrg(orgId);
}

// DON'T: one app-level filter; a forgotten clause anywhere leaks across tenants
const rows = await db.select().from(invoices).where(eq(invoices.orgId, orgId));
// DO: filter in code AND set the tenant in a session var that RLS enforces, inside one transaction
const rows = await db.transaction(async (tx) => {
  await tx.execute(sql`SELECT set_config('app.current_org', ${orgId}, true)`); // true => tx-local, read by the RLS policy
  return tx.select().from(invoices).where(eq(invoices.orgId, orgId));
});
// migration: CREATE POLICY tenant_isolation ON invoices
//   USING (org_id = current_setting('app.current_org')::uuid);

// DON'T: application filter only, database would happily return every tenant
return em.createQuery("select i from Invoice i where i.orgId = :o", Invoice.class)
         .setParameter("o", orgId).getResultList();
// DO: app filter plus a database RLS policy scoped by a session setting
em.createNativeQuery("SELECT set_config('app.current_org', :o, true)").setParameter("o", orgId).getSingleResult(); // tx-scoped: same @Transactional block as the read below
return Invoice.list("orgId", orgId); // RLS policy on invoices re-checks org_id
// V5__rls.sql: CREATE POLICY tenant_isolation ON invoices
//   USING (org_id = current_setting('app.current_org')::uuid);

// DON'T: missing tenant falls through and selects across all tenants
function invoicesFor(orgId?: string) {
  const q = db.select().from(invoices);
  return orgId ? q.where(eq(invoices.orgId, orgId)) : q; // undefined => everything
}
// DO: no tenant means no data, never a wildcard read
function invoicesFor(orgId: string | undefined): Promise<Invoice[]> {
  if (!orgId) return Promise.resolve([]); // fail closed, empty result
  return db.select().from(invoices).where(eq(invoices.orgId, orgId));
}

// DON'T: null tenant returns the full table
public List<Invoice> forOrg(String orgId) {
    if (orgId == null) return Invoice.listAll(); // leaks every tenant
    return Invoice.list("orgId", orgId);
}
// DO: absent tenant yields an empty list, not the estate and not an error
public List<Invoice> forOrg(String orgId) {
    if (orgId == null || orgId.isBlank()) return List.of(); // fail closed
    return Invoice.list("orgId", orgId);
}

-- DON'T: the app connects as an all-powerful role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_runtime; -- can read + drop
ALTER ROLE app_runtime WITH SUPERUSER BYPASSRLS;                    -- ignores tenant policy
-- DO: least privilege, and crucially the runtime role cannot bypass RLS
CREATE ROLE app_runtime NOSUPERUSER NOBYPASSRLS;                    -- RLS always applies
GRANT SELECT, INSERT, UPDATE ON invoices, receipts TO app_runtime; -- no DELETE, no DDL
-- migrations run as a separate migrator role, only in CI, never at runtime;
-- subject erasure (6.4) likewise runs as its own narrowly granted job: the runtime role anonymizes at most

// DON'T: runtime uses the owner/superuser connection string
const db = drizzle(postgres(process.env.SUPERUSER_URL!)); // full rights on every table
// DO: runtime uses the constrained, RLS-bound role
const db = drizzle(postgres(process.env.APP_RUNTIME_URL!)); // NOBYPASSRLS, scoped grants

// DON'T: only the happy path is tested, cross-tenant access never exercised
test('owner reads own invoice', async () => {
  const res = await app.request(`/invoices/${own}`, authAs(orgA));
  expect(res.status).toBe(200);
});
// DO: assert org A cannot reach org B's resource, and it looks absent (404)
test('cross-tenant read is not_found', async () => {
  const res = await app.request(`/invoices/${orgBInvoice}`, authAs(orgA));
  expect(res.status).toBe(404); // not 403, so existence is not disclosed
});

// DON'T: test only fetches the caller's own record
@Test void ownerReadsOwnInvoice() {
    given().auth().oauth2(tokenOrgA)
        .when().get("/invoices/" + ownInvoice).then().statusCode(200);
}
// DO: org A hitting org B's invoice must return not_found
@Test void crossTenantReadIsNotFound() {
    given().auth().oauth2(tokenOrgA)
        .when().get("/invoices/" + orgBInvoice)
        .then().statusCode(404); // hides existence, no cross-tenant leak
}

// DON'T: sequential integer in the path; guessable, enumerable, and the count leaks your volume
// GET /invoices/1041 -> /invoices/1042 is someone else's, one for-loop away if a single authz check slips
export const invoices = pgTable('invoices', { id: serial('id').primaryKey() });
// DO: UUIDv7 primary key; random enough that nothing enumerates, ordered enough that the index stays warm (10.4)
export const invoices = pgTable('invoices', {
  id: uuid('id').primaryKey().$defaultFn(() => uuidv7()),
});
// better still: no raw id in the contract where a scoped route serves
app.get('/me/invoices', requireAuth, (c) => c.json(listInvoices(c.get('claims').org_id)));
// the id is defense in depth, never the gate: cross-tenant access still returns 404 (7.1, 7.5)
// note: v7's prefix reveals creation time; if that is itself sensitive, use v4 and accept the index cost

// DON'T: IDENTITY id exposed in the path; one loop walks the whole table if any check is missed
@Entity public class Invoice { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) public Long id; }
// DO: UUIDv7 assigned at creation; time-ordered for index locality, random against enumeration
@Entity public class Invoice {
  @Id public UUID id = UuidCreator.getTimeOrderedEpoch();  // UUIDv7
}
// better still: scope by the verified identity, so most flows never carry a raw id at all
@GET @Path("/me/invoices") @Authenticated
public List<Invoice> mine(@Context SecurityIdentity id) { return repo.byOrg(id.getAttribute("org_id")); }
// unguessable is a layer on top of authorization, not a substitute: 7.1 decides, 7.5 proves it

// DON'T: a shared service key opens a firehose past all per-user scoping; leak the key, lose everything
app.get('/internal/all-orders', (c) =>
  c.req.header('x-service-key') === env.SERVICE_KEY ? c.json(db.select().from(orders)) : c.text('nope', 401));
// DO: user-scoped reads on the API (7.1); analytics reads its own copy in the platform, not this database
app.get('/me/orders', requireAuth, (c) => c.json(listOrders(c.get('claims').org_id))); // one owner, their token
// bulk analysis does not query the live API at all: an ETL lands the data in the warehouse (10.14),
// where a separate analytical identity governs access; the transactional path stays per-user, always

// DON'T: an unauthenticated bulk endpoint "for the BI tool"; it is the bypass around every control you built
@GET @Path("/internal/all-orders") @PermitAll
public List<Order> everything() { return repo.findAll(); } // the whole estate behind one shared secret
// DO: the API is user-scoped; volume for analysis comes from the platform, never from here
@GET @Path("/me/orders") @Authenticated
public List<Order> mine(@Context SecurityIdentity id) { return repo.byOrg(id.getAttribute("org_id")); }
// the warehouse (10.14) is fed by ETL and carries its own access model; the app API never serves a firehose