// @ts-check
/**
* Project snapshot service - time-exact project state from MariaDB temporal
* tables. See 080-Workspaces/025-Members-Backend-Implementation-Plan.mdx Phase 6
* and the project-snapshot.schema.json contract.
*
* Rules:
* - POST snapshot/current reads CURRENT_TIMESTAMP(6) from MariaDB first and
* resolves project, shares and links with exactly this value.
* - GET snapshot?asOf= uses the supplied RFC3339 timestamp (UTC).
* - A point older than the 13 year history guarantee -> 410 SNAPSHOT_EXPIRED.
* - A point where the project state never existed -> 404 SNAPSHOT_NOT_FOUND.
* - Snapshots never contain user rights.
*
* @import {ExpressRequestAuthorized} from '../../types.js'
*/
import { query, UUID2hex, HEX2uuid, pool } from '@commtool/sql-query';
import { isObjectVisible } from '../../utils/authChecks.js';
import { apiError } from '../../utils/apiEnvelope.js';
import { RETENTION_YEARS, metadataFromRow } from '../../utils/projectContract.js';
import { errorLoggerRead } from '../../utils/requestLogger.js';
import { getOrganizationForObject, isObjectInOrg } from '../../utils/organizationUtils.js';
const SHARE_TYPES_SQL = `'repositoryShare','directoryShare'`;
const RETENTION_MS = RETENTION_YEARS * 365.25 * 24 * 3600 * 1000;
/**
* Convert a MariaDB timestamp string ('YYYY-MM-DD HH:MM:SS.ffffff', UTC) to the
* contract RFC3339 format with six fractional digits.
* @param {string} mysqlTime
* @returns {string}
*/
export const mysqlTimeToRfc3339 = (mysqlTime) => {
const [date, time] = String(mysqlTime).split(' ');
const micro = time ? time.split('.')[1] || '000000' : '000000';
return `${date}T${time ? time.split('.')[0] : '00:00:00'}.${micro.padEnd(6, '0')}Z`;
};
/**
* Convert an RFC3339 timestamp (with optional timezone offset and microsecond
* fraction) into a UTC MariaDB timestamp literal for FOR SYSTEM_TIME AS OF.
* Returns null when the value is not parseable.
* @param {string} value
* @returns {string|null}
*/
export const rfc3339ToMysql = (value) => {
const m = /^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:\.(\d{1,6}))?(Z|([+-])(\d{2}):(\d{2}))?$/.exec(String(value).trim());
if (!m) return null;
const [, Y, Mo, D, H, Mi, S, frac, tz, sign, tzh, tzm] = m;
const micro = (frac || '').padEnd(6, '0');
if (!tz || tz === 'Z') {
return `${Y}-${Mo}-${D} ${H}:${Mi}:${S}.${micro}`;
}
// Convert wall-clock time to UTC using the supplied offset.
const offsetMin = (sign === '-' ? 1 : -1) * (parseInt(tzh, 10) * 60 + parseInt(tzm, 10));
let totalMin = (parseInt(H, 10) * 60 + parseInt(Mi, 10)) + offsetMin;
let day = parseInt(D, 10);
let hour = Math.floor(totalMin / 60);
let minute = totalMin % 60;
if (minute < 0) { minute += 60; hour -= 1; }
if (hour < 0) { hour += 24; day -= 1; }
if (hour >= 24) { hour -= 24; day += 1; }
const shifted = new Date(Date.UTC(parseInt(Y, 10), parseInt(Mo, 10) - 1, day));
const p = (n) => String(n).padStart(2, '0');
return `${shifted.getUTCFullYear()}-${p(shifted.getUTCMonth() + 1)}-${p(shifted.getUTCDate())} ${p(hour)}:${p(minute)}:${p(parseInt(S, 10))}.${micro}`;
};
/**
* Parse a snapshot share row into the contract share shape. The share belongs
* to the org, so project_uid comes from the snapshot context, not UIDBelongsTo.
* @param {any} row
* @param {string} projectUid
*/
const shareToSnapshot = (row, projectUid) => {
let metadata = {};
try {
metadata = row.Data && typeof row.Data === 'object' ? row.Data.metadata ?? {} : {};
} catch (e) {
metadata = {};
}
return {
uid: HEX2uuid(row.UID),
project_uid: projectUid,
type: row.Type,
metadata,
};
};
/**
* Authorize that the project is currently accessible to the caller (snapshots
* are service operations gated by the current event-driven rights).
* @param {ExpressRequestAuthorized} req
* @param {string} uidHex
* @param {string} orgHex
*/
const requireAccessible = async (req, uidHex, orgHex) => {
const [project] = await query(
`SELECT UID FROM ObjectBase WHERE UID=? AND Type='project'`,
[uidHex],
);
if (!project) throw apiError(404, 'PROJECT_NOT_FOUND', 'Project not found in this organization');
// The organization is derived over the owner group (UIDBelongsTo now holds the
// project's own UID) — see projectInOrg.
if (!(await isObjectInOrg(uidHex, orgHex))) {
throw apiError(404, 'PROJECT_NOT_FOUND', 'Project not found in this organization');
}
if (!(await isObjectVisible(req, uidHex))) {
throw apiError(403, 'PROJECT_NOT_ACCESSIBLE', 'Project is not accessible');
}
};
/**
* Build the snapshot for a resolved UTC MySQL timestamp.
* @param {string} uidHex
* @param {string} orgHex
* @param {string} mysqlTime - 'YYYY-MM-DD HH:MM:SS.ffffff' UTC
* @returns {Promise<Object>}
*/
const buildSnapshot = async (uidHex, orgHex, mysqlTime) => {
const asOf = `FOR SYSTEM_TIME AS OF TIMESTAMP${pool.escape(mysqlTime)}`;
const projectRows = await query(
`SELECT ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Title, ObjectBase.Data,
JSON_UNQUOTE(JSON_VALUE(ObjectBase.Data, '$.description')) AS description,
JSON_OBJECT('value', JSON_EXTRACT(ObjectBase.Data, '$.metadata')) AS metadata_json,
JSON_UNQUOTE(JSON_VALUE(ObjectBase.Data, '$.groupUID')) AS group_uid
FROM ObjectBase ${asOf}
WHERE ObjectBase.UID=? AND ObjectBase.Type='project'`,
[uidHex],
{ cast: ['json'] },
);
if (projectRows.length !== 1) {
throw apiError(404, 'SNAPSHOT_NOT_FOUND', 'No project state exists at this time');
}
const projectRow = projectRows[0];
const projectUidStr = HEX2uuid(uidHex);
const linkRows = await query(
`SELECT Links.UID AS source_uid, Links.UIDTarget AS target_uid, Links.Type AS link_type
FROM Links ${asOf}
WHERE (Links.UID=? OR Links.UIDTarget=?) AND Links.Type IN ('memberA','member')`,
[uidHex, uidHex],
);
// Collect the link ends that are not the project itself. Both directions are
// read so snapshots stay exact across the migration (Share -> Projekt now,
// Projekt -> Share in older states).
const candidates = new Map();
for (const l of linkRows) {
const src = HEX2uuid(l.source_uid);
const tgt = HEX2uuid(l.target_uid);
const fromProject = src === projectUidStr;
const other = fromProject ? tgt : src;
if (other === projectUidStr) continue;
candidates.set(other, { uid: fromProject ? l.target_uid : l.source_uid, link_type: l.link_type });
}
let shares = [];
let links = [];
if (candidates.size) {
const shareRows = await query(
`SELECT ObjectBase.UID, ObjectBase.Type, ObjectBase.UIDBelongsTo, ObjectBase.Data
FROM ObjectBase ${asOf}
WHERE ObjectBase.UID IN (?) AND ObjectBase.Type IN (${SHARE_TYPES_SQL})`,
[[...candidates.values()].map((c) => c.uid)],
{ cast: ['json'] },
);
shares = shareRows.map((r) => shareToSnapshot(r, projectUidStr)).sort((a, b) => (a.uid < b.uid ? -1 : a.uid > b.uid ? 1 : 0));
const shareUidSet = new Set(shareRows.map((r) => HEX2uuid(r.UID)));
links = [...candidates.entries()]
.filter(([uid]) => shareUidSet.has(uid))
.map(([uid, c]) => ({ uid: projectUidStr, type: c.link_type, target_uid: uid }))
.sort((a, b) => (a.target_uid < b.target_uid ? -1 : a.target_uid > b.target_uid ? 1 : 0));
}
const metadata = metadataFromRow(projectRow);
// Old model: UIDBelongsTo held the organization. New model: it holds the
// project's own UID, so the organization is derived over the owner group.
const organizationUid = HEX2uuid(projectRow.UIDBelongsTo) === HEX2uuid(projectRow.UID)
? (await getOrganizationForObject(uidHex)) || null
: HEX2uuid(projectRow.UIDBelongsTo);
return {
schema_version: 1,
project_uid: projectUidStr,
project_snapshot_at: mysqlTimeToRfc3339(mysqlTime),
project: {
uid: HEX2uuid(projectRow.UID),
organization_uid: organizationUid,
group_uid: projectRow.group_uid ? HEX2uuid(projectRow.group_uid) : null,
title: projectRow.Title || '',
description: projectRow.description ?? null,
metadata,
},
shares,
links,
};
};
/**
* POST snapshot/current - authoritative DB time snapshot.
* @param {ExpressRequestAuthorized} req
* @param {string} projectUid
* @returns {Promise<Object>}
*/
export const snapshotCurrent = async (req, projectUid) => {
try {
const uidHex = UUID2hex(projectUid);
const orgHex = UUID2hex(req.session.root);
await requireAccessible(req, uidHex, orgHex);
// authoritative database time - never an application clock
const [row] = await query(`SELECT DATE_FORMAT(CURRENT_TIMESTAMP(6), '%Y-%m-%d %H:%i:%s.%f') AS ts`);
return { success: true, result: await buildSnapshot(uidHex, orgHex, row.ts) };
} catch (e) {
errorLoggerRead(e);
throw e;
}
};
/**
* GET snapshot?asOf=<RFC3339> - historical snapshot.
* @param {ExpressRequestAuthorized} req
* @param {string} projectUid
* @param {string} asOf
* @returns {Promise<Object>}
*/
export const snapshotAsOf = async (req, projectUid, asOf) => {
try {
const uidHex = UUID2hex(projectUid);
const orgHex = UUID2hex(req.session.root);
await requireAccessible(req, uidHex, orgHex);
const mysqlTime = rfc3339ToMysql(asOf);
if (!mysqlTime) {
throw apiError(400, 'INVALID_AS_OF', 'asOf must be an RFC3339 timestamp');
}
const epoch = Date.parse(asOf);
if (Number.isNaN(epoch)) {
throw apiError(400, 'INVALID_AS_OF', 'asOf must be an RFC3339 timestamp');
}
if (Date.now() - epoch > RETENTION_MS) {
throw apiError(410, 'SNAPSHOT_EXPIRED', 'Snapshot older than the guaranteed temporal history');
}
return { success: true, result: await buildSnapshot(uidHex, orgHex, mysqlTime) };
} catch (e) {
errorLoggerRead(e);
throw e;
}
};