memind/mindspace-userdata-postgres.test.mjs

import assert from 'node:assert/strict';
import fs from 'node:fs';
import os from 'node:os';
import path from 'node:path';
import test from 'node:test';
import {
  buildControlSchemaSql,
  buildEnsureCurrentUserCanSetRoleSql,
  buildPostgresTableSql,
  buildPostgresCheckSql,
  buildPostgresForeignKeySql,
  buildPostgresIndexSql,
  buildProvisionUserSql,
  createSqliteSnapshot,
  convertSqliteValueForPostgres,
  deriveUserSpaceNames,
  extractSqliteChecks,
  mapSqliteTypeToPostgres,
  translateSqliteDefault,
} from './mindspace-userdata-postgres.mjs';
import { execFileSync } from 'node:child_process';

const USER_ID = 'ecc1c649-fff7-4361-a243-a69b460cc407';

test('deriveUserSpaceNames creates stable UUID based identifiers', () => {
  assert.deepEqual(deriveUserSpaceNames(USER_ID), {
    userId: USER_ID,
    schemaName: 'u_ecc1c649fff74361a243a69b460cc407',
    ownerRole: 'ms_u_ecc1c649fff7_owner',
    agentRole: 'ms_u_ecc1c649fff7_agent',
  });
});

test('control schema is private and tracks migration lifecycle', () => {
  const sql = buildControlSchemaSql();
  assert.match(sql, /REVOKE ALL ON SCHEMA "mindspace_control" FROM PUBLIC/);
  assert.match(sql, /CREATE TABLE IF NOT EXISTS "mindspace_control"\.user_spaces/);
  assert.match(sql, /migration_runs/);
  assert.match(sql, /audit_events/);
});

test('provision SQL creates isolated no-login roles and safety limits', () => {
  const provision = buildProvisionUserSql(USER_ID, { sourceSqlitePath: '/tmp/user.sqlite' });
  const sql = provision.statements.join('\n');
  assert.match(sql, /CREATE ROLE "ms_u_ecc1c649fff7_owner" NOLOGIN/);
  assert.match(sql, /CREATE ROLE "ms_u_ecc1c649fff7_agent" NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE/);
  assert.match(sql, /pg_auth_members/);
  assert.match(sql, /r\.rolname = 'ms_u_ecc1c649fff7_agent'.*am\.set_option/s);
  assert.match(sql, /GRANT %I TO %I WITH INHERIT FALSE, SET TRUE/);
  assert.match(sql, /REVOKE ALL ON SCHEMA "u_ecc1c649fff74361a243a69b460cc407" FROM PUBLIC/);
  assert.match(sql, /statement_timeout = '30s'/);
  assert.match(sql, /NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION/);
  assert.doesNotMatch(sql, /temp_file_limit/);
});

test('agent role reconciliation requires SET without inherited privileges', () => {
  const sql = buildEnsureCurrentUserCanSetRoleSql('ms_u_ecc1c649fff7_agent');
  assert.match(sql, /m\.rolname = CURRENT_USER AND am\.set_option/);
  assert.match(sql, /WITH INHERIT FALSE, SET TRUE/);
  assert.doesNotMatch(sql, /WITH INHERIT TRUE/);
});

test('SQLite types and defaults map to conservative PostgreSQL equivalents', () => {
  assert.equal(mapSqliteTypeToPostgres({ type: 'INTEGER', pk: 1 }), 'bigint GENERATED BY DEFAULT AS IDENTITY');
  assert.equal(mapSqliteTypeToPostgres({ type: 'REAL', pk: 0 }), 'double precision');
  assert.equal(mapSqliteTypeToPostgres({ type: 'TEXT', pk: 0 }), 'text');
  assert.equal(mapSqliteTypeToPostgres({ type: 'TEXT', pk: 0, dflt_value: "datetime('now', 'localtime')" }), 'timestamptz');
  assert.equal(translateSqliteDefault("(datetime('now', '+8 hours'))"), 'CURRENT_TIMESTAMP');
  assert.equal(translateSqliteDefault('""'), "''");
  assert.equal(translateSqliteDefault('dangerous_function()'), null);
});

test('SQLite checks, indexes and foreign keys become scoped PostgreSQL objects', () => {
  assert.deepEqual(extractSqliteChecks("CREATE TABLE x(status TEXT CHECK(status IN ('a','b')), score INT CHECK(score > 0))"), [
    "status IN ('a','b')",
    'score > 0',
  ]);
  assert.match(buildPostgresIndexSql('u_test', 'users', { unique: true, columns: ['username'] }), /CREATE UNIQUE INDEX/);
  assert.match(buildPostgresForeignKeySql('u_test', 'child', {
    id: 0, table: 'parent', from: ['parent_id'], to: ['id'], onUpdate: 'NO ACTION', onDelete: 'CASCADE',
  }), /REFERENCES "u_test"\."parent" \("id"\).*ON DELETE CASCADE/);
  assert.match(buildPostgresCheckSql('u_test', 'users', "status IN ('a','b')", 0), /CHECK \(status IN \('a','b'\)\)/);
});

test('SQLite local timestamps are made explicit for PostgreSQL', () => {
  assert.equal(
    convertSqliteValueForPostgres('2026-07-13 10:20:30', { dflt_value: "datetime('now', '+8 hours')" }),
    '2026-07-13T10:20:30+08:00',
  );
  assert.equal(convertSqliteValueForPostgres('plain', { dflt_value: null }), 'plain');
});

test('table builder preserves primary key, not-null and safe defaults', () => {
  const sql = buildPostgresTableSql('u_example', 'survey', [
    { name: 'id', type: 'INTEGER', pk: 1, notnull: 0, dflt_value: null },
    { name: 'answer', type: 'TEXT', pk: 0, notnull: 1, dflt_value: "''" },
    { name: 'created_at', type: 'TEXT', pk: 0, notnull: 1, dflt_value: "datetime('now', 'localtime')" },
  ]);
  assert.match(sql, /"id" bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY/);
  assert.match(sql, /"answer" text NOT NULL DEFAULT ''/);
  assert.match(sql, /"created_at" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP/);
});

test('SQLite snapshot includes committed rows without changing source', () => {
  const root = fs.mkdtempSync(path.join(os.tmpdir(), 'mindspace-pg-test-'));
  const source = path.join(root, 'source.sqlite');
  execFileSync('sqlite3', [source, 'CREATE TABLE sample (id INTEGER PRIMARY KEY, value TEXT); INSERT INTO sample VALUES (1, \'ok\');']);
  const snapshot = createSqliteSnapshot(source);
  try {
    const count = execFileSync('sqlite3', ['-readonly', snapshot, 'SELECT count(*) FROM sample;'], { encoding: 'utf8' }).trim();
    assert.equal(count, '1');
  } finally {
    fs.rmSync(root, { recursive: true, force: true });
    fs.rmSync(snapshot, { force: true });
  }
});