Remove userId from Site Model - Implementation Plan

Remove userId from Site Model - Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Remove the redundant userId column from the Site model since ownership is now tracked through the Publication model (User -> Publication -> Site).

Architecture: Replace all site.userId checks with site.publication.ownerId. Replace site.user relation lookups (ghUsername-based) with publication-based lookups or siteId-based lookups. Change the unique constraint from @@unique([userId, projectName]) to @@unique([publicationId, projectName]). Drop the user_id column via Prisma migration.

Tech Stack: Prisma ORM, PostgreSQL, Next.js (App Router), tRPC, NextAuth

Summary of Changes

The userId field on Site is used in 5 categories:

  1. Authorization - checking site.userId === session.user.id (3 places)
  2. Site creation - connecting user to site (1 place)
  3. Public lookups - filtering sites by user.ghUsername (4 procedures + callers)
  4. Analytics/GTM - sending site.userId in events (4 places)
  5. Scripts - seed/admin scripts using userId (3 scripts)

Task 1: Update Prisma Schema

Files:

  • Modify: prisma/schema.prisma:11-29 (User model)
  • Modify: prisma/schema.prisma:101-130 (Site model)

Step 1: Update the Site model in schema.prisma

Remove userId, the user relation, @@unique([userId, projectName]), and @@index([userId]). Add @@unique([publicationId, projectName]).

model Site {
  id               String        @id @default(cuid())
  ghRepository     String        @map("gh_repository")
  ghBranch         String        @map("gh_branch")
  rootDir          String?       @map("root_dir")
  projectName      String        @map("project_name")
  title            String?
  description      String?
  modifiedAt       DateTime?     @default(now()) @map("modified_at")
  createdAt        DateTime      @default(now()) @map("created_at")
  updatedAt        DateTime      @updatedAt @map("updated_at")
  autoSync         Boolean       @default(false) @map("auto_sync")
  webhookId        String?       @unique @map("webhook_id")
  enableComments   Boolean       @default(false) @map("enable_comments")
  giscusRepoId     String?       @map("giscus_repo_id")
  giscusCategoryId String?       @map("giscus_category_id")
  type             SiteType      @default(DATAPACKAGE)
  tree             Json?
  publicationId    String        @map("publication_id")
  blobs            Blob[]
  likes            Like[]
  publication      Publication   @relation(fields: [publicationId], references: [id])
  stats            SiteStat[]
  authors          SiteAuthor[]

  @@unique([publicationId, projectName])
  @@index([publicationId])
}

Step 2: Update the User model - remove sites relation

Remove the sites Site[] line from the User model (line 27). Keep siteAuthors since that's the new relationship.

model User {
  id                       String                    @id @default(cuid())
  name                     String?
  username                 String?
  ghUsername               String?                   @map("gh_username")
  email                    String?                   @unique
  emailVerified            DateTime?                 @map("email_verified")
  image                    String?
  role                     Role                      @default(USER)
  createdAt                DateTime                  @default(now()) @map("created_at")
  updatedAt                DateTime                  @updatedAt @map("updated_at")
  accounts                 Account[]
  likes                    Like[]
  publications             Publication[]
  publicationSubscriptions PublicationSubscription[]
  sessions                 Session[]
  siteAuthors              SiteAuthor[]
}

Step 3: Generate Prisma migration

Run: cd datahub-next && npx prisma migrate dev --name remove_userid_from_site

This migration will:

  • Drop the user_id column from the Site table
  • Drop the Site_user_id_idx index
  • Drop the Site_user_id_project_name_key unique constraint
  • Add the Site_publication_id_project_name_key unique constraint
  • Add the Site_publication_id_idx index (if not already present)

IMPORTANT: Review the generated migration SQL before applying. The migration may require manual editing if there are foreign key constraints. If it asks to drop data, verify it's only dropping the user_id column.

Step 4: Generate Prisma client

Run: cd datahub-next && npx prisma generate

