Skip to content

📈 Open source, privacy-first web analytics — a cookie-less Google Analytics alternative with built-in error tracking and performance monitoring. Self-host it or use our cloud. Get started for free!

9.9 KB
Raw Blame
import {
  Injectable,
  BadRequestException,
  NotFoundException,
  Logger,
} from '@nestjs/common'
import { randomUUID } from 'crypto'

import {
  DataImport,
  DataImportStatus,
  MAX_IMPORT_ID,
} from './entity/data-import.entity'
import { clickhouse } from '../common/integrations/clickhouse'
import { redis } from '../common/constants'

const CLICKHOUSE_DB = process.env.CLICKHOUSE_DATABASE || 'analytics'
const IMPORT_IN_PROGRESS_ERROR =
  'An import is already in progress for this project. Please wait for it to complete before starting a new one.'
const IMPORT_CREATE_LOCK_TTL_MS = 10_000
const IMPORT_CREATE_LOCK_WAIT_MS = 5_000
const IMPORT_CREATE_LOCK_RETRY_MS = 100
const CLICKHOUSE_DATE_TIME_REGEX = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/
const ISO_DATE_TIME_REGEX =
  /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d+)?(?:Z|[+-]\d{2}:\d{2})?$/

function formatClickHouseDateTime(value: Date | string): string {
  if (value instanceof Date) {
    return value.toISOString().replace('T', ' ').replace('Z', '').slice(0, 19)
  }

  if (CLICKHOUSE_DATE_TIME_REGEX.test(value)) {
    return value
  }

  if (ISO_DATE_TIME_REGEX.test(value)) {
    const date = new Date(value)
    if (!Number.isNaN(date.getTime())) {
      return date.toISOString().replace('T', ' ').replace('Z', '').slice(0, 19)
    }
  }

  throw new Error('Invalid datetime input')
}

function formatNullableClickHouseDateTime(
  value: Date | string | null | undefined,
): string | null {
  if (!value) {
    return null
  }

  return formatClickHouseDateTime(value)
}

@Injectable()
export class DataImportService {
  private readonly logger = new Logger(DataImportService.name)

  private getCreateLockKey(projectId: string): string {
    return `data-import:create:${projectId}`
  }

  private async acquireCreateLock(projectId: string): Promise<string> {
    const token = randomUUID()
    const lockKey = this.getCreateLockKey(projectId)
    const deadline = Date.now() + IMPORT_CREATE_LOCK_WAIT_MS

    while (Date.now() < deadline) {
      const acquired = await redis.set(
        lockKey,
        token,
        'PX',
        IMPORT_CREATE_LOCK_TTL_MS,
        'NX',
      )

      if (acquired === 'OK') {
        return token
      }

      await new Promise((resolve) =>
        setTimeout(resolve, IMPORT_CREATE_LOCK_RETRY_MS),
      )
    }

    throw new BadRequestException(IMPORT_IN_PROGRESS_ERROR)
  }

  private async releaseCreateLock(
    projectId: string,
    token: string,
  ): Promise<void> {
    await redis.eval(
      `if redis.call("GET", KEYS[1]) == ARGV[1] then
         return redis.call("DEL", KEYS[1])
       end
       return 0`,
      1,
      this.getCreateLockKey(projectId),
      token,
    )
  }

  private async getNextId(projectId: string): Promise<number> {
    const result = await clickhouse
      .query({
        query: `SELECT id FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} ORDER BY id ASC`,
        query_params: { projectId },
      })
      .then((rs) => rs.json<{ id: number | string }>())

    let next = 1

    for (const row of result.data || []) {
      const currentId = Number(row.id)

      if (currentId < next) {
        continue
      }

      if (currentId === next) {
        next += 1
        continue
      }

      break
    }

    if (next > MAX_IMPORT_ID) {
      throw new BadRequestException(
        `This project has reached the maximum number of imports (${MAX_IMPORT_ID}). Please delete an old import before creating a new one.`,
      )
    }

    return next
  }

  async create(projectId: string, provider: string): Promise<DataImport> {
    const lockToken = await this.acquireCreateLock(projectId)

    try {
      const active = await this.findActiveByProject(projectId)
      if (active) {
        throw new BadRequestException(IMPORT_IN_PROGRESS_ERROR)
      }

      const id = await this.getNextId(projectId)

      const now = formatClickHouseDateTime(new Date())

      await clickhouse.insert({
        table: `${CLICKHOUSE_DB}.data_import`,
        values: [
          {
            id,
            projectId,
            provider,
            status: 'pending',
            dateFrom: null,
            dateTo: null,
            totalRows: 0,
            importedRows: 0,
            invalidRows: 0,
            errorMessage: null,
            createdAt: now,
            finishedAt: null,
            version: 1,
          },
        ],
        format: 'JSONEachRow',
      })

      return {
        id,
        projectId,
        provider,
        status: DataImportStatus.PENDING,
        dateFrom: null,
        dateTo: null,
        totalRows: 0,
        importedRows: 0,
        invalidRows: 0,
        errorMessage: null,
        createdAt: now,
        finishedAt: null,
        version: 1,
      }
    } finally {
      await this.releaseCreateLock(projectId, lockToken)
    }
  }

