chore: add KAN_ADMIN_API_KEY to docker and readme

README.md

@@ -138,44 +138,45 @@ pnpm dev
 
 ## Environment Variables 🔐
 
-| Variable                                  | Description                                               | Required                 | Example                                                     |
-| ----------------------------------------- | --------------------------------------------------------- | ------------------------ | ----------------------------------------------------------- |
-| `POSTGRES_URL`                            | PostgreSQL connection URL                                 | To use external database | `postgres://user:pass@localhost:5432/db`                    |
-| `EMAIL_FROM`                              | Sender email address                                      | For Email                | `"Kan <hello@mail.kan.bn>"`                                 |
-| `SMTP_HOST`                               | SMTP server hostname                                      | For Email                | `smtp.resend.com`                                           |
-| `SMTP_PORT`                               | SMTP server port                                          | For Email                | `465`                                                       |
-| `SMTP_USER`                               | SMTP username/email                                       | No                       | `resend`                                                    |
-| `SMTP_PASSWORD`                           | SMTP password/token                                       | No                       | `re_xxxx`                                                   |
-| `SMTP_SECURE`                             | Use secure SMTP connection (defaults to true if not set)  | For Email                | `true`                                                      |
-| `SMTP_REJECT_UNAUTHORIZED`                | Reject invalid certificates (defaults to true if not set) | For Email                | `false`                                                     |
-| `NEXT_PUBLIC_DISABLE_EMAIL`               | To disable all email features                             | For Email                | `true`                                                      |
-| `NEXT_PUBLIC_BASE_URL`                    | Base URL of your installation                             | Yes                      | `http://localhost:3000`                                     |
-| `BETTER_AUTH_ALLOWED_DOMAINS`             | Comma-separated list of allowed domains for OIDC logins  | For OIDC/Social login    | `example.com,subsidiary.com`                                |
-| `BETTER_AUTH_SECRET`                      | Auth encryption secret                                    | Yes                      | Random 32+ char string                                      |
-| `BETTER_AUTH_TRUSTED_ORIGINS`             | Allowed callback origins                                  | No                       | `http://localhost:3000,http://localhost:3001`               |
-| `GOOGLE_CLIENT_ID`                        | Google OAuth client ID                                    | For Google login         | `xxx.apps.googleusercontent.com`                            |
-| `GOOGLE_CLIENT_SECRET`                    | Google OAuth client secret                                | For Google login         | `xxx`                                                       |
-| `DISCORD_CLIENT_ID`                       | Discord OAuth client ID                                   | For Discord login        | `xxx`                                                       |
-| `DISCORD_CLIENT_SECRET`                   | Discord OAuth client secret                               | For Discord login        | `xxx`                                                       |
-| `GITHUB_CLIENT_ID`                        | GitHub OAuth client ID                                    | For GitHub login         | `xxx`                                                       |
-| `GITHUB_CLIENT_SECRET`                    | GitHub OAuth client secret                                | For GitHub login         | `xxx`                                                       |
-| `OIDC_CLIENT_ID`                          | Generic OIDC client ID                                    | For OIDC login           | `xxx`                                                       |
-| `OIDC_CLIENT_SECRET`                      | Generic OIDC client secret                                | For OIDC login           | `xxx`                                                       |
-| `OIDC_DISCOVERY_URL`                      | OIDC discovery URL                                        | For OIDC login           | `https://auth.example.com/.well-known/openid-configuration` |
-| `TRELLO_APP_API_KEY`                      | Trello app API key                                        | For Trello import        | `xxx`                                                       |
-| `TRELLO_APP_API_SECRET`                   | Trello app API secret                                     | For Trello import        | `xxx`                                                       |
-| `S3_REGION`                               | S3 storage region                                         | For file uploads         | `WEUR`                                                      |
-| `S3_ENDPOINT`                             | S3 endpoint URL                                           | For file uploads         | `https://xxx.r2.cloudflarestorage.com`                      |
+| Variable                                  | Description                                               | Required                              | Example                                                     |
+| ----------------------------------------- | --------------------------------------------------------- | ------------------------------------- | ----------------------------------------------------------- |
+| `POSTGRES_URL`                            | PostgreSQL connection URL                                 | To use external database              | `postgres://user:pass@localhost:5432/db`                    |
+| `EMAIL_FROM`                              | Sender email address                                      | For Email                             | `"Kan <hello@mail.kan.bn>"`                                 |
+| `SMTP_HOST`                               | SMTP server hostname                                      | For Email                             | `smtp.resend.com`                                           |
+| `SMTP_PORT`                               | SMTP server port                                          | For Email                             | `465`                                                       |
+| `SMTP_USER`                               | SMTP username/email                                       | No                                    | `resend`                                                    |
+| `SMTP_PASSWORD`                           | SMTP password/token                                       | No                                    | `re_xxxx`                                                   |
+| `SMTP_SECURE`                             | Use secure SMTP connection (defaults to true if not set)  | For Email                             | `true`                                                      |
+| `SMTP_REJECT_UNAUTHORIZED`                | Reject invalid certificates (defaults to true if not set) | For Email                             | `false`                                                     |
+| `NEXT_PUBLIC_DISABLE_EMAIL`               | To disable all email features                             | For Email                             | `true`                                                      |
+| `NEXT_PUBLIC_BASE_URL`                    | Base URL of your installation                             | Yes                                   | `http://localhost:3000`                                     |
+| `BETTER_AUTH_ALLOWED_DOMAINS`             | Comma-separated list of allowed domains for OIDC logins   | For OIDC/Social login                 | `example.com,subsidiary.com`                                |
+| `BETTER_AUTH_SECRET`                      | Auth encryption secret                                    | Yes                                   | Random 32+ char string                                      |
+| `BETTER_AUTH_TRUSTED_ORIGINS`             | Allowed callback origins                                  | No                                    | `http://localhost:3000,http://localhost:3001`               |
+| `GOOGLE_CLIENT_ID`                        | Google OAuth client ID                                    | For Google login                      | `xxx.apps.googleusercontent.com`                            |
+| `GOOGLE_CLIENT_SECRET`                    | Google OAuth client secret                                | For Google login                      | `xxx`                                                       |
+| `DISCORD_CLIENT_ID`                       | Discord OAuth client ID                                   | For Discord login                     | `xxx`                                                       |
+| `DISCORD_CLIENT_SECRET`                   | Discord OAuth client secret                               | For Discord login                     | `xxx`                                                       |
+| `GITHUB_CLIENT_ID`                        | GitHub OAuth client ID                                    | For GitHub login                      | `xxx`                                                       |
+| `GITHUB_CLIENT_SECRET`                    | GitHub OAuth client secret                                | For GitHub login                      | `xxx`                                                       |
+| `OIDC_CLIENT_ID`                          | Generic OIDC client ID                                    | For OIDC login                        | `xxx`                                                       |
+| `OIDC_CLIENT_SECRET`                      | Generic OIDC client secret                                | For OIDC login                        | `xxx`                                                       |
+| `OIDC_DISCOVERY_URL`                      | OIDC discovery URL                                        | For OIDC login                        | `https://auth.example.com/.well-known/openid-configuration` |
+| `TRELLO_APP_API_KEY`                      | Trello app API key                                        | For Trello import                     | `xxx`                                                       |
+| `TRELLO_APP_API_SECRET`                   | Trello app API secret                                     | For Trello import                     | `xxx`                                                       |
+| `S3_REGION`                               | S3 storage region                                         | For file uploads                      | `WEUR`                                                      |
+| `S3_ENDPOINT`                             | S3 endpoint URL                                           | For file uploads                      | `https://xxx.r2.cloudflarestorage.com`                      |
 | `S3_ACCESS_KEY_ID`                        | S3 access key                                             | For file uploads (optional with IRSA) | `xxx`                                                       |
 | `S3_SECRET_ACCESS_KEY`                    | S3 secret key                                             | For file uploads (optional with IRSA) | `xxx`                                                       |