Step 5: Commit

git add prisma/schema.prisma prisma/migrations/
git commit -m "feat: remove userId from Site model, add publicationId+projectName unique constraint"

Task 2: Update SiteWithPublication Type

Files:

  • Modify: types.ts:1-8

Step 1: Update the type definition

Remove the user property since the relation no longer exists:

import { Site, Publication } from "@prisma/client";

export type SiteWithPublication = Site & {
  publication: Publication;
};

Step 2: Commit

git add types.ts
git commit -m "refactor: remove user from SiteWithPublication type"

Task 3: Update Authorization - withSiteAuth

Files:

  • Modify: server/auth.ts:219-244

Step 1: Update withSiteAuth to check ownership through publication

Replace the withSiteAuth function:

export function withSiteAuth(action: any) {
  return async (
    formData: FormData | null,
    siteId: string,
    key: string | null,
  ) => {
    const session = await getSession();
    if (!session) {
      return {
        error: "Not authenticated",
      };
    }
    const site = await prisma.site.findUnique({
      where: {
        id: siteId,
      },
      include: {
        publication: true,
      },
    });
    if (!site || site.publication.ownerId !== session.user.id) {
      return {
        error: "Not authorized",
      };
    }

    return action(formData, site, key);
  };
}

Step 2: Verify the change compiles

Run: cd datahub-next && npx tsc --noEmit --pretty 2>&1 | head -30

Step 3: Commit

git add server/auth.ts
git commit -m "refactor: withSiteAuth checks ownership via publication"

Task 4: Update Authorization - Settings Layout

Files:

  • Modify: app/dashboard/sites/[id]/settings/layout.tsx

Step 1: Update the auth check to use publication.ownerId

import { ReactNode } from "react";
import { getSession } from "@/server/auth";
import prisma from "@/server/db";
import { notFound, redirect } from "next/navigation";
import SiteSettingsHeader from "./header";

export default async function SiteSettingsLayout({
  params,
  children,
}: {
  params: { id: string };
  children: ReactNode;
}) {
  const session = await getSession();
  if (!session) {
    redirect("/login");
  }

  const site = await prisma.site.findUnique({
    where: {
      id: decodeURIComponent(params.id),
    },
    include: {
      publication: true,
    },
  });

  if (!site || site.publication.ownerId !== session.user.id) {
    notFound();
  }

  return (
    <>
      <SiteSettingsHeader site={site} />
      {children}
    </>
  );
}

Step 2: Commit

git add app/dashboard/sites/[id]/settings/layout.tsx
git commit -m "refactor: settings layout checks ownership via publication"

Task 5: Update Site Router - create procedure

Files:

  • Modify: server/api/routers/site.ts:62-117

Step 1: Update unique name check to use publication instead of user

Change the while loop (line 62-71) from checking { user: { id: ctx.session.user.id } } to checking { publicationId }:

      while (
        await ctx.db.site.findFirst({
          where: {
            AND: [{ projectName }, { publicationId }],
          },
        })
      ) {
        projectName = `${input.ghRepository.split("/")[1]}-${num}`;
        num++;
      }

Step 2: Remove user connect from site creation

Change the create call (line 105-117) to remove user: { connect: ... }:

      const site = await ctx.db.site.create({
        data: {
          projectName,
          ghRepository,
          ghBranch,
          rootDir,
          type: input.type,
          autoSync: false,
          webhookId: null,
          publication: { connect: { id: publicationId } },
        },
      });

Step 3: Commit

git add server/api/routers/site.ts
git commit -m "refactor: site.create uses publication for uniqueness, removes user connect"

Task 6: Update Site Router - getSyncStatus authorization

Files:

  • Modify: server/api/routers/site.ts:364-373

Step 1: Update the auth check to use publication

        const site = await ctx.db.site.findUnique({
          where: { id: input.id },
          include: { publication: true },
        });

        if (!site || site.publication.ownerId !== ctx.session.user.id) {
          throw new TRPCError({
            code: "NOT_FOUND",
            message: "Post not found",
          });
        }

