25563565134

Committed 08 May 2026 03:16PM UTC coverage: 86.873% (+0.2%) from 86.702%

Build # 25563565134

Build Type

push

github

Committed by

ClemRz

Commit Message

fix(entrance): resolve permanent delete FK violations and preserve audit history

- Parallelize sub-entity deletes for ~100x performance improvement
- Make notification fire-and-forget to unblock response
- Fix FK violations: clean up exit references, junction tables, and
  history table FKs using raw SQL (Waterline silently fails on
  composite-PK models)
- Preserve h_ rows for auditability instead of deleting them
- Add SQL migration to drop h_* parent FK constraints that block
  permanent deletes (aligns dev/test with production behavior)
- Add structured audit logging with entity IDs
- Add comprehensive auditability test covering all entity types,
  junction tables, and cave cascade
- Fix cave delete: use raw SQL for junction cleanup, fire-and-forget
  notifications, parallelize operations
- Extract subEntityDelete to module scope, fix entranceId type coercion

Coverage Stats

3038 of 3628 branches covered (83.74%)

Branch coverage included in aggregate %.

34 of 37 new or added lines in 4 files covered. (91.89%)

2 existing lines in 1 file now uncovered.

6260 of 7075 relevant lines covered (88.48%)

53.64 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

93.81

/api/services/RecentChangeService.js

const CommonService = require('./CommonService');

const GROUP_MAX_TIME_DIFF_S = 60 * 60 * 6; // 6 Hours

// Dashboard only needs a short lookback; 7 days covers the typical review cycle
// while keeping the query fast on the unpartitioned t_last_change table.
const RECENT_CHANGES_WINDOW_DAYS = 7;
// Hard cap to avoid oversized payloads — the dashboard is a summary, not a full audit log.
const RECENT_CHANGES_LIMIT = 500;

async function removeOlderChanges() {
  const query = `DELETE FROM t_last_change WHERE date_change < current_timestamp - interval '1 month';`;
  await CommonService.query(query);
}

// When creating a cave, entrance, massif, document or grotto the name is created after the entity
// So the name is updated afterward
async function setNameCreate(entityType, entityId, authorId, name) {
  const query = `
  UPDATE t_last_change
  SET name = $1
  WHERE type_entity = $2 AND type_change = 'create' AND id_entity = $3 AND id_author = $4 AND date_change > current_timestamp - interval '1 minute';
  `;
  await CommonService.query(query, [name, entityType, entityId, authorId]);
}

// When deleting / restoring an entity, the author is not historized, so it is updated afterward
async function setDeleteRestoreAuthor(
  changeType,
  entityType,
  entityId,
  authorId
) {
  const query = `
  UPDATE t_last_change
  SET id_author = $4
  WHERE type_entity = $2 AND type_change = $1 AND id_entity = $3 AND date_change > current_timestamp - interval '1 minute';
  `;
  await CommonService.query(query, [
    changeType,
    entityType,
    entityId,
    authorId,
  ]);
}

// To make the change list more relevant we groups change event when they are from the same author and about the same entity
function groupChanges(changes) {
  const authorChanges = {};
  // Grouping order:
  // - author
  // - related entity
  // - time
  // Change type 'create' or 'restore' have priority over 'update'
  // Change type 'delete' is never grouped

  const createNewGroupFromChange = (c) => ({
    date: c.date_change,
    authorId: c.id_author,
    author: c.nickname,
    mainEntityType: c.type_related_entity ?? c.type_entity,
    mainEntityId: c.id_related_entity ?? c.id_entity,
    mainAction: c.id_related_entity ? null : c.type_change, // Null in case it is a change on a sub entity
    subEntityTypes: c.id_related_entity ? [c.type_entity] : [],
    subAction: c.id_related_entity ? c.type_change : null, // Null when no sub entities
    name: c.name, // Can be the real entity name or the related entity name
  });

  const addChangeToExistingGroup = (g, c) => {
    if (
      !c.id_related_entity &&
      g.mainAction === 'update' &&
      ['create', 'restore'].includes(c.type_change)
    )
      g.mainAction = c.type_change; // eslint-disable-line no-param-reassign
    if (c.id_related_entity && !g.subEntityTypes.includes(c.type_entity))
      g.subEntityTypes.push(c.type_entity);
    if (c.id_related_entity && g.subAction !== c.type_change)
      g.subAction = 'change'; // eslint-disable-line no-param-reassign
  };

  for (const change of changes) {
    if (!authorChanges[change.id_author]) {
      authorChanges[change.id_author] = [createNewGroupFromChange(change)];
      continue; // eslint-disable-line no-continue
    }

    const authorsChanges = authorChanges[change.id_author];
    const previousChangeForThisEntity = authorsChanges.find(
      (e) =>
        e.mainEntityId === (change.id_related_entity ?? change.id_entity) &&
        e.mainEntityType === (change.type_related_entity ?? change.type_entity)
    );

    if (
      !previousChangeForThisEntity ||
      Math.abs(previousChangeForThisEntity.date - change.date_change) >
        GROUP_MAX_TIME_DIFF_S * 1000 ||
      change.type_change === 'delete' ||
      previousChangeForThisEntity.mainAction === 'delete'
    ) {
      authorsChanges.unshift(createNewGroupFromChange(change));
      continue; // eslint-disable-line no-continue
    }
    addChangeToExistingGroup(previousChangeForThisEntity, change);
  }

  const allGroups = Object.values(authorChanges).flat();
  return allGroups.sort((a, b) => b.date - a.date);
}

