genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db$ cat pool.js
'use strict';

const { Pool } = require('pg');
const config = require('../config');

const dbConfig = config.db || {};
const loggerConfig = config.logger || {};
console.log('[db.pool] init', {
  applicationName: config.app?.name || 'pverse-auth',
  hasUrl: Boolean(dbConfig.url),
  databaseUrl: dbConfig.url,
  ssl: dbConfig.ssl || false,
  max: dbConfig.pool?.max ?? 20,
  min: dbConfig.pool?.min ?? 2,
});

const pool = new Pool({
  connectionString: dbConfig.url,
  max: dbConfig.pool?.max ?? 20,
  min: dbConfig.pool?.min ?? 2,
  idleTimeoutMillis: dbConfig.pool?.idleTimeoutMillis ?? 10000,
  connectionTimeoutMillis: dbConfig.pool?.connectionTimeoutMillis ?? 5000,
  statement_timeout: dbConfig.statementTimeoutMs ?? 15000,
  ssl: dbConfig.ssl || false,
  application_name: config.app?.name || 'pverse-auth',
});
console.log('[db.pool] init', {
  applicationName: config.app?.name || 'pverse-auth',
  hasUrl: Boolean(dbConfig.url),
  ssl: dbConfig.ssl || false,
  max: dbConfig.pool?.max ?? 20,
  min: dbConfig.pool?.min ?? 2,
});

pool.on('error', (error) => {
  if (loggerConfig.level === 'silent') return;
  console.error('[db.pool] unexpected pool error', { message: error.message, code: error.code || null, stack: config.isProduction ? undefined : error.stack });
});

async function query(text, params = []) { return pool.query(text, params); }
async function getClient() { return pool.connect(); }
async function closePool() { await pool.end(); }

module.exports = { pool, query, getClient, closePool };
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db$ cat transaction.js
'use strict';

const { getClient } = require('./pool');
const { withTransactionAdvisoryLock } = require('./helpers/advisory-lock');
const { ValidationError } = require('../lib/errors');

const ALLOWED_ISOLATION_LEVELS = new Set([
  'READ COMMITTED',
  'REPEATABLE READ',
  'SERIALIZABLE',
]);

function normalizeIsolationLevel(isolationLevel) {
  if (isolationLevel == null) return null;

  const normalized = String(isolationLevel).trim().toUpperCase();

  if (!ALLOWED_ISOLATION_LEVELS.has(normalized)) {
    throw new ValidationError('Invalid transaction isolation level', {
      code: 'TX_ISOLATION_LEVEL_INVALID',
      details: {
        isolationLevel,
        allowed: Array.from(ALLOWED_ISOLATION_LEVELS),
      },
    });
  }

  return normalized;
}

async function withTransaction(work, options = {}) {
  if (typeof work !== 'function') {
    throw new ValidationError('Transaction work must be a function', {
      code: 'TX_WORK_INVALID',
    });
  }

  const {
    isolationLevel = null,
    label = null,
  } = options;

  const normalizedIsolationLevel = normalizeIsolationLevel(isolationLevel);
  const client = await getClient();

  try {
    await client.query('BEGIN');

    if (normalizedIsolationLevel) {
      await client.query(`SET TRANSACTION ISOLATION LEVEL ${normalizedIsolationLevel}`);
    }

    const result = await work(client);

    await client.query('COMMIT');

    console.log({
      step: 'tx.commit',
      scope: label,
      isolationLevel: normalizedIsolationLevel,
    }, 'transaction committed');

    return result;
  } catch (error) {
    try {
      await client.query('ROLLBACK');

      console.error({
        step: 'tx.rollback',
        scope: label,
        isolationLevel: normalizedIsolationLevel,
        errorMessage: error?.message || null,
        errorCode: error?.code || null,
      }, 'transaction rolled back');
    } catch (rollbackError) {
      error.rollbackError = rollbackError;
    }

    throw error;
  } finally {
    client.release();
  }
}

async function withAdvisoryTransactionLock(lockKey, work, options = {}) {
  return withTransaction(async (client) => {
    return withTransactionAdvisoryLock(
      client,
      lockKey,
      () => work(client),
      options.lockOptions || {},
    );
  }, options);
}

async function transaction(work, options = {}) {
  return withTransaction(work, options);
}

module.exports = transaction;
module.exports.transaction = transaction;
module.exports.withTransaction = withTransaction;
module.exports.withAdvisoryTransactionLock = withAdvisoryTransactionLock;
module.exports.normalizeIsolationLevel = normalizeIsolationLevel;
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db$ cd helpers
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db/helpers$ ls
advisory-lock.js  pagination.js  query.js
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db/helpers$ cat query.js
'use strict';