Step 2: Commit

git add server/api/routers/site.ts
git commit -m "refactor: getSyncStatus checks ownership via publication"

Task 7: Update Site Router - update procedure (cache revalidation)

Files:

  • Modify: server/api/routers/site.ts:175-273

Step 1: Update the include to use publication with owner instead of user

Change line 177 from include: { user: true } to:

        include: { publication: { include: { owner: true } } },

Step 2: Update revalidateTag calls

Change line 262 from site?.user?.ghUsername to site?.publication?.owner?.ghUsername:

      revalidateTag(`${site?.publication?.owner?.ghUsername}-${site?.projectName}-metadata`);

And lines 267-271:

        revalidateTag(
          `${site?.publication?.owner?.ghUsername}-${site?.projectName}-permalinks`,
        );
        revalidateTag(
          `${site?.publication?.owner?.ghUsername}-${site?.projectName}-page-content`,
        );

Step 3: Commit

git add server/api/routers/site.ts
git commit -m "refactor: site.update uses publication.owner for cache tags"

Task 8: Update Site Router - getById, get, getCustomStyles, getConfig, getAll, getBlob includes

Files:

  • Modify: server/api/routers/site.ts (multiple procedures)

Step 1: Update getById (line 447-464)

Remove user: true from the include. The publication is already included. Add publication: { include: { owner: true } } to maintain backwards compatibility for callers that need the owner info:

  getById: protectedProcedure
    .input(z.object({ id: z.string().min(1) }))
    .query(async ({ ctx, input }) => {
      return await ctx.db.site.findFirst({
        where: { id: input.id },
        include: {
          publication: {
            include: {
              owner: true,
            },
          },
          authors: {
            include: {
              user: {
                select: {
                  id: true,
                  name: true,
                  username: true,
                  image: true,
                  email: true,
                },
              },
            },
          },
        },
      });
    }),

Step 2: Update get procedure (line 466-503)

Change from filtering by user.ghUsername to filtering by publication.owner.ghUsername. Update the select to use publication instead of user:

  get: publicProcedure
    .input(
      z.object({
        ghUsername: z.string().min(1),
        projectName: z.string().min(1),
      }),
    )
    .query(async ({ ctx, input }) => {
      return await unstable_cache(
        async () => {
          return ctx.db.site.findFirst({
            where: {
              AND: [
                { projectName: input.projectName },
                { publication: { owner: { ghUsername: input.ghUsername } } },
              ],
            },
            include: {
              publication: {
                include: {
                  owner: {
                    select: {
                      ghUsername: true,
                    },
                  },
                },
              },
            },
          });
        },
        [`${input.ghUsername} - ${input.projectName} - metadata`],
        {
          revalidate: 60,
          tags: [`${input.ghUsername} - ${input.projectName} - metadata`],
        },
      )();
    }),

Step 3: Update getCustomStyles (line 504-546)