async function getRecent() {
  // The t_last_change table is populated by trigger on the other main tables
  const query = `
  SELECT tbl.*, author.nickname FROM t_last_change tbl
  LEFT JOIN t_caver author ON tbl.id_author = author.id
  WHERE tbl.date_change > current_timestamp - interval '${RECENT_CHANGES_WINDOW_DAYS} days'
  ORDER BY date_change DESC
  LIMIT ${RECENT_CHANGES_LIMIT}
  `;
  const rep = await CommonService.query(query);

  // When creating/updating a entrance the associated cave is also created/updated (when not part of a network)
  // As cave changes are duplicate we filter out them
  const changes = rep.rows.filter((e, i, a) => {
    if (e.type_entity !== 'cave') return true;

    // Is the cave change is after the entrance change ?
    if (
      i < a.length - 2 &&
      a[i + 1].date_change - e.date_change < 5000 &&
      a[i + 1].id_author === e.id_author &&
      a[i + 1].type_entity === 'entrance'
    )
      return false;

    // Is the cave change is before the entrance change ?
    if (
      i > 0 &&
      e.date_change - a[i - 1].date_change < 5000 &&
      a[i - 1].id_author === e.id_author &&
      a[i - 1].type_entity === 'entrance'
    )
      return false;

    return true;
  });

  // 5% chance to also remove older changes
  if (Math.random() < 0.05) removeOlderChanges();

  return groupChanges(changes);
}

module.exports = {
  getRecent,
  setNameCreate,
  setDeleteRestoreAuthor,
};