const pool = require('../pool');
const { ValidationError } = require('../../lib/errors');

function resolveExecutor(client = null) {
  if (client && typeof client.query === 'function') {
    return client;
  }

  if (pool && typeof pool.query === 'function') {
    return pool;
  }

  throw new ValidationError('No valid database executor available', {
    code: 'DB_EXECUTOR_UNAVAILABLE',
  });
}

async function query(sql, params = [], client = null) {
  if (typeof sql !== 'string' || !sql.trim()) {
    throw new ValidationError('SQL must be a non-empty string', {
      code: 'DB_SQL_INVALID',
    });
  }

  if (!Array.isArray(params)) {
    throw new ValidationError('SQL params must be an array', {
      code: 'DB_SQL_PARAMS_INVALID',
    });
  }

  const executor = resolveExecutor(client);
  return executor.query(sql, params);
}

async function one(sql, params = [], client = null) {
  const result = await query(sql, params, client);
  return result.rows[0] || null;
}

async function many(sql, params = [], client = null) {
  const result = await query(sql, params, client);
  return result.rows;
}

async function none(sql, params = [], client = null) {
  await query(sql, params, client);
}

async function value(sql, params = [], client = null) {
  const row = await one(sql, params, client);
  if (!row) return null;

  const keys = Object.keys(row);
  return keys.length ? row[keys[0]] : null;
}

function buildWhereClause(filters = {}, options = {}) {
  const {
    columnMap = {},
    startIndex = 1,
  } = options;

  const clauses = [];
  const params = [];
  let index = startIndex;

  for (const [key, rawValue] of Object.entries(filters || {})) {
    if (rawValue === undefined) continue;

    const column = columnMap[key] || key;

    if (rawValue === null) {
      clauses.push(`${column} IS NULL`);
      continue;
    }

    if (Array.isArray(rawValue)) {
      if (rawValue.length === 0) {
        clauses.push('1 = 0');
        continue;
      }

      clauses.push(`${column} = ANY($${index})`);
      params.push(rawValue);
      index += 1;
      continue;
    }

    clauses.push(`${column} = $${index}`);
    params.push(rawValue);
    index += 1;
  }

  return {
    clause: clauses.length ? `WHERE ${clauses.join(' AND ')}` : '',
    params,
    nextIndex: index,
  };
}

function buildSetClause(updates = {}, options = {}) {
  const {
    columnMap = {},
    startIndex = 1,
    skipUndefined = true,
  } = options;

  const sets = [];
  const params = [];
  let index = startIndex;

  for (const [key, rawValue] of Object.entries(updates || {})) {
    if (skipUndefined && rawValue === undefined) continue;

    const column = columnMap[key] || key;
    sets.push(`${column} = $${index}`);
    params.push(rawValue);
    index += 1;
  }

  return {
    clause: sets.length ? `SET ${sets.join(', ')}` : '',
    params,
    nextIndex: index,
    isEmpty: sets.length === 0,
  };
}

function json(value) {
  return value == null ? null : JSON.stringify(value);
}

module.exports = {
  resolveExecutor,
  query,
  one,
  many,
  none,
  value,
  buildWhereClause,
  buildSetClause,
  json,
};
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db/helpers$ cat pagination.js
'use strict';

function normalizeLimit(limit, fallback = 20, max = 100) {
  const parsed = Number.parseInt(limit, 10);
  if (!Number.isFinite(parsed) || parsed <= 0) return fallback;
  return Math.min(parsed, max);
}

function normalizeOffset(offset, fallback = 0) {
  const parsed = Number.parseInt(offset, 10);
  if (!Number.isFinite(parsed) || parsed < 0) return fallback;
  return parsed;
}

function buildLimitOffset(limit, offset, options = {}) {
  const safeLimit = normalizeLimit(
    limit,
    options.defaultLimit || 20,
    options.maxLimit || 100,
  );
  const safeOffset = normalizeOffset(
    offset,
    options.defaultOffset || 0,
  );

  return {
    limit: safeLimit,
    offset: safeOffset,
    sql: `LIMIT ${safeLimit} OFFSET ${safeOffset}`,
  };
}

function buildCursorPage(rows, options = {}) {
  const limit = normalizeLimit(options.limit ?? rows.length, rows.length || 20, options.maxLimit || 100);
  const getCursor = options.getCursor || ((row) => row?.id ?? null);

  const hasMore = rows.length > limit;
  const sliced = hasMore ? rows.slice(0, limit) : rows;
  const nextCursor = hasMore && sliced.length
    ? getCursor(sliced[sliced.length - 1])
    : null;

  return {
    items: sliced,
    pageInfo: {
      hasMore,
      nextCursor,
      limit,
    },
  };
}

