test: guard integration database cleanup

apps/server/src/core/page/services/trash-cleanup.service.ts

@@ -5,7 +5,10 @@ import { KyselyDB } from '@docmost/db/types/kysely.types';
 import { InjectQueue } from '@nestjs/bullmq';
 import { Queue } from 'bullmq';
 import { QueueJob, QueueName } from '../../../integrations/queue/constants';
-import { assertAcyclicPageTraversal } from '../../../database/helpers/page-hierarchy-cycle';
+import {
+  assertAcyclicPageTraversal,
+  PageHierarchyCycleError,
+} from '../../../database/helpers/page-hierarchy-cycle';
 import { sql } from 'kysely';
 
 const DEFAULT_RETENTION_DAYS = 30;
@@ -44,11 +47,33 @@ export class TrashCleanupService {
           .select(['id'])
           .where('workspaceId', '=', workspace.id)
           .where('deletedAt', '<', retentionDate)
+          .orderBy('id')
           .execute();
 
         for (const page of oldDeletedPages) {
+          let pageIds: string[];
+
           try {
-            totalCleaned += await this.cleanupPage(page.id);
+            const ancestors = await this.getPageAncestors(page.id);
+            assertAcyclicPageTraversal(ancestors, page.id);
+
+            const descendants = await this.getPageDescendants(page.id);
+            assertAcyclicPageTraversal(descendants, page.id);
+            pageIds = descendants.map((descendant) => descendant.id);
+          } catch (error) {
+            if (error instanceof PageHierarchyCycleError) {
+              this.logCleanupError(page.id, error);
+              continue;
+            }
+            throw error;
+          }
+
+          if (pageIds.length === 0) {
+            continue;
+          }
+
+          try {
+            totalCleaned += await this.cleanupPage(page.id, pageIds);
           } catch (error) {
             this.logCleanupError(page.id, error);
           }
@@ -68,6 +93,36 @@ export class TrashCleanupService {
     }
   }
 
+  private async getPageAncestors(pageId: string) {
+    return this.db
+      .withRecursive('page_ancestors', (db) =>
+        db
+          .selectFrom('pages')
+          .select([
+            'id',
+            'parentPageId',
+            sql<string[]>`ARRAY[id]::uuid[]`.as('traversalPath'),
+            sql<boolean>`false`.as('isCycle'),
+          ])
+          .where('id', '=', pageId)
+          .unionAll((exp) =>
+            exp
+              .selectFrom('pages as p')
+              .select([
+                'p.id',
+                'p.parentPageId',
+                sql<string[]>`pa.traversal_path || p.id`.as('traversalPath'),
+                sql<boolean>`p.id = ANY(pa.traversal_path)`.as('isCycle'),
+              ])
+              .innerJoin('page_ancestors as pa', 'pa.parentPageId', 'p.id')
+              .where('pa.isCycle', '=', false),
+          ),
+      )
+      .selectFrom('page_ancestors')
+      .select(['id', 'isCycle'])
+      .execute();
+  }
+
   private async getPageDescendants(pageId: string) {
     // Get all descendants using recursive CTE (including the page itself)
     return this.db
@@ -97,15 +152,7 @@ export class TrashCleanupService {
       .execute();
   }
 
-  private async cleanupPage(pageId: string) {
-    const descendants = await this.getPageDescendants(pageId);
-    assertAcyclicPageTraversal(descendants, pageId);
-
-    const pageIds = descendants.map((descendant) => descendant.id);
-    if (pageIds.length === 0) {
-      return 0;
-    }
-
+  private async cleanupPage(pageId: string, pageIds: string[]) {
     this.logger.debug(
       `Cleaning up page ${pageId} with ${pageIds.length - 1} descendants`,
     );

apps/server/src/database/repos/page/page-permission.repo.ts

@@ -728,7 +728,48 @@ export class PagePermissionRepo {
     if (spaceId) {
       const hasRestrictions = await this.hasRestrictedPagesInSpace(spaceId);
       if (!hasRestrictions) {
-        return pageIds;
+        const cyclicPages = await this.db
+          .withRecursive('allAncestors', (qb) =>
+            qb
+              .selectFrom('pages')
+              .select([
+                'pages.id as pageId',
+                'pages.id as ancestorId',
+                'pages.parentPageId',
+                sql<string[]>`ARRAY[pages.id]::uuid[]`.as('traversalPath'),
+                sql<boolean>`false`.as('isCycle'),
+              ])
+              .where(sql<SqlBool>`pages.id = ANY(${pageIds}::uuid[])`)
+              .unionAll((eb) =>
+                eb
+                  .selectFrom('pages')
+                  .innerJoin(
+                    'allAncestors',
+                    'allAncestors.parentPageId',
+                    'pages.id',
+                  )
+                  .select([
+                    'allAncestors.pageId',
+                    'pages.id as ancestorId',
+                    'pages.parentPageId',
+                    sql<string[]>`all_ancestors.traversal_path || pages.id`.as(
+                      'traversalPath',
+                    ),
+                    sql<boolean>`pages.id = ANY(all_ancestors.traversal_path)`.as(
+                      'isCycle',
+                    ),
+                  ])
+                  .where('allAncestors.isCycle', '=', false),
+              ),
+          )
+          .selectFrom('allAncestors')
+          .select('allAncestors.pageId')
+          .distinct()
+          .where('allAncestors.isCycle', '=', true)
+          .execute();
+        const cyclicPageIds = new Set(cyclicPages.map((page) => page.pageId));
+
+        return pageIds.filter((pageId) => !cyclicPageIds.has(pageId));
       }
     }
 

apps/server/test/docker-compose.integration.yml

@@ -0,0 +1,18 @@
+name: docmost-cycle-test
+
+services:
+  postgres:
+    image: postgres:18
+    environment:
+      POSTGRES_DB: docmost_cycle_test
+      POSTGRES_USER: docmost
+      POSTGRES_PASSWORD: docmost
+    ports:
+      - '127.0.0.1:55432:5432'
+    tmpfs:
+      - /var/lib/postgresql
+    healthcheck:
+      test: ['CMD-SHELL', 'pg_isready -U docmost -d docmost_cycle_test']
+      interval: 2s
+      timeout: 2s
+      retries: 15

apps/server/test/jest-integration.json

@@ -0,0 +1,26 @@
+{
+  "moduleFileExtensions": ["js", "json", "ts"],
+  "rootDir": ".",
+  "testEnvironment": "node",
+  "testRegex": ".*\\.integration-spec\\.ts$",
+  "transform": {
+    "happy-dom.+\\.js$": [
+      "babel-jest",
+      {
+        "presets": [["@babel/preset-env", { "targets": { "node": "current" } }]]
+      }
+    ],
+    "^.+\\.(t|j)s$": "ts-jest"
+  },
+  "transformIgnorePatterns": [
+    "/node_modules/(?!(\\.pnpm/)?(nanoid|uuid|image-dimensions|marked|happy-dom)(@|/))"
+  ],
+  "moduleNameMapper": {
+    "^@docmost/db/(.*)$": "<rootDir>/../src/database/$1",
+    "^@docmost/transactional/(.*)$": "<rootDir>/../src/integrations/transactional/$1",
+    "^@docmost/ee/(.*)$": "<rootDir>/../src/ee/$1",
+    "^src/(.*)$": "<rootDir>/../src/$1",
+    "^@docmost/base-formula/server$": "<rootDir>/../../../packages/base-formula/src/index.server.ts",
+    "^@docmost/base-formula/client$": "<rootDir>/../../../packages/base-formula/src/index.client.ts"
+  }
+}

apps/server/test/support/database.ts

@@ -0,0 +1,90 @@
+import { CamelCasePlugin, Kysely, sql } from 'kysely';
+import { PostgresJSDialect } from 'kysely-postgres-js';
+import * as postgres from 'postgres';
+import { DbInterface } from '../../src/database/types/db.interface';
+
+const disposableDatabaseName = 'docmost_cycle_test';
+const databaseUrl = process.env.TEST_DATABASE_URL;
+
+if (!databaseUrl) {
+  throw new Error('TEST_DATABASE_URL must be set for integration tests');
+}
+
+export function assertDisposableTestDatabaseUrl(value: string): void {
+  let parsedUrl: URL;
+
+  try {
+    parsedUrl = new URL(value);
+  } catch {
+    throw new Error('TEST_DATABASE_URL must be a valid PostgreSQL URL');
+  }
+
+  const isPostgresUrl = ['postgres:', 'postgresql:'].includes(
+    parsedUrl.protocol,
+  );
+  const isLoopback = ['127.0.0.1', 'localhost', '[::1]'].includes(
+    parsedUrl.hostname,
+  );
+  const databaseName = decodeURIComponent(parsedUrl.pathname.slice(1));
+
+  if (!isPostgresUrl || !isLoopback || databaseName !== disposableDatabaseName) {
+    throw new Error(
+      `Integration tests require the loopback database ${disposableDatabaseName}`,
+    );
+  }
+}
+
+assertDisposableTestDatabaseUrl(databaseUrl);
+
+const postgresPool = postgres(databaseUrl, { max: 1, onnotice: () => {} });
+
+export const db = new Kysely<DbInterface>({
+  dialect: new PostgresJSDialect({ postgres: postgresPool }),
+  plugins: [new CamelCasePlugin()],
+});
+
+let databaseSafetyVerified = false;
+
+beforeAll(async () => {
+  const result = await sql<{ databaseName: string }>`
+    SELECT current_database() AS "databaseName"
+  `.execute(db);
+
+  if (result.rows[0]?.databaseName !== disposableDatabaseName) {
+    throw new Error(
+      `Connected database must be the disposable ${disposableDatabaseName} database`,
+    );
+  }
+
+  databaseSafetyVerified = true;
+});
+
+export async function withStatementTimeout<T>(
+  callback: (connection: Kysely<DbInterface>) => Promise<T>,
+): Promise<T> {
+  return db.connection().execute(async (connection) => {
+    await sql`SET statement_timeout = '500ms'`.execute(connection);
+
+    try {
+      return await callback(connection);
+    } finally {
+      await sql`SET statement_timeout = DEFAULT`.execute(connection);
+    }
+  });
+}
+
+export async function truncateFixtureTables(): Promise<void> {
+  if (!databaseSafetyVerified) {
+    throw new Error('Refusing to truncate an unverified integration database');
+  }
+
+  await sql`TRUNCATE TABLE pages, spaces, workspaces CASCADE`.execute(db);
+}
+
+afterEach(async () => {
+  await truncateFixtureTables();
+});
+
+afterAll(async () => {
+  await postgresPool.end();
+});

apps/server/test/support/page-hierarchy-fixtures.ts

@@ -0,0 +1,114 @@
+import { randomUUID } from 'node:crypto';
+import { sql } from 'kysely';
+import { db } from './database';
+
+export type PageFixture = {
+  id: string;
+  parentPageId: string | null;
+  title: string;
+};
+
+type HierarchyContext = {
+  workspaceId: string;
+  spaceId: string;
+};
+
+async function seedWorkspaceAndSpace(): Promise<HierarchyContext> {
+  const workspace = await db
+    .insertInto('workspaces')
+    .values({
+      hostname: `cycle-test-${randomUUID()}`,
+      name: 'Page hierarchy integration workspace',
+    })
+    .returning('id')
+    .executeTakeFirstOrThrow();
+
+  const space = await db
+    .insertInto('spaces')
+    .values({
+      name: 'Page hierarchy integration space',
+      slug: `cycle-test-${randomUUID()}`,
+      workspaceId: workspace.id,
+    })
+    .returning('id')
+    .executeTakeFirstOrThrow();
+
+  return { spaceId: space.id, workspaceId: workspace.id };
+}
+
+async function insertPage(
+  context: HierarchyContext,
+  title: string,
+  parentPageId: string | null = null,
+): Promise<PageFixture> {
+  const page = await db
+    .insertInto('pages')
+    .values({
+      parentPageId,
+      slugId: randomUUID(),
+      spaceId: context.spaceId,
+      title,
+      workspaceId: context.workspaceId,
+    })
+    .returning(['id', 'parentPageId', 'title'])
+    .executeTakeFirstOrThrow();
+
+  return { id: page.id, parentPageId: page.parentPageId, title };
+}
+
+export async function seedAcyclicPageChain(): Promise<{
+  root: PageFixture;
+  child: PageFixture;
+  grandchild: PageFixture;
+}> {
+  const context = await seedWorkspaceAndSpace();
+  const root = await insertPage(context, 'Root');
+  const child = await insertPage(context, 'Child', root.id);
+  const grandchild = await insertPage(context, 'Grandchild', child.id);
+
+  return { root, child, grandchild };
+}
+
+export async function seedSelfCycle(): Promise<{ self: PageFixture }> {
+  const context = await seedWorkspaceAndSpace();
+  const self = await insertPage(context, 'Self');
+
+  await sql`UPDATE pages SET parent_page_id = ${self.id} WHERE id = ${self.id}`.execute(
+    db,
+  );
+
+  return { self: { ...self, parentPageId: self.id } };
+}
+
+export async function seedTwoPageCycle(): Promise<{
+  a: PageFixture;
+  b: PageFixture;
+}> {
+  const context = await seedWorkspaceAndSpace();
+  const a = await insertPage(context, 'A');
+  const b = await insertPage(context, 'B', a.id);
+
+  await sql`UPDATE pages SET parent_page_id = ${b.id} WHERE id = ${a.id}`.execute(
+    db,
+  );
+
+  return {
+    a: { ...a, parentPageId: b.id },
+    b,
+  };
+}
+
+export async function seedBranchingDescendantTree(): Promise<{
+  root: PageFixture;
+  firstChild: PageFixture;
+  secondChild: PageFixture;
+  grandchild: PageFixture;
+}> {
+  const context = await seedWorkspaceAndSpace();
+  const root = await insertPage(context, 'Root');
+  const firstChild = await insertPage(context, 'First child', root.id);
+  const secondChild = await insertPage(context, 'Second child', root.id);
+  const grandchild = await insertPage(context, 'Grandchild', firstChild.id);
+
+  return { root, firstChild, secondChild, grandchild };
+}