Change from filtering by user.ghUsername to publication.owner.ghUsername:

  getCustomStyles: publicProcedure
    .input(
      z.object({
        ghUsername: z.string().min(1),
        projectName: z.string().min(1),
      }),
    )
    .query(async ({ ctx, input }) => {
      return await unstable_cache(
        async () => {
          const site = await ctx.db.site.findFirst({
            where: {
              AND: [
                { projectName: input.projectName },
                { publication: { owner: { ghUsername: input.ghUsername } } },
              ],
            },
          });
          // ... rest unchanged

Step 4: Update getConfig (line 547-591)

Same pattern - change from filtering by user.ghUsername to publication.owner.ghUsername:

          const site = await ctx.db.site.findFirst({
            where: {
              AND: [
                { projectName: input.projectName },
                { publication: { owner: { ghUsername: input.ghUsername } } },
              ],
            },
          });

Step 5: Update getSiteTree (line 602-607)

Remove user: true from include:

          const site = await ctx.db.site.findUnique({
            where: { id: input.siteId },
            include: {
              publication: true,
            },
          });

Step 6: Update getPermalinks (line 659-664)

Remove user: true from include:

          const site = await ctx.db.site.findUnique({
            where: { id: input.siteId },
            include: {
              publication: true,
            },
          });

Step 7: Update getAll (line 838)

Change from include: { user: true } to:

    return await ctx.db.site.findMany({ include: { publication: { include: { owner: true } } } });

Step 8: Update getBlob (line 862)

Remove include: { user: true } since the user data is not actually used in the getBlob return:

          const site = await ctx.db.site.findFirst({
            where: {
              id: input.siteId,
            },
          });

Step 9: Commit

git add server/api/routers/site.ts
git commit -m "refactor: replace site.user includes with publication.owner"

Task 9: Update Publication Router - getPost include

Files:

  • Modify: server/api/routers/publication.ts:436-452

Step 1: Replace user: true with publication owner info

The getPost procedure includes user: true on the site. Remove it since the publication with owner is the source of truth. The publication is already included:

        include: {
          publication: {
            include: {
              owner: {
                select: {
                  ghUsername: true,
                },
              },
            },
          },
          authors: {
            include: {
              user: {
                select: {
                  id: true,
                  name: true,
                  username: true,
                  image: true,
                },
              },
            },
          },
        },

Step 2: Commit

git add server/api/routers/publication.ts
git commit -m "refactor: publication.getPost uses publication.owner instead of site.user"

Task 10: Update Public Pages - Layout and Renderers

Files:

  • Modify: app/[publication]/[post]/[[...slug]]/layout.tsx:37-51
  • Modify: components/site-renderers/DatasetPage.tsx:23-26
  • Modify: components/site-renderers/ObservablePage.tsx:27-29

Step 1: Update public layout

Change line 37 from site.user?.ghUsername! to site.publication?.owner?.ghUsername!:

  const ghUsername = site.publication?.owner?.ghUsername!;

NOTE: This requires the SiteWithPublication type to include the owner's ghUsername. Since the getPost query (Task 9) now includes publication.owner.ghUsername, this will work. However, the SiteWithPublication type needs to accommodate this. We may need to extend the type or use a type assertion.

Step 2: Update DatasetPage.tsx

Change line 24 from site.user!.ghUsername! to:

    siteConfig = await api.site.getConfig.query({
      ghUsername: (site as any).publication?.owner?.ghUsername!,
      projectName: site.projectName,
    });

Step 3: Update ObservablePage.tsx

Same pattern - change line 28:

    siteConfig = await api.site.getConfig.query({
      ghUsername: (site as any).publication?.owner?.ghUsername!,
      projectName: site.projectName,
    });

Step 4: Update SiteWithPublication type to support the new shape

Update types.ts to include the nested owner:

import { Site, Publication } from "@prisma/client";

export type SiteWithPublication = Site & {
  publication: Publication & {
    owner?: {
      ghUsername: string | null;
    };
  };
};

Step 5: Commit

git add app/[publication]/[post]/[[...slug]]/layout.tsx components/site-renderers/DatasetPage.tsx components/site-renderers/ObservablePage.tsx types.ts
git commit -m "refactor: public pages use publication.owner.ghUsername instead of site.user.ghUsername"

Task 11: Update Webhook Handler

Files:

  • Modify: app/api/webhook/route.ts

Step 1: Replace include: { user: true } with include: { publication: true }

Update both site lookups (lines 28-35 and 41-48):

  let site;
  if (siteId) {
    site = await prisma.site.findUnique({
      where: {
        id: siteId,
      },
      include: {
        publication: true,
      },
    });
  }

  if (!site && webhookId) {
    site = await prisma.site.findUnique({
      where: {
        webhookId,
      },
      include: {
        publication: true,
      },
    });
  }

Step 2: Update GTM event to use publication.ownerId

Change lines 62-67:

        client_id: site.publication.ownerId,
        events: [
          {
            name: "auto_sync",
            params: {
              user_id: site.publication.ownerId,
              site_id: site.id,
            },
          },
        ],

Step 3: Update account lookup to use publication.ownerId

Change lines 86-89:

  const account = await prisma.account.findFirst({
    where: {
      userId: site.publication.ownerId,
    },
  });

Step 4: Commit

git add app/api/webhook/route.ts
git commit -m "refactor: webhook handler uses publication.ownerId instead of site.userId"

Task 12: Update SiteAuthorsForm

Files:

  • Modify: app/dashboard/sites/[id]/settings/page.tsx:180-184
  • Modify: components/form/site-authors-form.tsx:20-27

Step 1: Update settings page to pass publication.ownerId

Change lines 180-184 in page.tsx. The getById now returns publication.owner instead of user, so:

        <SiteAuthorsForm
          siteId={site.id}
          ownerUserId={site.publication.owner.id}
          initialAuthors={site.authors ?? []}
        />

NOTE: ownerUserId prop type stays string - no change needed in the form component itself. The component logic is the same: it filters out the owner from the list of editable authors and prevents adding/removing the owner.

Step 2: Commit

git add app/dashboard/sites/[id]/settings/page.tsx
git commit -m "refactor: SiteAuthorsForm receives publication.owner.id instead of site.userId"

Task 13: Update GTM Events in Create/Delete Site

Files:

  • Modify: app/dashboard/sites/new/page.tsx:93-97
  • Modify: components/modal/create-site.tsx:94-98
  • Modify: components/form/delete-site-form.tsx:22-26

Step 1: Update new site page GTM event (line 93-97)

The create mutation returns the site object which no longer has userId. Use the session user ID instead or just the site ID. Since these are client components, we don't have the publication owner readily. The simplest fix is to remove user_id from the event or use the site ID:

      onSuccess: (res) => {
        sendGTMEvent({
          event: "create_site",
          site_id: res.id,
        });
        router.push(`/dashboard/sites/${res.id}/settings`);
        router.refresh();
      },

Step 2: Update create-site modal GTM event (line 94-98)

      onSuccess: (res) => {
        sendGTMEvent({
          event: "create_site",
          site_id: res.id,
        });
        modal?.hide();
        router.push(`/site/${res.id}/settings`);
        router.refresh();
      },

Step 3: Update delete-site-form GTM event (line 22-26)

      onSuccess: (res) => {
        if (env.NEXT_PUBLIC_VERCEL_ENV === "production") {
          sendGTMEvent({
            event: "delete_site",
            site_id: res?.id,
          });
        }

Step 4: Commit

git add app/dashboard/sites/new/page.tsx components/modal/create-site.tsx components/form/delete-site-form.tsx
git commit -m "refactor: GTM events no longer reference site.userId"

Task 14: Update Settings Header

Files:

  • Modify: app/dashboard/sites/[id]/settings/header.tsx

Step 1: Verify the SiteSettingsHeader component

The header receives site: SiteWithPublication and only uses site.projectName, site.ghRepository, site.publication, and site.id. It does NOT reference site.user, so no changes needed. Just verify it still compiles with the updated type.

Run: cd datahub-next && npx tsc --noEmit --pretty 2>&1 | head -30

Task 15: Update Scripts

Files:

  • Modify: scripts/create-sites-from-subfolders.ts
  • Modify: scripts/seed-observable.js
  • Modify: scripts/move-sites-by-repository.ts

Step 1: Update create-sites-from-subfolders.ts

Change the duplicate check (lines 173-179) from userId_projectName to publicationId_projectName:

        const existingSite = await prisma.site.findUnique({
          where: {
            publicationId_projectName: {
              publicationId: publication.id,
              projectName: projectName,
            },
          },
        });

Remove userId from site creation (line 199):

        const site = await prisma.site.create({
          data: {
            projectName,
            ghRepository,
            ghBranch: "main",
            rootDir: dir,
            publicationId: publication.id,
            autoSync: false,
          },
        });

Step 2: Update seed-observable.js

Change the upsert (lines 103-121) from userId_projectName to publicationId_projectName. This script also needs a publication to be created/found first:

NOTE: This script is a dev seed script. It will need to be updated to create/find a publication before creating the site. The userId_projectName unique constraint no longer exists. This script needs a more significant rewrite. For now, update it to use publicationId_projectName and add a step to create/find a publication for the user. This is a seed script so the exact implementation can be pragmatic.

  // Create or find publication for user
  const publication = await prisma.publication.upsert({
    where: { slug: ghUsername },
    update: {},
    create: {
      slug: ghUsername,
      name: ghUsername,
      ownerId: user.id,
    },
  });

  const site = await prisma.site.upsert({
    where: {
      publicationId_projectName: {
        publicationId: publication.id,
        projectName: projectName,
      },
    },
    update: {
      type: "OBSERVABLE",
    },
    create: {
      projectName: projectName,
      ghRepository: `${ghUsername}/${projectName}`,
      ghBranch: branch,
      type: "OBSERVABLE",
      publicationId: publication.id,
    },
  });

Step 3: Update move-sites-by-repository.ts

Remove user: true from the include (line 97) and update display code (lines 115-120):

    include: {
      publication: {
        include: {
          owner: true,
        },
      },
    },

Update the owner display:

    const owner = site.publication?.owner
      ? site.publication.owner.ghUsername || site.publication.owner.name
      : "Unknown";

And line 157:

      site.publication?.owner ? site.publication.owner.ghUsername || site.publication.owner.name : "Unknown"

Step 4: Commit

git add scripts/create-sites-from-subfolders.ts scripts/seed-observable.js scripts/move-sites-by-repository.ts
git commit -m "refactor: scripts use publicationId_projectName instead of userId_projectName"

Task 16: Verify and Fix Compilation

Step 1: Run TypeScript compilation check

Run: cd datahub-next && npx tsc --noEmit --pretty 2>&1

Step 2: Fix any remaining type errors

Search for any remaining references to site.userId or site.user that were missed:

Run: cd datahub-next && grep -rn "site\.userId\|site\.user[^A]" --include="*.ts" --include="*.tsx" --include="*.js" --include="*.mjs" | grep -v node_modules | grep -v ".next"

Step 3: Fix any issues found

Step 4: Commit any fixes

git add -A
git commit -m "fix: resolve remaining site.userId / site.user references"

Task 17: Test the Application

Step 1: Run the test suite

Run: cd datahub-next && npm test 2>&1

Step 2: Run the dev server to verify no runtime errors

Run: cd datahub-next && npm run dev

Manually verify:

  • Dashboard loads
  • Site settings page loads
  • Create site flow works
  • Public site pages render

Step 3: Fix any test failures

Step 4: Final commit

git add -A
git commit -m "fix: resolve test failures from userId removal"

Risk Assessment

  1. Database migration on production: The migration drops a column and changes a unique constraint. Ensure a database backup before running. The user_id column is nullable, so dropping it won't fail due to NOT NULL constraints.

  2. Unique constraint change: Changing from @@unique([userId, projectName]) to @@unique([publicationId, projectName]) could fail if there are duplicate (publicationId, projectName) pairs in the database. Check for duplicates before migrating:

    SELECT publication_id, project_name, COUNT(*)
FROM "Site"
GROUP BY publication_id, project_name
HAVING COUNT(*) > 1;

  3. Cache invalidation: The revalidateTag patterns in the update procedure use ghUsername-projectName. These tags are shared with the cached get procedure. After migration, the tags will use publication.owner.ghUsername which should resolve to the same value. Old cached entries will expire naturally (60s TTL).

  4. seed-observable.js: This script doesn't have a publication setup step. After migration, it will need one. This is a dev-only script.