function buildOffsetPage(rows, totalCount, options = {}) {
  const limit = normalizeLimit(options.limit, 20, options.maxLimit || 100);
  const offset = normalizeOffset(options.offset, 0);
  const total = Number(totalCount || 0);

  return {
    items: Array.isArray(rows) ? rows : [],
    pageInfo: {
      totalCount: total,
      limit,
      offset,
      hasMore: offset + (Array.isArray(rows) ? rows.length : 0) < total,
    },
  };
}

module.exports = {
  normalizeLimit,
  normalizeOffset,
  buildLimitOffset,
  buildCursorPage,
  buildOffsetPage,
};
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db/helpers$ cat advisory-lock.js
'use strict';

const crypto = require('node:crypto');
const { ValidationError } = require('../../lib/errors');

const BIGINT_MASK_63 = BigInt('0x7fffffffffffffff');

function assertClient(client) {
  if (!client || typeof client.query !== 'function') {
    throw new ValidationError('A pg client with query() is required', {
      code: 'PG_CLIENT_REQUIRED',
    });
  }
}

function normalizeLockKey(key, namespace = 'default') {
  if (typeof key !== 'string' || key.trim() === '') {
    throw new ValidationError('Advisory lock key must be a non-empty string', {
      code: 'ADVISORY_LOCK_KEY_INVALID',
    });
  }

  if (typeof namespace !== 'string' || namespace.trim() === '') {
    throw new ValidationError('Advisory lock namespace must be a non-empty string', {
      code: 'ADVISORY_LOCK_NAMESPACE_INVALID',
    });
  }

  return `${namespace.trim()}:${key.trim()}`;
}

function advisoryLockIdFromString(key, namespace = 'default') {
  const normalized = normalizeLockKey(key, namespace);
  const hex = crypto
    .createHash('sha256')
    .update(normalized, 'utf8')
    .digest('hex')
    .slice(0, 16);

  return BigInt(`0x${hex}`) & BIGINT_MASK_63;
}

async function tryAdvisoryLock(client, key, options = {}) {
  assertClient(client);
  const lockId = advisoryLockIdFromString(key, options.namespace);

  const result = await client.query(
    'SELECT pg_try_advisory_lock($1::bigint) AS locked',
    [lockId.toString()],
  );

  return {
    lockId,
    locked: Boolean(result.rows[0]?.locked),
  };
}

async function acquireAdvisoryLock(client, key, options = {}) {
  assertClient(client);
  const lockId = advisoryLockIdFromString(key, options.namespace);

  await client.query(
    'SELECT pg_advisory_lock($1::bigint)',
    [lockId.toString()],
  );

  return { lockId, locked: true };
}

async function releaseAdvisoryLock(client, key, options = {}) {
  assertClient(client);
  const lockId = advisoryLockIdFromString(key, options.namespace);

  const result = await client.query(
    'SELECT pg_advisory_unlock($1::bigint) AS unlocked',
    [lockId.toString()],
  );

  return {
    lockId,
    unlocked: Boolean(result.rows[0]?.unlocked),
  };
}

async function acquireTransactionAdvisoryLock(client, key, options = {}) {
  assertClient(client);
  const lockId = advisoryLockIdFromString(key, options.namespace);

  await client.query(
    'SELECT pg_advisory_xact_lock($1::bigint)',
    [lockId.toString()],
  );

  return { lockId, locked: true };
}

async function withAdvisoryLock(client, key, work, options = {}) {
  assertClient(client);

  if (typeof work !== 'function') {
    throw new ValidationError('work must be a function', {
      code: 'ADVISORY_LOCK_WORK_INVALID',
    });
  }

  await acquireAdvisoryLock(client, key, options);

  try {
    return await work();
  } finally {
    await releaseAdvisoryLock(client, key, options).catch(() => null);
  }
}

async function withTransactionAdvisoryLock(client, key, work, options = {}) {
  assertClient(client);

  if (typeof work !== 'function') {
    throw new ValidationError('work must be a function', {
      code: 'ADVISORY_XACT_LOCK_WORK_INVALID',
    });
  }

  await acquireTransactionAdvisoryLock(client, key, options);
  return work();
}

module.exports = {
  normalizeLockKey,
  advisoryLockIdFromString,
  tryAdvisoryLock,
  acquireAdvisoryLock,
  releaseAdvisoryLock,
  acquireTransactionAdvisoryLock,
  withAdvisoryLock,
  withTransactionAdvisoryLock,
};
genesis@pudelverse-core:/opt/pudelverse/auth-backend/src/db/helpers$