  private async findActiveByProject(
    projectId: string,
  ): Promise<DataImport | null> {
    const result = await clickhouse
      .query({
        query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} AND status IN ('pending', 'processing') LIMIT 1`,
        query_params: { projectId },
      })
      .then((rs) => rs.json<DataImport>())

    return result.data?.[0] || null
  }

  async findAllByProject(projectId: string): Promise<DataImport[]> {
    const result = await clickhouse
      .query({
        query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} ORDER BY createdAt DESC`,
        query_params: { projectId },
      })
      .then((rs) => rs.json<DataImport>())

    return result.data || []
  }

  async findOne(projectId: string, importId: number): Promise<DataImport> {
    const result = await clickhouse
      .query({
        query: `SELECT * FROM ${CLICKHOUSE_DB}.data_import FINAL WHERE projectId = {projectId:FixedString(12)} AND id = {id:UInt8} LIMIT 1`,
        query_params: { projectId, id: importId },
      })
      .then((rs) => rs.json<DataImport>())

    const dataImport = result.data?.[0]
    if (!dataImport) {
      throw new NotFoundException('Import not found')
    }

    return dataImport
  }

  async updateStatus(
    id: number,
    projectId: string,
    updates: Partial<DataImport>,
  ): Promise<void> {
    const existing = await this.findOne(projectId, id)
    const nextVersion = (existing.version || 0) + 1

    await clickhouse.insert({
      table: `${CLICKHOUSE_DB}.data_import`,
      values: [
        {
          ...existing,
          ...updates,
          id,
          projectId,
          createdAt: formatClickHouseDateTime(
            updates.createdAt ?? existing.createdAt,
          ),
          finishedAt: formatNullableClickHouseDateTime(
            updates.finishedAt ?? existing.finishedAt,
          ),
          version: nextVersion,
        },
      ],
      format: 'JSONEachRow',
    })
  }

  async markProcessing(id: number, projectId: string): Promise<void> {
    await this.updateStatus(id, projectId, {
      status: DataImportStatus.PROCESSING,
    })
  }

  async markCompleted(
    id: number,
    projectId: string,
    stats: {
      importedRows: number
      invalidRows: number
      totalRows: number
      dateFrom: string | null
      dateTo: string | null
    },
  ): Promise<void> {
    const now = formatClickHouseDateTime(new Date())
    await this.updateStatus(id, projectId, {
      status: DataImportStatus.COMPLETED,
      importedRows: stats.importedRows,
      invalidRows: stats.invalidRows,
      totalRows: stats.totalRows,
      dateFrom: stats.dateFrom,
      dateTo: stats.dateTo,
      finishedAt: now,
    })
  }

  async markFailed(
    id: number,
    projectId: string,
    errorMessage: string,
  ): Promise<void> {
    const now = formatClickHouseDateTime(new Date())
    await this.updateStatus(id, projectId, {
      status: DataImportStatus.FAILED,
      errorMessage,
      finishedAt: now,
    })
  }

  async cleanupImportedRows(
    projectId: string,
    importId: number,
  ): Promise<void> {
    await clickhouse.command({
      query: `ALTER TABLE ${CLICKHOUSE_DB}.events DELETE WHERE pid = {pid:FixedString(12)} AND importID = {importID:UInt8} AND type IN ('pageview', 'custom_event', 'outbound')`,
      query_params: { pid: projectId, importID: importId },
      clickhouse_settings: { mutations_sync: '2' },
    })
  }

  async deleteImport(projectId: string, importId: number): Promise<void> {
    const dataImport = await this.findOne(projectId, importId)

    if (
      dataImport.status === DataImportStatus.PENDING ||
      dataImport.status === DataImportStatus.PROCESSING
    ) {
      throw new BadRequestException(
        'Cannot delete an import that is still in progress.',
      )
    }

    try {
      await this.cleanupImportedRows(projectId, importId)
    } catch (error) {
      this.logger.error(
        `Failed to delete imported data from ClickHouse for import ${importId}: ${error.message}`,
      )
      throw new BadRequestException(
        'Failed to remove imported data. Please try again later.',
      )
    }

    await this.deleteImportRecord(projectId, importId)
  }

  async deleteImportRecord(projectId: string, importId: number): Promise<void> {
    await clickhouse.command({
      query: `ALTER TABLE ${CLICKHOUSE_DB}.data_import DELETE WHERE projectId = {projectId:FixedString(12)} AND id = {id:UInt8}`,
      query_params: { projectId, id: importId },
    })
  }

  async hasImportedDataInRange(
    projectId: string,
    from: string,
    to: string,
  ): Promise<boolean> {
    const result = await clickhouse
      .query({
        query: `SELECT count() as cnt FROM ${CLICKHOUSE_DB}.events WHERE pid = {pid:FixedString(12)} AND type = 'pageview' AND importID IS NOT NULL AND created >= {from:DateTime} AND created <= {to:DateTime} LIMIT 1`,
        query_params: { pid: projectId, from, to },
      })
      .then((rs) => rs.json<{ cnt: string }>())

    return Number(result.data?.[0]?.cnt) > 0
  }
}