1	const CommonService = require('./CommonService');	8✔
2
3	const GROUP_MAX_TIME_DIFF_S = 60 * 60 * 6; // 6 Hours	8✔
4
5	// Dashboard only needs a short lookback; 7 days covers the typical review cycle
6	// while keeping the query fast on the unpartitioned t_last_change table.
7	const RECENT_CHANGES_WINDOW_DAYS = 7;	8✔
8	// Hard cap to avoid oversized payloads — the dashboard is a summary, not a full audit log.
9	const RECENT_CHANGES_LIMIT = 500;	8✔
10
11	async function removeOlderChanges() {
UNCOV 12	const query = `DELETE FROM t_last_change WHERE date_change < current_timestamp - interval '1 month';`;	×
UNCOV 13	await CommonService.query(query);	×
14	}
15
16	// When creating a cave, entrance, massif, document or grotto the name is created after the entity
17	// So the name is updated afterward
18	async function setNameCreate(entityType, entityId, authorId, name) {
19	const query = `	32✔
20	UPDATE t_last_change
21	SET name = $1
22	WHERE type_entity = $2 AND type_change = 'create' AND id_entity = $3 AND id_author = $4 AND date_change > current_timestamp - interval '1 minute';
23	`;
24	await CommonService.query(query, [name, entityType, entityId, authorId]);	32✔
25	}
26
27	// When deleting / restoring an entity, the author is not historized, so it is updated afterward
28	async function setDeleteRestoreAuthor(
29	changeType,
30	entityType,
31	entityId,
32	authorId
33	) {
34	const query = `	29✔
35	UPDATE t_last_change
36	SET id_author = $4
37	WHERE type_entity = $2 AND type_change = $1 AND id_entity = $3 AND date_change > current_timestamp - interval '1 minute';
38	`;
39	await CommonService.query(query, [	29✔
40	changeType,
41	entityType,
42	entityId,
43	authorId,
44	]);
45	}
46
47	// To make the change list more relevant we groups change event when they are from the same author and about the same entity
48	function groupChanges(changes) {
49	const authorChanges = {};	22✔
50	// Grouping order:
51	// - author
52	// - related entity
53	// - time
54	// Change type 'create' or 'restore' have priority over 'update'
55	// Change type 'delete' is never grouped
56
57	const createNewGroupFromChange = (c) => ({	22✔
58	date: c.date_change,
59	authorId: c.id_author,
60	author: c.nickname,
61	mainEntityType: c.type_related_entity ?? c.type_entity,	23✔
62	mainEntityId: c.id_related_entity ?? c.id_entity,	23✔
63	mainAction: c.id_related_entity ? null : c.type_change, // Null in case it is a change on a sub entity	13✔
64	subEntityTypes: c.id_related_entity ? [c.type_entity] : [],	13✔
65	subAction: c.id_related_entity ? c.type_change : null, // Null when no sub entities	13✔
66	name: c.name, // Can be the real entity name or the related entity name
67	});
68
69	const addChangeToExistingGroup = (g, c) => {	22✔
70	if (	4!
71	!c.id_related_entity &&	6!
72	g.mainAction === 'update' &&
73	['create', 'restore'].includes(c.type_change)
74	)
75	g.mainAction = c.type_change; // eslint-disable-line no-param-reassign	×
76	if (c.id_related_entity && !g.subEntityTypes.includes(c.type_entity))	4✔
77	g.subEntityTypes.push(c.type_entity);	1✔
78	if (c.id_related_entity && g.subAction !== c.type_change)	4✔
79	g.subAction = 'change'; // eslint-disable-line no-param-reassign	2✔
80	};
81
82	for (const change of changes) {	22✔
83	if (!authorChanges[change.id_author]) {	17✔
84	authorChanges[change.id_author] = [createNewGroupFromChange(change)];	11✔
85	continue; // eslint-disable-line no-continue	11✔
86	}
87
88	const authorsChanges = authorChanges[change.id_author];	6✔
89	const previousChangeForThisEntity = authorsChanges.find(	6✔
90	(e) =>
91	e.mainEntityId === (change.id_related_entity ?? change.id_entity) &&	6✔
92	e.mainEntityType === (change.type_related_entity ?? change.type_entity)	10✔
93	);
94
95	if (	6✔
96	!previousChangeForThisEntity \|\|	22✔
97	Math.abs(previousChangeForThisEntity.date - change.date_change) >
98	GROUP_MAX_TIME_DIFF_S * 1000 \|\|
99	change.type_change === 'delete' \|\|
100	previousChangeForThisEntity.mainAction === 'delete'
101	) {
102	authorsChanges.unshift(createNewGroupFromChange(change));	2✔
103	continue; // eslint-disable-line no-continue	2✔
104	}
105	addChangeToExistingGroup(previousChangeForThisEntity, change);	4✔
106	}
107
108	const allGroups = Object.values(authorChanges).flat();	22✔
109	return allGroups.sort((a, b) => b.date - a.date);	22✔
110	}
111
112	async function getRecent() {
113	// The t_last_change table is populated by trigger on the other main tables
114	const query = `	22✔
115	SELECT tbl.*, author.nickname FROM t_last_change tbl
116	LEFT JOIN t_caver author ON tbl.id_author = author.id
117	WHERE tbl.date_change > current_timestamp - interval '${RECENT_CHANGES_WINDOW_DAYS} days'
118	ORDER BY date_change DESC
119	LIMIT ${RECENT_CHANGES_LIMIT}
120	`;
121	const rep = await CommonService.query(query);	22✔
122
123	// When creating/updating a entrance the associated cave is also created/updated (when not part of a network)
124	// As cave changes are duplicate we filter out them
125	const changes = rep.rows.filter((e, i, a) => {	22✔
126	if (e.type_entity !== 'cave') return true;	19✔
127
128	// Is the cave change is after the entrance change ?
129	if (	3✔
130	i < a.length - 2 &&	6✔
131	a[i + 1].date_change - e.date_change < 5000 &&
132	a[i + 1].id_author === e.id_author &&
133	a[i + 1].type_entity === 'entrance'
134	)
135	return false;	1✔
136
137	// Is the cave change is before the entrance change ?
138	if (	2✔
139	i > 0 &&	5✔
140	e.date_change - a[i - 1].date_change < 5000 &&
141	a[i - 1].id_author === e.id_author &&
142	a[i - 1].type_entity === 'entrance'
143	)
144	return false;	1✔
145
146	return true;	1✔
147	});
148
149	// 5% chance to also remove older changes
150	if (Math.random() < 0.05) removeOlderChanges();	22!
151
152	return groupChanges(changes);	22✔
153	}
154
155	module.exports = {	8✔
156	getRecent,
157	setNameCreate,
158	setDeleteRestoreAuthor,
159	};

GrottoCenter / grottocenter-api / 25563565134

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous