import { query, UUID2hex, HEX2uuid, transaction, HEX2base64 } from '@commtool/sql-query';
import { renderObject } from '../../utils/renderTemplates.js';
import { Templates, BannerRules, getConfig, phonetikArray } from '../../utils/compileTemplates.js';
import { queueRunning, triggerQueues, queueAdd } from '../../tree/treeQueue/treeQueue.js';
import { personRebuildAccess, listRebuildAccess, personListRebuildAccess } from '../../tree/rebuildList.js';
import { extractAchievements } from '../functionTemplate/controller.js';
import { requalify, jobQualified } from '../job/utilities.js';
import { insertList, list } from '../list.js';
import { addFilter } from '../filter.js';
import { rebuildEventAccess } from '../../RouterEvents/eventVisibility.js';
import _ from 'lodash';
import { getFamilyFees } from '../../utils/getFamilyFee.js';
import { rebuildWithTimeFilters } from '../../cron/cron.js';
import { addVisibility } from '../../tree/matchObjects/matchObjects.js';
import { checkPersonListMember } from '../../tree/matchObjects/checkPersonListMember.js';
import { superAdminData } from '../../config/superAdminJob.js';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { EventEmitter } from 'events';
import { sendProgressStatus } from '../../server.ws.js';
import { createDB as configCreateDB } from '../../config/initOrga.js';
import { generateEmbeddings, calculateContentHash, EMBEDDING_CONFIG } from '../../utils/embeddings.js';
import sanitizePII from '../../utils/santitizePII.js';
// @ts-check
/**
* @import {ExpressRequestAuthorized, ExpressResponse} from './../../types.js'
*/
/**
* Maintenance Controller
*
* This controller contains all business logic for maintenance operations:
* - Object recreation based on templates
* - Database optimization and cleanup
* - Tree rebuilding and access management
* - Achievement processing
* - Family and person data management
* - Event management and participant lists
* - System maintenance tasks
*/
/**
* Recreates all objects of the organization of the given type via the template
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const recreateObjects = async (req, res) => {
sendProgressStatus(req.session.root, {
action: 'start',
text: `Processing recreate ${req.params.type} objects...`,
progress: 0
});
try {
const template = Templates[req.session.root][req.params.type];
if (['person', 'extern', 'guest'].includes(req.params.type)) {
// recreate all objects of the organisation via the template
const members = await query(`SELECT ObjectBase.UID,Member.Data,PGroup.Data AS GroupData,ObjectBase.Type,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom, ObjectBase.SortName, ObjectBase.Title,
ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage, ObjectBase.Data AS BaseData,
Member.FullTextIndex, Member.PhonetikIndex, Member.SortName AS SortIndex
FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UIDBelongsTo)
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Member AS PGroup ON (PGroup.UID=GLink.UIDTarget)
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`, [req.params.type, UUID2hex(req.session.root)], { cast: ['json'] });
for (const member of members) {
// adjust for persons the stage and hierarchie according to member group
if (member.UID.equals(UUID2hex('UUID-2854dfb1-e869-11eb-aa16-0242ac190005')))
debugger;
const memberData = member.Data;
const GroupData = member.GroupData;
const oldData = _.cloneDeep(memberData);
const oldMemberStage = memberData.stage;
const config = await getConfig('db', req);
memberData.stage = GroupData.stage > 0 ? GroupData.stage : config.DefaultStage;
if (oldMemberStage != memberData.stage)
await query(`UPDATE Member SET Data=? WHERE UID=?`, [JSON.stringify(memberData), member.UID]);
memberData.hierarchie = GroupData.hierarchie;
if (BannerRules[req.session.root].member[0] === 'inherit') {
if (GroupData.banner)
memberData.banner = GroupData.banner;
} else if (BannerRules[req.session.root].member.includes('inherit')) {
memberData.banner = GroupData.banner;
}
const object = await renderObject(template, { UID: HEX2uuid(member.UID), ...memberData, group: GroupData }, req);
const phonetikIndex = phonetikArray([memberData.firstName, memberData.lastName]);
object.UID = member.UID;
if (member.Title !== object.Title || member.SortName !== object.SortBase || member.hierarchie !== object.hierarchie
|| member.gender !== object.gender || member.stage !== object.stage) {
// find the latest none actual system time we can back-date to
const latestBefore = await query(`SELECT UNIX_TIMESTAMP(MAX(validUntil)) as backDate FROM ObjectBase FOR SYSTEM_TIME ALL
WHERE UID=? AND validUntil<'2038-01-01'`, [member.UID], { log: false });
const timestamp = latestBefore[0].backDate > parseFloat(member.validFrom) ? parseFloat(latestBefore[0].backDate) : parseFloat(member.validFrom);
await transaction(async (connection) => {
console.log('update', HEX2uuid(member.UID), new Date(timestamp));
await query(`
UPDATE ObjectBase SET Title=?,SortName=?,hierarchie=?,stage=?,gender=?,Data=?
WHERE UID=?
`,
[object.Title, object.SortBase,
object.hierarchie, object.stage, object.gender, JSON.stringify(member.BaseData),
member.UID], { connection }
);
}, { backDate: (timestamp + 3600) });
}
if (req.params.type !== 'guest') {
if (oldData.banner !== memberData.banner || member.SortIndex != object.SortIndex ||
member.FullTextIndex !== object.FullTextIndex || member.PhonetikIndex !== phonetikIndex ||
oldData.stage !== memberData.stage || oldData.hierarchie !== memberData.hierarchie)
await query(`UPDATE Member SET
Display=?,SortName=?,FullTextIndex=?,PhonetikIndex=?, Data=?
WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex, phonetikIndex, JSON.stringify(memberData), member.UID]);
}
}
} else if (req.params.type === 'job') {
const UIDroot = UUID2hex(req.session.root);
const jobs = await query(`SELECT ObjectBase.UID,ObjectBase.Data,ObjectBase.dindex as qualified, PGroup.Data AS GroupData,
Person.UID AS UIDperson,FunctionT.Data AS FunctionData,ObjectBase.Title,
FunctionT.UID AS UIDfunction, UNIX_TIMESTAMP(Person.ValidFrom) AS validFrom, ObjectBase.SortName,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
INNER JOIN Links AS GLink ON (GLink.UID=ObjectBase.UID AND GLink.Type='memberA')
INNER JOIN Member AS PGroup ON (PGroup.UID=GLink.UIDTarget)
INNER JOIN ObjectBase AS Person ON (ObjectBase.UIDBelongsTo=Person.UID AND Person.Type='person')
INNER JOIN Links AS FLink ON (FLink.UIDTarget=ObjectBase.UID AND FLink.Type='function')
INNER JOIN ObjectBase AS FunctionT ON (FunctionT.UID=FLink.UID)
WHERE ObjectBase.Type='job' AND Links.UIDTarget=? GROUP BY ObjectBase.UID`, [UUID2hex(req.session.root)], { cast: ['json'] });
for (const job of jobs) {
const functionData = job.FunctionData;
const qualified = await jobQualified(job.UIDperson, functionData.qualification ?? {}, Date.now() / 1000);
const Data = {
UID: HEX2uuid(job.UID),
qualified: qualified,
function: { ...job.FunctionData, functionUID: HEX2uuid(job.UIDfunction) },
group: job.GroupData
};
const object = await renderObject(template, Data, req);
if (job.Title !== object.Title, job.SortName !== object.SortBase || job.dindex !== qualified || job.hierarchie !== object.hierarchie
|| job.gender !== object.gender || job.stage !== object.stage) {
transaction(async (connection) => {
await query(`DELETE FROM ObjectBase WHERE UID=? `, [job.UID], { connection });
await query(`
INSERT INTO ObjectBase (UID,Type,UIDBelongsTo,Title,SortName,dindex,hierarchie,stage,gender,Data)
VALUES (?,'job',?,?,?,?,?,?,?,?)
`,
[job.UID, job.UIDperson, object.Title, object.SortBase, qualified, object.hierarchie, object.stage, object.gender, JSON.stringify(Data)
], { connection });
}, { backDate: (parseFloat(job.validFrom) + 3600) });
}
}
} else if (req.params.type === 'function') {
const objects = await query(`SELECT ObjectBase.UID,UIDBelongsTo,ObjectBase.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title,ObjectBase.Display,ObjectBase.SortName, ObjectBase.Type, ObjectBase.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
WHERE ObjectBase.Type=? AND ObjectBase.UIDBelongsTo=?`,
[req.params.type, UUID2hex(req.session.root)],
{ cast: ['json'] }
);
for (const myObject of objects) {
const object = await renderObject(template, { UID: HEX2uuid(myObject.UID), ...myObject.Data }, req);
if (myObject.Title !== object.Title || myObject.Display !== object.Display || myObject.SortName !== object.SortIndex
|| myObject.hierarchie !== object.hierarchie || myObject.FullTextIndex !== object.FullTextIndex
|| myObject.gender !== object.gender || myObject.stage !== object.stage) {
await transaction(async (connection) => {
await query(`DELETE FROM ObjectBase WHERE UID=? `, [myObject.UID], { connection });
await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName,FullTextIndex, dindex,hierarchie,stage,gender,Data)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
`,
[myObject.UID, myObject.Type, UUID2hex(req.session.root), object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex,
object.hierarchie, object.stage, object.gender, myObject.Data],
{ connection }
);
}, { backDate: (parseFloat(myObject.validFrom) + 1) });
}
}
} else if (['event', 'location'].includes(req.params.type)) {
const app = ['event'].includes(req.params.type) ? 'events' : ['location'].includes(req.params.type) ? 'locations' : 'db';
const objects = await query(`SELECT ObjectBase.UID,UIDBelongsTo,Member.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title,Member.Display,ObjectBase.SortName AS SortBase,Member.SortName, ObjectBase.Type,
Member.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA','location'))
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`,
[req.params.type, UUID2hex(req.session.root)],
{ cast: ['json'], log: false }
);
for (const myObject of objects) {
const object = await renderObject(template, { UID: HEX2uuid(myObject.UID), ...myObject.Data }, req, app);
if (myObject.Title !== object.Title || myObject.Display !== object.Display || myObject.SortBase !== object.SortBase
|| myObject.SortName !== object.SortIndex
|| myObject.hierarchie !== object.hierarchie || myObject.FullTextIndex !== object.FullTextIndex
|| myObject.gender !== object.gender || myObject.stage !== object.stage) {
await transaction(async (connection) => {
await connection.query(`DELETE FROM ObjectBase WHERE UID=? `, [myObject.UID]);
await connection.query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,SortName, dindex,hierarchie,stage,gender,Data)
VALUES (?,?,?,?,?,?,?,?,?,?)
`,
[myObject.UID, myObject.Type, myObject.UIDBelongsTo, object.Title, object.SortBase, object.dindex,
object.hierarchie, object.stage, object.gender, myObject.Data],
);
await connection.query(`UPDATE Member SET Display=?, SortName=?, FullTextIndex =? WHERE UID=?`,
[object.Display, object.SortIndex, object.FullTextIndex, myObject.UID]);
}, { backDate: (parseFloat(myObject.validFrom) + 3600) });
}
}
} else {
const objects = await query(`SELECT ObjectBase.UID,UIDBelongsTo,ObjectBase.Data,
UNIX_TIMESTAMP(ObjectBase.validFrom) AS validFrom,
ObjectBase.Title,ObjectBase.Display,ObjectBase.SortName, ObjectBase.Type, ObjectBase.FullTextIndex,
ObjectBase.dindex, ObjectBase.hierarchie, ObjectBase.gender, ObjectBase.stage
FROM ObjectBase
INNER JOIN Links ON (Links.UID=ObjectBase.UID AND Links.Type IN ('member','memberA'))
WHERE ObjectBase.Type=? AND Links.UIDTarget=?`,
[req.params.type, UUID2hex(req.session.root)],
{ cast: ['json'] }
);
for (const myObject of objects) {
const object = await renderObject(template, { UID: HEX2uuid(myObject.UID), ...myObject.Data }, req);
if (myObject.Title !== object.Title || myObject.Display !== object.Display || myObject.SortName !== object.SortIndex
|| myObject.hierarchie !== object.hierarchie || myObject.FullTextIndex !== object.FullTextIndex
|| myObject.gender !== object.gender || myObject.stage !== object.stage) {
await transaction(async (connection) => {
await connection.query(`DELETE FROM ObjectBase WHERE UID=? `, [myObject.UID]);
await connection.query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,SortName,FullTextIndex, dindex,hierarchie,stage,gender,Data)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
`,
[myObject.UID, myObject.Type, myObject.UIDBelongsTo, object.Title, object.Display, object.SortIndex, object.FullTextIndex, object.dindex,
object.hierarchie, object.stage, object.gender, myObject.Data],
);
}, { backDate: (parseFloat(myObject.validFrom) + 3600) });
}
}
}
// Final status
sendProgressStatus(req.session.root, {
action: 'finished',
text: `All recreate ${req.params.type} objects completed`,
progress: 100
});
res.json({ success: true });
} catch (e) {
sendProgressStatus(req.session.root, {
action: 'error',
text: `Error: ${e.message}`
});
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Rebuilds the membership tree for the given type
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getTree = async (req, res) => {
try {
sendProgressStatus(req.session.root, {
action: 'start',
text: `Processing rebuild ownership tree ${req.params.identifyer}...`,
progress: 0
});
const identifyer = req.params.identifyer;
const type = identifyer.split('-')[0];
const start = parseFloat(identifyer.split('-')[1] || 0);
const end = parseFloat(identifyer.split('-')[2] || 0);
const rootUID = UUID2hex(req.session.root);
if (type === 'person') {
await personRebuildAccess(rootUID);
} else if (type === 'list') {
await listRebuildAccess(rootUID, rootUID);
} else if (type === 'personlist') {
await personListRebuildAccess(rootUID, rootUID);
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `Rebuild ownership tree ${req.params.identifyer} completed`,
progress: 100
});
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Migrates the database with pending migrations
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const migrateDB = async (req, res) => {
try {
sendProgressStatus(req.session.root, {
action: 'start',
text: 'Starting database migration...',
progress: 0
});
// Run any pending migrations
const migrations = await query('SELECT * FROM migrations WHERE applied = 0 ORDER BY id',[]);
let completed = 0;
for (const migration of migrations) {
await query(migration.sql, []);
await query('UPDATE migrations SET applied = 1 WHERE id = ?', [migration.id]);
completed++;
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Applied migration: ${migration.name}`,
progress: Math.round((completed / migrations.length) * 100)
});
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `Database migration completed. Applied ${migrations.length} migrations.`,
progress: 100
});
res.json({ success: true, migrationsApplied: migrations.length });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Gets achievements for the organization
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getAchievements = async (req, res) => {
try {
const achievements = await extractAchievements(req.session.root);
res.json(achievements);
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Gets duplicate achievements
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getAchievementsDuplicates = async (req, res) => {
try {
const duplicates = await query(`
SELECT o1.UID, o1.Title, o1.Data, o2.UID as duplicateUID, o2.Title as duplicateTitle
FROM ObjectBase o1
JOIN ObjectBase o2 ON o1.Title = o2.Title AND o1.UID < o2.UID
WHERE o1.Type = 'achievement' AND o2.Type = 'achievement'
AND o1.UIDBelongsTo = ? AND o2.UIDBelongsTo = ?
`, [UUID2hex(req.session.root), UUID2hex(req.session.root)], { cast: ['json'] });
res.json(duplicates);
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Gets achievements tree structure
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getAchievementsTree = async (req, res) => {
try {
const tree = await query(`
SELECT achievement.UID, achievement.UIDBelongsTo, UNIX_TIMESTAMP(achievement.validFrom) as validFrom
FROM ObjectBase AS achievement
INNER JOIN Links ON (achievement.UID = Links.UID AND Links.Type = 'achievement')
INNER JOIN ObjectBase AS template ON (template.UID = Links.UIDTarget)
WHERE template.UIDBelongsTo = ? AND template.Type = 'achievementT' AND achievement.Type = 'achievement'
ORDER BY achievement.UIDBelongsTo
`, [UUID2hex(req.session.root)], { cast: ['json'] });
res.json({ success: true, tree });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Requalifies all jobs based on function templates
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const requalifyPersons = async (req, res) => {
try {
sendProgressStatus(req.session.root, {
action: 'start',
text: 'Recheck qualifications for all jobs...',
progress: 0
});
const functions = await query(`
SELECT Main.UID, Main.Display, Main.SortName, Main.Title, Main.Data
FROM ObjectBase AS Main
WHERE Main.UIDBelongsTo = ? AND Main.Type = 'function'
GROUP BY Main.UID
ORDER BY Main.SortName
`, [UUID2hex(req.session.root)], { cast: ['json'] });
let i = 1;
for (const func of functions) {
await requalify(req, func.UID);
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Processed function: ${func.Title}`,
progress: Math.round((i / functions.length) * 100)
});
i++;
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `All ${functions.length} jobs completed`,
progress: 100
});
res.json({ success: true, processedFunctions: functions.length });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Triggers the organisations tree queue
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const triggerMaintenance = async (req, res) => {
try {
queueRunning[req.session.root] = false;
await triggerQueues();
res.json({ success: true, message: 'Tree queue triggered' });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Gets family data or processes family corrections
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getFamilyData = async (req, res) => {
try {
sendProgressStatus(req.session.root, {
action: 'start',
text: `Processing family data for UID: ${req.params.UID}...`,
progress: 0
});
const config = await getConfig('db', req);
let result = [];
if (req.params.UID === 'all') {
// Get all families that need correction
result = await query(`
SELECT DISTINCT f.UID, f.Title, f.Data
FROM ObjectBase f
WHERE f.Type = 'family' AND f.UIDBelongsTo = ?
`, [UUID2hex(req.session.root)], { cast: ['json'] });
} else {
// Get specific family
result = await query(`
SELECT f.UID, f.Title, f.Data
FROM ObjectBase f
WHERE f.Type = 'family' AND f.UID = ? AND f.UIDBelongsTo = ?
`, [UUID2hex(req.params.UID), UUID2hex(req.session.root)], { cast: ['json'] });
}
let i = 0;
if (result.length > 0) {
const toBeCorrected = await query(`
SELECT * FROM (
SELECT Links.UID, IF(ObjectBase.Type='person','familyFees','family') AS newType,
Links.Type, Links.UIDTarget, UNIX_TIMESTAMP(Links.ValidFrom) AS ValidFrom
FROM Links
INNER JOIN ObjectBase ON (Links.UID = ObjectBase.UID)
WHERE Links.UIDTarget IN (?) AND (
(Links.Type = 'familyFees' AND ObjectBase.Type = 'extern') OR
(Links.Type = 'family' AND ObjectBase.Type = 'person')
)
ORDER BY ValidFrom DESC
) AS x
GROUP BY x.UID
`, [result.map(o => o.UID)]);
for (const correct of toBeCorrected) {
await query(`
UPDATE Links SET Type = ? WHERE UID = ? AND UIDTarget = ?
`, [correct.newType, correct.UID, correct.UIDTarget]);
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Corrected family link: ${correct.UID}`,
current: i,
total: toBeCorrected.length,
progress: Math.round((i / toBeCorrected.length) * 100)
});
i++;
}
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `All families processed`,
progress: 100
});
res.json({ success: true, processed: i });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Checks family consistency
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const checkFamilies = async (req, res) => {
try {
const result = await query(`
SELECT DISTINCT
Member.UID AS UIDfamily, Member.Display,
JSON_VALUE(person.Data,'$.lastName') AS lastName,
person.Display AS DisplayPerson
FROM Member
INNER JOIN Links AS pLinks ON (pLinks.UIDTarget = Member.UID AND pLinks.Type IN('family','familyFees'))
INNER JOIN Member AS person ON (pLinks.UID = person.UID)
INNER JOIN ObjectBase ON (ObjectBase.UID = person.UID AND person.Type = 'person')
INNER JOIN Visible ON (Visible.UID = ObjectBase.UID)
INNER JOIN Links AS aLinks ON (aLinks.UID = ObjectBase.UID AND aLinks.Type = 'memberA')
GROUP BY person.UID
ORDER BY Member.Display, lastName
`, [], { cast: ['UUID'] });
const missmatch = [];
let family = {};
for (let entry of result) {
if (entry.UIDfamily !== family.UIDfamily) {
if (family.missmatch) {
missmatch.push(family);
}
family = {
UIDfamily: entry.UIDfamily,
Display: entry.Display,
lastName: entry.lastName,
members: [entry.DisplayPerson]
};
} else {
family.members.push(entry.DisplayPerson);
if (family.lastName !== entry.lastName) {
family.missmatch = true;
}
}
}
res.json(missmatch);
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Rebuilds visibility settings for objects
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getVisibility = async (req, res) => {
try {
if (req.params.UID === 'job') {
const jobs = await query(`
SELECT ObjectBase.UIDBelongsTo, ObjectBase.Title, Member.Display
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UIDBelongsTo)
WHERE ObjectBase.Type = 'job'
GROUP BY ObjectBase.UIDBelongsTo
`,[]);
let i = 0;
const total = jobs.length;
sendProgressStatus(req.session.root, {
action: 'start',
text: `Processing ${total} jobs...`,
progress: 0
});
for (const job of jobs) {
await personRebuildAccess(job.UIDBelongsTo);
await personListRebuildAccess(job.UIDBelongsTo);
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Processed job: ${job.Title}`,
current: i,
total: total,
progress: Math.round((i / total) * 100)
});
i++;
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `All ${total} jobs completed`,
progress: 100
});
res.json({ success: true });
} else {
await addVisibility(UUID2hex(req.params.UID), UUID2hex(req.session.root));
res.json({ success: true });
}
} catch (e) {
sendProgressStatus(req.session.root, {
action: 'error',
text: `Error: ${e.message}`
});
errorLoggerUpdate(e);
res.status(500).json({ error: e.message });
}
};
/**
* Optimizes database tables
*/
export const optimizeDatabase = async (/** @typeExpressRequestAuthorized */ req, /** @type {ExpressResponse} */ res) => {
try {
await optimizeTables();
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Utility function to optimize database tables
*/
export const optimizeTables = async () => {
// optimize all none system versioned tables
await transaction(async (connection) => {
await connection.query('OPTIMIZE TABLE `eventLog`;');
await connection.query('OPTIMIZE TABLE `Member`;');
await connection.query('OPTIMIZE TABLE `Visible`;');
await connection.query('OPTIMIZE TABLE `TreeQueue`;');
await connection.query('OPTIMIZE TABLE `Visible`;');
}, { connectionOptions: { queryTimeout: 100000 } });
};
/**
* Cleans up old event logs
*
* Deletes all events from the `eventLog` table that have a timestamp older than the specified timestamp.
* This includes system logs, user actions, and other logged events that are no longer relevant.
*/
/**
* Cleans up the event log by deleting entries older than a specified timestamp.
* If no timestamp is provided, it defaults to the first day of the previous month.
*
* @async
* @function cleanEventLog
* @param {number|null} [timestamp=null] - The UNIX timestamp (in seconds) to use as the cutoff for deletion.
* Defaults to the first day of the previous month if not provided.
* @returns {Promise<void>} Resolves when the cleanup operation is complete.
* @throws {Error} Logs any errors encountered during the operation.
*
* @description
* - Deletes events from the `eventLog` table where the `Timestamp` is older than the specified timestamp.
* - If `timestamp` is `null`, the default value is calculated as the UNIX timestamp of the first day of the previous month.
* - After deletion, the `eventLog` table is optimized to reclaim storage and improve performance.
* - The database transaction is executed with a query timeout of 100 seconds.
*/
export const cleanEventLog = async (timestamp = null) => {
try {
if (!timestamp) {
const today = new Date();
const year = today.getMonth() === 0 ? today.getFullYear() - 1 : today.getFullYear();
const month = today.getMonth() === 0 ? 11 : today.getMonth() - 1;
timestamp = Math.floor(new Date(year, month, 1).getTime() / 1000);
}
await transaction(async (connection) => {
await connection.query(`DELETE FROM eventLog WHERE Timestamp < FROM_UNIXTIME(?)`, [timestamp]);
await connection.query('OPTIMIZE TABLE `eventLog`;');
}, { connectionOptions: { queryTimeout: 100000 } });
} catch (e) {
errorLoggerUpdate(e);
}
};
/**
* Gets event logs
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getEventLog = async (req, res) => {
try {
await cleanEventLog(req.query.timestamp);
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Rebuilds list entries
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const rebuildListEntries = async (req, res) => {
try {
sendProgressStatus(req.session.root, {
action: 'start',
text: `Rebuilding list entries for UID: ${req.params.UID}...`,
progress: 0
});
// Delete dangling Links
await query(`DELETE Links FROM Links
INNER JOIN ObjectBase AS Target ON (Target.UID = Links.UIDTarget AND Target.Type = 'dlist')
LEFT JOIN ObjectBase ON (ObjectBase.UID = Links.UID AND ObjectBase.Type = 'entry')
WHERE Links.Type IN('member','member0','memberA') AND ObjectBase.UID IS NULL;`, []);
await query(`DELETE Links FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID = Links.UID AND ObjectBase.Type = 'entry')
INNER JOIN ObjectBase AS Target ON (Target.UID = Links.UIDTarget AND Target.Type = 'dlist')
LEFT JOIN ( Links AS DLink
INNER JOIN ObjectBase AS Filter
ON (DLink.UIDTarget = Filter.UID AND Filter.Type IN ('include','exclude','intersect') AND DLink.Type = 'dynamic')
) ON (DLink.UID = ObjectBase.UID)
WHERE Links.Type IN('member','member0','memberA') AND DLink.UID IS NULL;`, []);
if (req.params.UID === 'all') {
const persons = await query(`
SELECT ObjectBase.UID, ObjectBase.Type, ObjectBase.Title, Member.Display
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
INNER JOIN Links ON (Links.UID = ObjectBase.UID)
WHERE ObjectBase.Type IN ('extern','person')
AND Links.UIDTarget = ? AND Links.Type IN ('member','memberA')
GROUP BY ObjectBase.UID
`, [UUID2hex(req.session.root)]);
let i = 0;
for (const person of persons) {
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Processing person: ${person.Title}`,
current: i,
total: persons.length,
progress: Math.round((i / persons.length) * 100)
});
await checkPersonListMember(UUID2hex(person.UID), HEX2uuid(req.session.root));
++i;
}
} else {
await checkPersonListMember(UUID2hex(req.params.UID), HEX2uuid(req.session.root));
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `Rebuilding list entries completed`,
progress: 100
});
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* re-assesses birthday filter
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const createBirthdayFilter = async (req, res) => {
try {
await rebuildWithTimeFilters();
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Cleans unused filters
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const cleanFilters = async (req, res) => {
try {
const filter = await query(`DELETE ObjectBase FROM ObjectBase
LEFT JOIN (Links INNER JOIN ObjectBase AS dlist ON (Links.UIDTarget = dlist.UID AND Links.Type = 'list'))
ON (Links.UID = ObjectBase.UID)
WHERE Links.UID IS NULL AND ObjectBase.Type IN ('include','exclude', 'intersect')
`, []);
const vfilter = await query(`DELETE ObjectBase FROM ObjectBase
LEFT JOIN (Links INNER JOIN ObjectBase AS dlist ON (Links.UIDTarget = dlist.UID AND Links.Type = 'list'))
ON (Links.UID = ObjectBase.UID)
WHERE Links.UID IS NULL AND ObjectBase.Type IN ('visible','changeable')
`, []);
res.json({ filter, vfilter });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Rebuilds event visibility
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getEventVisibility = async (req, res) => {
try {
if (req.params.UID === 'all') {
const events = await query(`
SELECT event.UID FROM ObjectBase AS event
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type = 'event')
INNER JOIN ObjectBase AS eventT ON (eventT.UID = Links.UIDTarget)
WHERE ObjectBase.Type = 'event' AND eventT.UIDBelongsTo = ?
`, [UUID2hex(req.session.root)]);
for (const event of events) {
await rebuildEventAccess(event.UID, req);
}
} else {
const UIDevent = UUID2hex(req.params.UID);
if (UIDevent) {
await rebuildEventAccess(UIDevent, req);
} else {
return res.json({ success: false, message: 'You have to supply a UUID of an event, or all' });
}
}
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Manages event participant lists
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const getEventParticipantLists = async (req, res) => {
try {
const fixLists = async (eventUID) => {
const events = await query(`
SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo, ObjectBase.Title, eMember.Display,
eMember.Data AS Data, ALink.UIDTarget AS UIDTemplate, Template.Data AS TemplateData,
Template.Display AS DisplayTemplate,
pGroup.Title AS GroupTitle, pMember.Display AS GroupDisplay, pGroup.UID AS UIDgroup,
rMember.UID AS UIDresponsible, job.Title AS titleResponsible, rMember.Display AS responsible
FROM ObjectBase
INNER JOIN Member AS eMember ON (eMember.UID = ObjectBase.UID)
INNER JOIN ObjectBase AS pGroup ON (pGroup.UID = ObjectBase.UIDBelongsTo)
INNER JOIN Member AS pMember ON (pMember.UID = pGroup.UID)
INNER JOIN Links AS ALink ON (ALink.UID = ObjectBase.UID AND ALink.Type = 'event')
INNER JOIN ObjectBase FOR SYSTEM_TIME AS OF IF(ObjectBase.validFrom < TIMESTAMP '${(await getConfig('events', req)).minTemplateTime}', NOW(), ObjectBase.validFrom) AS Template
ON (Template.UID = ALink.UIDTarget AND Template.Type = 'eventT')
LEFT JOIN (Links AS RLinks INNER JOIN ObjectBase AS job ON (job.UID = RLinks.UID AND job.Type = 'eventJob')
INNER JOIN Member AS rMember ON (rMember.UID = job.UIDBelongsTo))
ON (RLinks.UIDTarget = ObjectBase.UID AND RLinks.Type = 'memberA')
WHERE ObjectBase.UID = ?
`, [eventUID], { cast: ['json'], log: false });
const event = events[0];
const lists = await query(`
SELECT ObjectBase.UID, Member.Data, owner.UIDTarget AS UIDowner
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type = 'memberA')
INNER JOIN ObjectBase AS event ON (event.UID = Links.UIDTarget AND event.Type = 'event')
INNER JOIN Links AS owner ON (owner.UID = ObjectBase.UID AND owner.Type = 'memberA')
WHERE ObjectBase.Type IN ('list','dlist')
AND Links.UIDTarget = ?
`, [event.UID], { cast: ['json', 'UUID'], log: false });
let mainList = lists.find(l => l.Data.tag.includes('all'));
if (!mainList) {
const result = await insertList({ ...req,
query: { private: true },
body: {
name: `${event.TemplateData.participantListPrefix} ${event.DisplayTemplate} ${event.Display}`,
tag: ['participant', 'event', 'all'],
}
}, eventUID, 'dlist');
mainList = result.result;
}
const eventGroups = await query(`
SELECT ObjectBase.UID, ObjectBase.UIDBelongsTo AS UIDBelongsTo, listOwner.UID AS UIDlist
FROM ObjectBase
INNER JOIN Links ON(Links.UID = ObjectBase.UID AND Links.Type = 'memberA')
LEFT JOIN Links AS listOwner ON (listOwner.UIDTarget = ObjectBase.UID AND listOwner.Type = 'memberA')
WHERE ObjectBase.Type = 'gentry' AND Links.UIDTarget = ?
`, [eventUID], { log: false, cast: ['UUID'] });
for (const group of eventGroups) {
let glist = group.UIDlist;
if (!glist) {
const result = await insertList({ ...req,
query: { user: event.UID },
body: {
name: `${event.TemplateData.groupListPrefix} ${event.TemplateDisplay} ${event.Display}`,
tag: ['participant', 'group', 'event'],
description: `<p>${group.Title} ${group.Display}</p>`,
},
}, group.UID, 'list');
glist = result.result.UID;
}
const result = await query(`
SELECT filter.UID FROM ObjectBase AS filter
INNER JOIN Links ON (Links.UID = filter.UID AND Links.Type = 'list')
WHERE filter.UIDBelongsTo = ? AND Links.UIDTarget = ?
`, [UUID2hex(glist), UUID2hex(mainList.UID)], { log: false });
if (result.length === 0) {
await addFilter({
params: {
target: mainList.UID,
source: glist,
type: 'include'
},
body: { person: { all: null }, extern: { all: null } },
session: req.session
});
}
}
};
if (req.params.UID === 'all') {
const events = await query(`
SELECT event.UID FROM ObjectBase AS event
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type = 'event')
INNER JOIN ObjectBase AS eventT ON (eventT.UID = Links.UIDTarget)
WHERE eventT.Type = 'eventT' AND eventT.UIDBelongsTo = ?
`, [UUID2hex(req.session.root)], { log: false });
for (const event of events) {
await fixLists(event.UID);
}
} else {
const UIDevent = UUID2hex(req.params.UID);
if (UIDevent) {
await fixLists(UIDevent);
} else {
return res.json({ success: false, message: 'You have to supply a UUID of an event, or all' });
}
}
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Deletes all events
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const deleteEvents = async (req, res) => {
try {
await query(`
DELETE ObjectBase, Links, Member FROM ObjectBase
INNER JOIN Links ON (Links.UID = ObjectBase.UID OR ObjectBase.UID = Links.UIDTarget)
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
WHERE ObjectBase.Type = 'event'
`,[]);
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Restores a person from historical data
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const restorePerson = async (req, res) => {
try {
let asOf = `FOR SYSTEM_TIME AS OF FROM_UNIXTIME(${req.params.timestamp})`;
const pObject = await query(`
SELECT UID FROM ObjectBase ${asOf}
WHERE UID = ? AND Type IN('person','extern')
`, [UUID2hex(req.params.UID)]);
if (pObject.length !== 1) {
return res.json({
success: false,
timestamp: req.params.timestamp,
UID: req.params.UID,
message: 'no person or external person found with matching UID and timestamp'
});
}
const missingObjects = await query(`
SELECT old.UID, old.Type, old.UIDBelongsTo, old.Title, old.SortName, old.dindex, old.hierarchie, old.stage, old.gender, old.Data
FROM ObjectBase ${asOf} AS old
LEFT JOIN ObjectBase AS exist ON (exist.UID = old.UID AND exist.Type IN('extern','person','entry','achievement','guest','job','eventJob'))
WHERE old.UIDBelongsTo = ? AND old.Type IN('extern','person','entry','achievement','guest','job', 'eventJob') AND exist.UID IS NULL
`, [UUID2hex(req.params.UID)]);
let result = { success: true, insertResults: { objects: null, links: null } };
if (missingObjects.length > 0) {
result.insertResults.objects = await query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,SortName, dindex,hierarchie,stage,gender,Data)
VALUES(?,?,?,?,?,?,?,?,?,?)
`, missingObjects.map(o => Object.values(o)), { batch: true });
const missingLinks = await query(`
SELECT old.UID, old.Type, old.UIDTarget FROM Links ${asOf} AS old
LEFT JOIN Links AS exist ON (exist.UID = old.UID AND exist.Type = old.Type AND exist.UIDTarget = old.UIDTarget)
WHERE old.UID IN (?) OR old.UIDTarget IN (?)
`, [missingObjects.map(o => o.UID), missingObjects.map(o => o.UID)]);
if (missingLinks.length > 0) {
result.insertResults.links = await query(`
INSERT INTO Links (UID,Type,UIDTarget)
VALUES (?,?,?)
`, missingLinks.map(l => Object.values(l)), { batch: true });
}
const selection = missingObjects.filter(o => ['person', 'extern', 'job', 'guest'].includes(o.Type)).map(o => o.UID);
if (selection.length > 0) {
const groups = await query(`
SELECT ObjectBase.UID, Links.UID AS oUID FROM Links
INNER JOIN ObjectBase ON (ObjectBase.UID = Links.UIDTarget AND Links.Type IN ('member','memberA'))
WHERE ObjectBase.Type IN ('group','ggroup') AND Links.UID IN(?)
`, [selection]);
for (const group of groups) {
await addVisibility(group.oUID, group.UID);
}
}
}
res.json(result);
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Processes SEPA mandate data for family fees
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const processLastschrift = async (req, res) => {
try {
// Get all persons/extern with first account = familyFees
const persons = await query(`
SELECT Member.UID, Member.Data
FROM Member
INNER JOIN ObjectBase ON (ObjectBase.UID = Member.UID)
WHERE JSON_VALUE(Member.Data,'$.accounts[0].type') = 'familyFees'
AND JSON_QUERY(Member.Data,'$.accounts[0].SEPA') IS NULL
`, [], { cast: ['json'] });
const sepaDate = Math.floor(Date.now() / 1000);
// Add SEPA data to accounts
for (const person of persons) {
const [{ UID }] = await query(`SELECT UIDV1() AS UID`, []);
await query(`UPDATE Member SET Data = ? WHERE UID = ?`, [
JSON.stringify({
...person.Data,
accounts: person.Data.accounts.map((a, Index) =>
Index > 0 ? a : {
...a,
SEPA: {
date: sepaDate,
mandatNo: UID.toString('base64')
}
}
)
}),
person.UID
]);
}
// Check for accounts without IBANowner and set them to family
const persons2 = await query(`
SELECT Member.UID, Member.Data
FROM Member
INNER JOIN ObjectBase ON (ObjectBase.UID = Member.UID)
WHERE JSON_VALUE(Member.Data,'$.accounts[0].type') = 'familyFees'
AND (JSON_VALUE(Member.Data,'$.accounts[0].IBANowner') IS NULL
OR JSON_VALUE(Member.Data,'$.accounts[0].IBANowner') = '')
`, [], { cast: ['json'] });
for (const person of persons2) {
await query(`UPDATE Member SET Data = ? WHERE UID = ?`, [
JSON.stringify({
...person.Data,
accounts: person.Data.accounts.map((a, Index) =>
Index > 0 ? a : { ...a, type: 'family' }
)
}),
person.UID
]);
}
res.json({ success: true });
} catch (e) {
errorLoggerUpdate(e);
res.status(500).json({ success: false, error: e.message });
}
};
/**
* Create a new database
*
* Endpoint to initialize a new database schema.
* Protected by employee rights.
*
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const createDB = async (req, res) => {
try {
if (!req.body.database) {
throw new Error('Database name is required');
}
await configCreateDB(req.body.database);
res.status(200).json({
success: true,
message: 'Database created successfully',
database: req.body.database
});
} catch (error) {
errorLoggerUpdate(error);
res.status(500).json({
success: false,
message: error.message || 'Failed to create database'
});
}
};
/**
* Regenerate AI embeddings for all objects of a specific type
*
* Endpoint to regenerate embeddings for person, extern, guest, or job types.
* Processes objects in batches with single API call per batch for cost efficiency.
*
* @param {ExpressRequestAuthorized} req - Express request object
* @param {ExpressResponse} res - Express response object
*/
export const regenerateEmbeddings = async (req, res) => {
try {
const type = req.params.type;
const validTypes = ['person', 'extern', 'guest', 'job'];
if (!validTypes.includes(type)) {
return res.status(400).json({
success: false,
message: `Invalid type. Must be one of: ${validTypes.join(', ')}`
});
}
sendProgressStatus(req.session.root, {
action: 'start',
text: `Starting embedding regeneration for ${type}...`,
progress: 0
});
// Query all objects of the specified type with Title from ObjectBase
const objects = await query(`
SELECT ObjectBase.UID, ObjectBase.Type, ObjectBase.Title, Member.Data
FROM ObjectBase
INNER JOIN Member ON (Member.UID = ObjectBase.UID)
INNER JOIN Links ON (Links.UID = ObjectBase.UID AND Links.Type IN ('member', 'memberA'))
WHERE ObjectBase.Type = ? AND Links.UIDTarget = ?
`, [type, UUID2hex(req.session.root)], { cast: ['json'] });
const total = objects.length;
const BATCH_SIZE = 50; // Process 50 objects per API call
let processed = 0;
let succeeded = 0;
let failed = 0;
const currentModel = EMBEDDING_CONFIG.model;
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Found ${total} ${type} objects to process`,
progress: 0
});
// Process in batches with single API call per batch
for (let i = 0; i < objects.length; i += BATCH_SIZE) {
const batch = objects.slice(i, i + BATCH_SIZE);
try {
// Step 1: Prepare embedding texts for all objects in batch
const batchData = batch.map(obj => {
// Extract relevant fields like updatePersonEmbedding does
const eData = { Title: obj.Title };
['email','phone','address','accounts','firstName','lastName'].forEach((field) => {
if (obj.Data[field]) eData[field] = obj.Data[field];
});
// Sanitize PII
const { embeddingText } = sanitizePII(eData, { keepNames: true });
return {
uid: obj.UID,
text: embeddingText,
type: obj.Type
};
});
// Step 2: Generate ALL embeddings in ONE API call
const texts = batchData.map(item => item.text);
const embeddings = await generateEmbeddings(texts);
// Step 3: Store embeddings in database individually
for (let j = 0; j < batchData.length; j++) {
try {
const { uid, text, type } = batchData[j];
const embedding = embeddings[j];
const contentHash = calculateContentHash(text);
const embeddingBuffer = Buffer.from(embedding.buffer);
// Check if embedding already exists and is up to date
const existing = await query(
'SELECT ContentHash FROM AIEmbeddings WHERE UID = ?',
[uid]
);
if (existing.length > 0 && Buffer.compare(existing[0].ContentHash, contentHash) === 0) {
// Embedding unchanged, skip update
succeeded++;
continue;
}
// Insert or update embedding
await query(`
INSERT INTO AIEmbeddings (UID, EntityType, EmbeddingModel, EmbeddingDimension, Embedding, ContentHash, UIDOrganization)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
EntityType = VALUES(EntityType),
EmbeddingModel = VALUES(EmbeddingModel),
EmbeddingDimension = VALUES(EmbeddingDimension),
Embedding = VALUES(Embedding),
ContentHash = VALUES(ContentHash),
UIDOrganization = VALUES(UIDOrganization)
`, [
uid,
type,
currentModel.name,
currentModel.dimension,
embeddingBuffer,
contentHash,
UUID2hex(req.session.root)
]);
succeeded++;
} catch (error) {
failed++;
errorLoggerUpdate(error);
}
}
} catch (error) {
// Batch failed entirely (e.g., API error)
failed += batch.length;
errorLoggerUpdate(error);
}
processed += batch.length;
// Send progress update after each batch
sendProgressStatus(req.session.root, {
action: 'progress',
text: `Processed ${processed}/${total} objects (${succeeded} succeeded, ${failed} failed)`,
current: processed,
total: total,
progress: Math.round((processed / total) * 100)
});
}
sendProgressStatus(req.session.root, {
action: 'finished',
text: `Embedding regeneration completed: ${succeeded} succeeded, ${failed} failed`,
progress: 100
});
res.status(200).json({
success: true,
message: `Embeddings regenerated for ${type}`,
processed: total,
succeeded: succeeded,
failed: failed
});
} catch (error) {
sendProgressStatus(req.session.root, {
action: 'error',
text: `Error: ${error.message}`
});
errorLoggerUpdate(error);
res.status(500).json({
success: false,
message: error.message || 'Failed to regenerate embeddings'
});
}
};