OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { db } from '../prisma-client';
import { getIsDry, printBoxMessage } from './helpers';
/**
* Give every member an explicit project scope.
*
* Before: a member with no `project_access` rows could reach every project,
* one with rows only those. After: `members.projectScope` says which, and
* `member_projects` lists the selected ones. Nothing is implied by absence.
*
* `project_access` is left in place and kept in sync by the app for one more
* build, so the previous image keeps working during the rolling deploy and
* a rollback needs no data change.
*
* Idempotent: scope is only ever moved towards `selected` where rows exist,
* and rows are inserted with ON CONFLICT DO NOTHING, so a rerun converges on
* the same state. Members already set to `selected` with no rows (restricted
* to nothing by the new UI) are not touched.
*
* Levels: `read` was a deliberate restriction and is kept. `write` and `admin`
* were the unenforced defaults, so they become NULL and derive from the role.
*
* Pending invites got the column default `all`; one with grants was
* restricted and becomes `selected`. `project_access` rows with no membership
* are reported and left alone: they never granted access, so making them
* memberships would.
*
* An install that upgrades past the next build gets `project_access` folded in
* and dropped by that build's Prisma migration before this runs, so only the
* invite and slug steps are left to do.
*/
interface Count {
count: bigint;
}
async function countMembersToScope(): Promise<number> {
const rows = await db.$queryRaw<Count[]>`
SELECT COUNT(*) AS count
FROM members m
WHERE m."projectScope" = 'all'
AND EXISTS (
SELECT 1 FROM project_access pa
WHERE pa."userId" = m."userId"
AND pa."organizationId" = m."organizationId"
)
`;
return Number(rows[0]?.count ?? 0);
}
// Not async: `$transaction([...])` needs the PrismaPromise itself.
function scopeMembers() {
return db.$executeRaw`
UPDATE members m
SET "projectScope" = 'selected'
WHERE m."projectScope" = 'all'
AND EXISTS (
SELECT 1 FROM project_access pa
WHERE pa."userId" = m."userId"
AND pa."organizationId" = m."organizationId"
)
`;
}
function copyGrants() {
return db.$executeRaw`
INSERT INTO member_projects ("memberId", "projectId", "level")
SELECT m.id, pa."projectId",
CASE WHEN bool_or(pa.level = 'read') THEN 'read'::"AccessLevel" END
FROM project_access pa
JOIN members m
ON m."userId" = pa."userId"
AND m."organizationId" = pa."organizationId"
-- project_access has no unique key; a read row among duplicates wins.
GROUP BY m.id, pa."projectId"
ON CONFLICT DO NOTHING
`;
}
async function countInvitesToScope(): Promise<number> {
const rows = await db.$queryRaw<Count[]>`
SELECT COUNT(*) AS count FROM invites
WHERE "projectScope" = 'all' AND jsonb_array_length("projectAccess") > 0
`;
return Number(rows[0]?.count ?? 0);
}
function scopeInvites() {
return db.$executeRaw`
UPDATE invites
SET "projectScope" = 'selected'
WHERE "projectScope" = 'all'
AND jsonb_array_length("projectAccess") > 0
`;
}
async function countGrantsWithoutMembership(): Promise<number> {
const rows = await db.$queryRaw<Count[]>`
SELECT COUNT(*) AS count
FROM project_access pa
WHERE NOT EXISTS (
SELECT 1 FROM members m
WHERE m."userId" = pa."userId"
AND m."organizationId" = pa."organizationId"
)
`;
return Number(rows[0]?.count ?? 0);
}
async function hasProjectAccessTable(): Promise<boolean> {
const rows = await db.$queryRaw<{ exists: boolean }[]>`
SELECT to_regclass('public.project_access') IS NOT NULL AS exists
`;
return rows[0]?.exists ?? false;
}
async function countGrants(): Promise<number> {
const rows = await db.$queryRaw<Count[]>`
SELECT COUNT(*) AS count FROM project_access
`;
return Number(rows[0]?.count ?? 0);
}
async function countOrganizationsWithoutSlug(): Promise<number> {
return db.organization.count({ where: { slug: null } });
}
// A slug equal to the id can never collide with another organization's;
// new organizations get the same.
function backfillSlugs() {
return db.$executeRaw`UPDATE organizations SET slug = id WHERE slug IS NULL`;
}
export async function up() {
const isDry = getIsDry();
const hasGrants = await hasProjectAccessTable();
const [membersToScope, grantsToCopy, invitesToScope, orphanGrants] =
await Promise.all([
hasGrants ? countMembersToScope() : 0,
hasGrants ? countGrants() : 0,
countInvitesToScope(),
hasGrants ? countGrantsWithoutMembership() : 0,
]);
const orphanLine = `project_access rows without a membership, left as is: ${orphanGrants}`;
if (isDry) {
const slugs = await countOrganizationsWithoutSlug();
printBoxMessage('🧪 Dry run: member project scope', [
`Members to set to "selected": ${membersToScope}`,
`project_access rows to copy: ${grantsToCopy}`,
`Pending invites to set to "selected": ${invitesToScope}`,
orphanLine,
`Organizations without a slug: ${slugs}`,
]);
return;
}
const [scoped, copied] = hasGrants
? await db.$transaction([scopeMembers(), copyGrants()])
: [0, 0];
const invitesScoped = await scopeInvites();
const slugs = await backfillSlugs();
printBoxMessage('✅ Member project scope', [
`Members set to "selected": ${scoped}`,
`member_projects rows inserted: ${copied} (of ${grantsToCopy} grants)`,
`Pending invites set to "selected": ${invitesScoped}`,
orphanLine,
`Organization slugs set: ${slugs}`,
]);
}