-| `S3_FORCE_PATH_STYLE`                     | Use path-style URLs for S3                                | For file uploads         | `true`                                                      |
-| `NEXT_PUBLIC_STORAGE_URL`                 | Storage service URL                                       | For file uploads         | `https://storage.kanbn.com`                                 |
-| `NEXT_PUBLIC_STORAGE_DOMAIN`              | Storage domain name                                       | For file uploads         | `kanbn.com`                                                 |
-| `NEXT_PUBLIC_AVATAR_BUCKET_NAME`          | S3 bucket name for avatars                                | For file uploads         | `avatars`                                                   |
-| `NEXT_PUBLIC_ATTACHMENTS_BUCKET_NAME`     | S3 bucket name for attachments                            | For file uploads         | `attachments`                                               |
-| `NEXT_PUBLIC_ALLOW_CREDENTIALS`           | Allow email & password login                              | For authentication       | `true`                                                      |
-| `NEXT_PUBLIC_DISABLE_SIGN_UP`             | Disable sign up                                           | For authentication       | `false`                                                     |
-| `NEXT_PUBLIC_WHITE_LABEL_HIDE_POWERED_BY` | Hide “Powered by kan.bn” on public boards (self-host)     | For white labelling      | `true`                                                      |
+| `S3_FORCE_PATH_STYLE`                     | Use path-style URLs for S3                                | For file uploads                      | `true`                                                      |
+| `NEXT_PUBLIC_STORAGE_URL`                 | Storage service URL                                       | For file uploads                      | `https://storage.kanbn.com`                                 |
+| `NEXT_PUBLIC_STORAGE_DOMAIN`              | Storage domain name                                       | For file uploads                      | `kanbn.com`                                                 |
+| `NEXT_PUBLIC_AVATAR_BUCKET_NAME`          | S3 bucket name for avatars                                | For file uploads                      | `avatars`                                                   |
+| `NEXT_PUBLIC_ATTACHMENTS_BUCKET_NAME`     | S3 bucket name for attachments                            | For file uploads                      | `attachments`                                               |
+| `NEXT_PUBLIC_ALLOW_CREDENTIALS`           | Allow email & password login                              | For authentication                    | `true`                                                      |
+| `NEXT_PUBLIC_DISABLE_SIGN_UP`             | Disable sign up                                           | For authentication                    | `false`                                                     |
+| `NEXT_PUBLIC_WHITE_LABEL_HIDE_POWERED_BY` | Hide “Powered by kan.bn” on public boards (self-host)     | For white labelling                   | `true`                                                      |
+| `KAN_ADMIN_API_KEY`                       | Admin API key for stats and admin endpoints               | For admin/monitoring                  | `your-secret-admin-key`                                     |
 
 See `.env.example` for a complete list of supported environment variables.
 

apps/web/src/env.ts

@@ -14,6 +14,7 @@ export const env = createEnv({
    * This way you can ensure the app isn't built with invalid env vars.
    */
   server: {
+    KAN_ADMIN_API_KEY: z.string().optional(),
     BETTER_AUTH_SECRET: z.string(),
     BETTER_AUTH_TRUSTED_ORIGINS: z
       .string()

cloud/docker-compose.yml

@@ -13,6 +13,7 @@ services:
       - .env
     restart: unless-stopped
     environment:
+      - KAN_ADMIN_API_KEY=${KAN_ADMIN_API_KEY}
       - NEXT_PUBLIC_KAN_ENV=${NEXT_PUBLIC_KAN_ENV}
       - NEXT_PUBLIC_BASE_URL=${NEXT_PUBLIC_BASE_URL}
       - BETTER_AUTH_SECRET=${BETTER_AUTH_SECRET}

docker-compose.yml

@@ -18,6 +18,9 @@ services:
       - BETTER_AUTH_SECRET=${BETTER_AUTH_SECRET}
       - POSTGRES_URL=${POSTGRES_URL}
 
+      # Admin API key (optional)
+      - KAN_ADMIN_API_KEY=${KAN_ADMIN_API_KEY}
+
       # SMTP (optional)
       - SMTP_HOST=${SMTP_HOST}
       - SMTP_PORT=${SMTP_PORT}

packages/api/src/openapi.ts

@@ -9,5 +9,15 @@ export const openApiDocument = generateOpenApiDocument(appRouter, {
   version: "1.0.0",
   baseUrl: `${env("NEXT_PUBLIC_BASE_URL")}/api/v1`,
   docsUrl: "docs.kan.bn",
-  tags: ["Auth", "Users", "Boards", "Lists", "Cards", "Labels", "Imports", "Integrations"],
+  tags: [
+    "Auth",
+    "Users",
+    "Boards",
+    "Lists",
+    "Cards",
+    "Labels",
+    "Imports",
+    "Integrations",
+    "Health",
+  ],
 });

packages/api/src/root.ts

@@ -3,6 +3,7 @@ import { boardRouter } from "./routers/board";
 import { cardRouter } from "./routers/card";
 import { checklistRouter } from "./routers/checklist";
 import { feedbackRouter } from "./routers/feedback";
+import { healthRouter } from "./routers/health";
 import { importRouter } from "./routers/import";
 import { integrationRouter } from "./routers/integration";
 import { labelRouter } from "./routers/label";
@@ -18,6 +19,7 @@ export const appRouter = createTRPCRouter({
   card: cardRouter,
   checklist: checklistRouter,
   feedback: feedbackRouter,
+  health: healthRouter,
   label: labelRouter,
   list: listRouter,
   member: memberRouter,

packages/api/src/routers/health.ts

@@ -0,0 +1,203 @@
+import { HeadBucketCommand } from "@aws-sdk/client-s3";
+import { TRPCError } from "@trpc/server";
+import { sql } from "drizzle-orm";
+import { env } from "next-runtime-env";
+import { z } from "zod";
+
+import type { dbClient } from "@kan/db/client";
+import * as boardRepo from "@kan/db/repository/board.repo";
+import * as cardRepo from "@kan/db/repository/card.repo";
+import * as cardActivityRepo from "@kan/db/repository/cardActivity.repo";
+import * as cardAttachmentRepo from "@kan/db/repository/cardAttachment.repo";
+import * as cardCommentRepo from "@kan/db/repository/cardComment.repo";
+import * as checklistRepo from "@kan/db/repository/checklist.repo";
+import * as importRepo from "@kan/db/repository/import.repo";
+import * as inviteLinkRepo from "@kan/db/repository/inviteLink.repo";
+import * as labelRepo from "@kan/db/repository/label.repo";
+import * as listRepo from "@kan/db/repository/list.repo";
+import * as memberRepo from "@kan/db/repository/member.repo";
+import * as userRepo from "@kan/db/repository/user.repo";
+import * as workspaceRepo from "@kan/db/repository/workspace.repo";
+
+import {
+  adminProtectedProcedure,
+  createTRPCRouter,
+  publicProcedure,
+} from "../trpc";
+import { createS3Client } from "../utils/s3";
+
+const checkDatabaseConnection = async (db: dbClient) => {
+  try {
+    await db.execute(sql`SELECT 1`);
+    return true;
+  } catch {
+    return false;
+  }
+};
+
+const checkS3Connection = async () => {
+  try {
+    // Check if S3 is configured
+    if (
+      !process.env.S3_ENDPOINT ||
+      !process.env.S3_ACCESS_KEY_ID ||
+      !process.env.S3_SECRET_ACCESS_KEY
+    ) {
+      // S3 is optional, so return true if not configured
+      return true;
+    }
+
+    const client = createS3Client();
+    const avatarBucketName = env("NEXT_PUBLIC_AVATAR_BUCKET_NAME");
+    const attachmentsBucketName = env("NEXT_PUBLIC_ATTACHMENTS_BUCKET_NAME");
+
+    await client.send(new HeadBucketCommand({ Bucket: avatarBucketName }));
+    await client.send(new HeadBucketCommand({ Bucket: attachmentsBucketName }));
+
+    return true;
+  } catch (error) {
+    console.error(error);
+    return false;
+  }
+};
+
+export const healthRouter = createTRPCRouter({
+  health: publicProcedure
+    .meta({
+      openapi: {
+        method: "GET",
+        path: "/health",
+        summary: "Health check",
+        description:
+          "Returns the health status of the application and its dependencies",
+        tags: ["Health"],
+        protect: false,
+      },
+    })
+    .input(z.void())
+    .output(
+      z.object({
+        status: z.enum(["ok", "error"]),
+        database: z.enum(["ok", "error"]).optional(),
+        storage: z.enum(["ok", "error", "not_configured"]).optional(),
+      }),
+    )
+    .query(async ({ ctx }) => {
+      const dbHealthy = await checkDatabaseConnection(ctx.db);
+      const s3Healthy = await checkS3Connection();
+      const s3Configured = !!(
+        process.env.S3_ENDPOINT &&
+        process.env.S3_ACCESS_KEY_ID &&
+        process.env.S3_SECRET_ACCESS_KEY
+      );
+
+      const database = dbHealthy ? "ok" : "error";
+      const storage = !s3Configured
+        ? "not_configured"
+        : s3Healthy
+          ? "ok"
+          : "error";
+
+      // Overall status is "ok" only if database is healthy and (storage is not configured or storage is healthy)
+      const status = dbHealthy && (!s3Configured || s3Healthy) ? "ok" : "error";
+
+      return {
+        status,
+        database,
+        storage,
+      };
+    }),
+  stats: adminProtectedProcedure
+    .meta({
+      openapi: {
+        method: "GET",
+        path: "/stats",
+        summary: "Get statistics",
+        description:
+          "Returns statistics about the application (workspaces, users, cards, etc.)",
+        tags: ["Health"],
+        protect: true,
+      },
+    })
+    .input(z.void())
+    .output(
+      z.object({
+        users: z.number(),
+        workspaces: z.number(),
+        boards: z.number(),
+        lists: z.number(),
+        cards: z.number(),
+        cardComments: z.number(),
+        cardAttachments: z.number(),
+        cardActivityLogs: z.number(),
+        labels: z.number(),
+        checklists: z.number(),
+        checklistItems: z.number(),
+        activeMembers: z.number(),
+        activeInviteLinks: z.number(),
+        imports: z.number(),
+      }),
+    )
+    .query(async ({ ctx }) => {
+      try {
+        // Execute all count queries in parallel
+        const [
+          usersCount,
+          workspacesCount,
+          boardsCount,
+          listsCount,
+          cardsCount,
+          cardCommentsCount,
+          checklistItemsCount,
+          checklistsCount,
+          labelsCount,
+          cardAttachmentsCount,
+          cardActivitiesCount,
+          activeMembersCount,
+          activeInviteLinksCount,
+          importsCount,
+          cardActivityLogsCount,
+        ] = await Promise.all([
+          userRepo.getCount(ctx.db),
+          workspaceRepo.getCount(ctx.db),
+          boardRepo.getCount(ctx.db),
+          listRepo.getCount(ctx.db),
+          cardRepo.getCount(ctx.db),
+          cardCommentRepo.getCount(ctx.db),
+          checklistRepo.getCountItems(ctx.db),
+          checklistRepo.getCount(ctx.db),
+          labelRepo.getCount(ctx.db),
+          cardAttachmentRepo.getCount(ctx.db),
+          cardActivityRepo.getCount(ctx.db),
+          memberRepo.getActiveCount(ctx.db),
+          inviteLinkRepo.getActiveCount(ctx.db),
+          importRepo.getCount(ctx.db),
+          memberRepo.getActiveCount(ctx.db),
+          cardActivityRepo.getCount(ctx.db),
+        ]);
+
+        return {
+          users: usersCount,
+          workspaces: workspacesCount,
+          boards: boardsCount,
+          lists: listsCount,
+          cards: cardsCount,
+          cardComments: cardCommentsCount,
+          checklistItems: checklistItemsCount,
+          checklists: checklistsCount,
+          labels: labelsCount,
+          cardAttachments: cardAttachmentsCount,
+          cardActivities: cardActivitiesCount,
+          activeMembers: activeMembersCount,
+          activeInviteLinks: activeInviteLinksCount,
+          imports: importsCount,
+          cardActivityLogs: cardActivityLogsCount,
+        };
+      } catch (error) {
+        throw new TRPCError({
+          message: `Failed to retrieve statistics: ${error instanceof Error ? error.message : "Unknown error"}`,
+          code: "INTERNAL_SERVER_ERROR",
+        });
+      }
+    }),
+});

packages/api/src/trpc.ts

@@ -2,6 +2,7 @@ import type { CreateNextContextOptions } from "@trpc/server/adapters/next";
 import type { NextApiRequest } from "next";
 import type { OpenApiMeta } from "trpc-to-openapi";
 import { initTRPC, TRPCError } from "@trpc/server";
+import { env } from "next-runtime-env";
 import superjson from "superjson";
 import { ZodError } from "zod";
 
@@ -45,6 +46,7 @@ interface CreateContextOptions {
   user: User | null | undefined;
   db: dbClient;
   auth: ReturnType<typeof createAuthWithHeaders>;
+  headers: Headers;
 }
 
 export const createInnerTRPCContext = (opts: CreateContextOptions) => {
@@ -52,6 +54,7 @@ export const createInnerTRPCContext = (opts: CreateContextOptions) => {
     user: opts.user,
     db: opts.db,
     auth: opts.auth,
+    headers: opts.headers,
   };
 };
 
@@ -63,7 +66,7 @@ export const createTRPCContext = async ({ req }: CreateNextContextOptions) => {
 
   const session = await auth.api.getSession();
 
-  return createInnerTRPCContext({ db, user: session?.user, auth });
+  return createInnerTRPCContext({ db, user: session?.user, auth, headers });
 };
 
 export const createNextApiContext = async (req: NextApiRequest) => {
@@ -74,7 +77,7 @@ export const createNextApiContext = async (req: NextApiRequest) => {
 
   const session = await auth.api.getSession();
 
-  return createInnerTRPCContext({ db, user: session?.user, auth });
+  return createInnerTRPCContext({ db, user: session?.user, auth, headers });
 };
 
 export const createRESTContext = async ({ req }: CreateNextContextOptions) => {
@@ -91,7 +94,7 @@ export const createRESTContext = async ({ req }: CreateNextContextOptions) => {
     throw error;
   }
 
-  return createInnerTRPCContext({ db, user: session?.user, auth });
+  return createInnerTRPCContext({ db, user: session?.user, auth, headers });
 };
 
 const t = initTRPC
@@ -129,9 +132,28 @@ const enforceUserIsAuthed = t.middleware(async ({ ctx, next }) => {
   });
 });
 
+const enforceUserIsAdmin = t.middleware(async ({ ctx, next }) => {
+  if (ctx.headers.get("x-admin-api-key") !== env("KAN_ADMIN_API_KEY")) {
+    throw new TRPCError({ code: "UNAUTHORIZED" });
+  }
+
+  return next({
+    ctx,
+  });
+});
+
 export const protectedProcedure = t.procedure.use(enforceUserIsAuthed).meta({
   openapi: {
     method: "GET",
     path: "/protected",
   },
 });
+
+export const adminProtectedProcedure = t.procedure
+  .use(enforceUserIsAdmin)
+  .meta({
+    openapi: {
+      method: "GET",
+      path: "/admin/protected",
+    },
+  });

packages/db/src/repository/board.repo.ts

@@ -1,6 +1,7 @@
 import {
   and,
   asc,
+  count,
   desc,
   eq,
   gte,
@@ -29,6 +30,15 @@ import {
 } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(boards)
+    .where(isNull(boards.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const getAllByWorkspaceId = (
   db: dbClient,
   workspaceId: number,

packages/db/src/repository/card.repo.ts

@@ -1,4 +1,14 @@
-import { and, asc, desc, eq, gt, inArray, isNull, lt, sql } from "drizzle-orm";
+import {
+  and,
+  asc,
+  count,
+  desc,
+  eq,
+  gt,
+  inArray,
+  isNull,
+  sql,
+} from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import {
@@ -9,13 +19,21 @@ import {
   cardToWorkspaceMembers,
   checklistItems,
   checklists,
-  comments,
   labels,
   lists,
   workspaceMembers,
 } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(cards)
+    .where(isNull(cards.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   cardInput: {

packages/db/src/repository/cardActivity.repo.ts

@@ -1,10 +1,16 @@
-import { and, asc, eq, gt, inArray, isNull, or } from "drizzle-orm";
+import { and, asc, count, eq, gt, inArray, isNull, or } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import type { ActivityType } from "@kan/db/schema";
 import { cardActivities, comments } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db.select({ count: count() }).from(cardActivities);
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   activityInput: {

packages/db/src/repository/cardAttachment.repo.ts

@@ -1,9 +1,18 @@
-import { and, eq, isNull } from "drizzle-orm";
+import { and, count, eq, isNull } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { cardAttachments } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(cardAttachments)
+    .where(isNull(cardAttachments.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   attachmentInput: {

packages/db/src/repository/cardComment.repo.ts

@@ -1,9 +1,18 @@
-import { eq } from "drizzle-orm";
+import { count, eq, isNull } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { comments } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(comments)
+    .where(isNull(comments.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   commentInput: {

packages/db/src/repository/checklist.repo.ts

@@ -1,9 +1,24 @@
-import { and, desc, eq, isNull, sql } from "drizzle-orm";
+import { and, count, desc, eq, isNull, sql } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { checklistItems, checklists } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(checklists)
+    .where(isNull(checklists.deletedAt));
+  return result[0]?.count ?? 0;
+};
+
+export const getCountItems = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(checklistItems)
+    .where(isNull(checklistItems.deletedAt));
+  return result[0]?.count ?? 0;
+};
 
 export const create = async (
   db: dbClient,
@@ -357,7 +372,10 @@ export const reorderItem = async (
         index: true,
         checklistId: true,
       },
-      where: and(eq(checklistItems.id, args.itemId), isNull(checklistItems.deletedAt)),
+      where: and(
+        eq(checklistItems.id, args.itemId),
+        isNull(checklistItems.deletedAt),
+      ),
     });
 
     if (!item) {
@@ -374,7 +392,10 @@ export const reorderItem = async (
           title: true,
           completed: true,
         },
-        where: and(eq(checklistItems.id, args.itemId), isNull(checklistItems.deletedAt)),
+        where: and(
+          eq(checklistItems.id, args.itemId),
+          isNull(checklistItems.deletedAt),
+        ),
       });
 
       if (!unchanged) {
@@ -418,7 +439,6 @@ export const reorderItem = async (
       throw new Error(`Failed to update checklist item with ID ${args.itemId}`);
     }
 
-      return updated;
-    });
-  }
-
+    return updated;
+  });
+};

packages/db/src/repository/import.repo.ts

@@ -1,9 +1,15 @@
-import { eq } from "drizzle-orm";
+import { count, eq } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { imports } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db.select({ count: count() }).from(imports);
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   importInput: { source: string; createdBy: string },

packages/db/src/repository/inviteLink.repo.ts

@@ -1,9 +1,18 @@
-import { and, eq, gt } from "drizzle-orm";
+import { and, count, eq } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { workspaceInviteLinks } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getActiveCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(workspaceInviteLinks)
+    .where(eq(workspaceInviteLinks.status, "active"));
+
+  return result[0]?.count ?? 0;
+};
+
 export const createInviteLink = async (
   db: dbClient,
   args: {

packages/db/src/repository/label.repo.ts

@@ -1,9 +1,18 @@
-import { and, eq, inArray, isNull } from "drizzle-orm";
+import { and, count, eq, inArray, isNull } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { cardsToLabels, labels } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(labels)
+    .where(isNull(labels.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   labelInput: {

packages/db/src/repository/list.repo.ts

@@ -1,9 +1,18 @@
-import { and, desc, eq, gt, isNull, sql } from "drizzle-orm";
+import { and, count, desc, eq, gt, isNull, sql } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import { lists } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(lists)
+    .where(isNull(lists.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   listInput: {

packages/db/src/repository/member.repo.ts

@@ -1,10 +1,24 @@
-import { and, eq, isNull } from "drizzle-orm";
+import { and, count, eq, isNull } from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import type { MemberRole, MemberStatus } from "@kan/db/schema";
 import { workspaceMembers } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getActiveCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(workspaceMembers)
+    .where(
+      and(
+        isNull(workspaceMembers.deletedAt),
+        eq(workspaceMembers.status, "active"),
+      ),
+    );
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   memberInput: {

packages/db/src/repository/user.repo.ts

@@ -1,9 +1,15 @@
-import { desc, eq } from "drizzle-orm";
+import { count, desc, eq } from "drizzle-orm";
 import { v4 as uuidv4 } from "uuid";
 
 import type { dbClient } from "@kan/db/client";
 import { apikey, users } from "@kan/db/schema";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db.select({ count: count() }).from(users);
+
+  return result[0]?.count ?? 0;
+};
+
 export const getById = async (db: dbClient, userId: string) => {
   return await db.query.users.findFirst({
     columns: {

packages/db/src/repository/workspace.repo.ts

@@ -1,4 +1,14 @@
-import { and, desc, eq, ilike, inArray, isNull, or, sql } from "drizzle-orm";
+import {
+  and,
+  count,
+  desc,
+  eq,
+  ilike,
+  inArray,
+  isNull,
+  or,
+  sql,
+} from "drizzle-orm";
 
 import type { dbClient } from "@kan/db/client";
 import {
@@ -10,6 +20,15 @@ import {
 } from "@kan/db/schema";
 import { generateUID } from "@kan/shared/utils";
 
+export const getCount = async (db: dbClient) => {
+  const result = await db
+    .select({ count: count() })
+    .from(workspaces)
+    .where(isNull(workspaces.deletedAt));
+
+  return result[0]?.count ?? 0;
+};
+
 export const create = async (
   db: dbClient,
   workspaceInput: {

turbo.json

@@ -46,6 +46,7 @@
     }
   },
   "globalEnv": [
+    "KAN_ADMIN_API_KEY",
     "POSTGRES_URL",
     "TRELLO_APP_API_KEY",
     "TRELLO_APP_SECRET",