Moderation Tools & Audit Logging

​

Audit Log System

​

Database Schema

export const adminAuditLog = pgTable("admin_audit_log", {
  id: uuid("id").primaryKey().defaultRandom(),
  actorProfileId: uuid("actor_profile_id")
    .notNull()
    .references(() => profiles.id),
  action: text("action").notNull(),
  targetProfileId: uuid("target_profile_id")
    .references(() => profiles.id),
  metadata: text("metadata"), // JSON string
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

• actorProfileId: Admin/moderator who performed the action
• action: Action type (string identifier)
• targetProfileId: User who was acted upon (nullable for non-user actions)
• metadata: JSON-encoded context (reason, old/new values, etc.)
• createdAt: Timestamp of action

​

Logged Actions

​

Audit Log Viewer

• Displays all audit entries in reverse chronological order
• Shows actor name (via display_name join)
• Shows target name (if applicable, via left join)
• Displays action type and metadata
• Timestamp for each entry

export async function getAuditLog(): Promise<AuditLogEntry[]> {
  const rows = await db
    .select({
      id: adminAuditLog.id,
      action: adminAuditLog.action,
      actorName: sql<string>`actor.display_name`.as("actor_name"),
      targetName: sql<string | null>`target.display_name`.as("target_name"),
      metadata: adminAuditLog.metadata,
      createdAt: adminAuditLog.createdAt,
    })
    .from(adminAuditLog)
    .innerJoin(
      sql`${profiles} as actor`,
      sql`actor.id = ${adminAuditLog.actorProfileId}`
    )
    .leftJoin(
      sql`${profiles} as target`,
      sql`target.id = ${adminAuditLog.targetProfileId}`
    )
    .orderBy(desc(adminAuditLog.createdAt));

  return rows;
}

​

Ban Workflow

​

Initiating a Ban

1. Navigate to /admin/users or /admin/users/[id]
2. Click the ban icon (block symbol)
3. Dialog prompts for optional ban reason
4. Confirm action

​

Server Action Flow

export async function banUser(data: {
  profileId: string;
  reason?: string;
}): Promise<ActionResult>

1. Authorization: Verify actor is moderator/admin
2. Validation:
   • User exists and is not already banned
   • Target is not a super-admin
   • Moderators cannot ban admins
   • Cannot ban yourself
3. Database Update:
   await db
     .update(profiles)
     .set({
       bannedAt: new Date(),
       bannedBy: actor.id,
       banReason: parsed.data.reason?.trim() || null,
     })
     .where(eq(profiles.id, data.profileId));
   
4. Clerk Sync (non-blocking):
   await clerk.users.banUser(target.clerkId);
   
   Disables authentication. If Clerk call fails, Sentry reports but DB ban remains (source of truth).
5. Audit Log:
   await logAudit(actor.id, "ban_user", data.profileId, {
     reason: data.reason?.trim() || null,
   });
   
6. Revalidation: revalidatePath("/admin/users")

​

Middleware Redirect

const profile = await db.query.profiles.findFirst({
  where: eq(profiles.clerkId, userId),
  columns: { bannedAt: true },
});

if (profile?.bannedAt) {
  return NextResponse.redirect(new URL("/banned", req.url));
}

​

Ban Page

• Ban notice
• Ban reason (if provided)
• Support contact link

​

Unbanning

export async function unbanUser(data: {
  profileId: string;
}): Promise<ActionResult>

await db
  .update(profiles)
  .set({
    bannedAt: null,
    bannedBy: null,
    banReason: null,
    hiddenAt: null, // Also unhides
    hiddenBy: null,
  })
  .where(eq(profiles.id, data.profileId));

​

Hide Workflow

​

Use Cases

• Temporary content policy violations
• Suspicious accounts under investigation
• User-requested profile privacy during sensitive situations

​

Hide vs Ban

​

Server Action Flow

export async function hideUser(data: {
  profileId: string;
}): Promise<ActionResult>

• User not already hidden
• Target is not a super-admin

await db
  .update(profiles)
  .set({ hiddenAt: new Date(), hiddenBy: actor.id })
  .where(eq(profiles.id, data.profileId));

​

Public Query Filtering

function isProfileVisible(profile: { bannedAt: Date | null; hiddenAt: Date | null }) {
  return profile.bannedAt === null && profile.hiddenAt === null;
}

• getHackathonProfiles(): List page
• getProfileByUsername(): Detail page
• getHackathonProjects(): Project list (owner must be visible)
• getProjectBySlug(): Project detail (owner must be visible)

​

Mentor Application Review

​

Application Schema

export const mentorApplications = pgTable("mentor_applications", {
  id: uuid,
  name: text,
  email: text (unique),
  discordHandle: text,
  twitterHandle: text (nullable),
  websiteUrl: text (nullable),
  githubHandle: text (nullable),
  mentorTypes: text[],  // ["design", "technical", "growth"]
  background: text,
  availability: text,
  status: mentorApplicationStatusEnum,  // pending | approved | declined
  reviewedBy: uuid (FK to profiles, nullable),
  reviewedAt: timestamp (nullable),
  createdAt: timestamp,
  updatedAt: timestamp,
});

​

Review Interface

• Statistics: Total applications, pending count, approved count, declined count
• Application list: All applications with status badges
• Details: Expand to see mentor type, background, availability
• Actions: Approve or decline pending applications

​

Approval Workflow

export async function approveMentorApplication(data: {
  applicationId: string;
}): Promise<ActionResult>

1. Verify actor is admin
2. Fetch application and check status is pending
3. Update application:
   await db
     .update(mentorApplications)
     .set({
       status: "approved",
       reviewedBy: actor.id,
       reviewedAt: new Date(),
     })
     .where(eq(mentorApplications.id, data.applicationId));
   
4. Log to audit:
   await db.insert(adminAuditLog).values({
     actorProfileId: actor.id,
     action: "approve_mentor",
     targetProfileId: null,
     metadata: JSON.stringify({
       applicationId: data.applicationId,
       applicantName: application.name,
       applicantEmail: application.email,
     }),
   });
   
5. Revalidate /admin/mentors

​

Decline Workflow

export async function declineMentorApplication(data: {
  applicationId: string;
}): Promise<ActionResult>

​

Public Submission Form

• Collects contact info, social handles, mentor types, background, availability
• Validates email uniqueness (DB constraint)
• Inserts into mentorApplications with status: "pending"
• Fires Discord notification via notifyMentorApplication webhook

​

Best Practices

​

Audit Log

1. Review regularly to detect suspicious patterns (e.g., mass bans, frequent role escalations)
2. Export for compliance if required by data protection regulations
3. Reference before reversing actions to understand original context (especially ban reasons)

​

Moderation

1. Default to hide for first offenses unless behavior is egregiously abusive
2. Always provide ban reasons — logged metadata helps future reviewers understand context
3. Coordinate with team before unbanning to ensure consensus on policy violations
4. Monitor ban page traffic via analytics to track how many users hit the ban wall

​

Mentor Applications

1. Respond to pending applications within 7 days to maintain applicant trust
2. Document decision criteria in team guidelines (experience level, availability, mentor type balance)
3. Follow up with approved mentors via email to onboard and assign Discord roles (manual process)
4. Track approval rate (stats displayed on /admin/mentors) to ensure consistent standards