Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1import { type ItemIdentifier } from '@roostorg/types';
2import { sql, type Kysely } from 'kysely';
3
4import { inject, type Dependencies } from '../../iocContainer/index.js';
5import { getUtcDateOnlyString, WEEK_MS } from '../../utils/time.js';
6import {
7 warehouseDateToDate,
8 warehouseDateToDateOnlyString,
9 type DataWarehousePublicSchema,
10} from '../../storage/dataWarehouse/warehouseSchema.js';
11
12const RULE_EXECUTIONS_COLUMNS = [
13 'ds',
14 'ts',
15 'item_type_name',
16 'item_type_id',
17 'item_id',
18 'item_data',
19 'result',
20 'environment',
21 'passed',
22 'rule_id',
23 'rule',
24 'policy_names',
25 'tags',
26] as const;
27
28class UserHistoryQueries {
29 constructor(
30 private readonly dialect: Dependencies['DataWarehouseDialect'],
31 private readonly warehouse: Dependencies['DataWarehouse'],
32 ) {}
33
34 private get kysely(): Kysely<DataWarehousePublicSchema> {
35 return this.dialect.getKyselyInstance();
36 }
37
38 async getUserRuleExecutionsHistory(
39 orgId: string,
40 userItemIdentifier: ItemIdentifier,
41 ) {
42 const lowercaseUserId = userItemIdentifier.id.toLowerCase();
43
44 // eslint-disable-next-line @typescript-eslint/no-explicit-any
45 const kyselyAny = this.kysely as unknown as Kysely<Record<string, any>>;
46 const makeQuery = (startDate: Date, endDate: Date) => {
47 const startDateString = getUtcDateOnlyString(startDate);
48 const endDateString = getUtcDateOnlyString(endDate);
49 return kyselyAny
50 .selectFrom('RULE_EXECUTIONS')
51 .select([...RULE_EXECUTIONS_COLUMNS])
52 .where('org_id', '=', orgId)
53 .where('passed', '=', true)
54 .where(({ ref, fn, eb, and }) =>
55 and([
56 eb('item_creator_type_id', '=', userItemIdentifier.typeId),
57 eb(fn('LOWER', ['item_creator_id']), '=', lowercaseUserId),
58 sql`${ref('ds')} BETWEEN ${startDateString} AND ${endDateString}`,
59 ]),
60 )
61 .orderBy('environment', 'desc')
62 .orderBy('passed', 'desc')
63 .orderBy('ts', 'desc');
64 };
65 const now = Date.now();
66 const dateRanges = Array.from(Array(6), (_, i) => [
67 new Date(now - (i + 1) * WEEK_MS * 2),
68 new Date(now - i * WEEK_MS * 2),
69 ]);
70 const results = await Promise.all(
71 dateRanges.map(async ([startDate, endDate]) =>
72 makeQuery(startDate, endDate).execute(),
73 ),
74 );
75 const rows = results.flat() as Array<Record<string, unknown>>;
76 return rows.map((result) => ({
77 date: warehouseDateToDateOnlyString(
78 result.ds as Parameters<typeof warehouseDateToDateOnlyString>[0],
79 ),
80 ts: warehouseDateToDate(
81 result.ts as Parameters<typeof warehouseDateToDate>[0],
82 ),
83 itemTypeName: result.item_type_name as string,
84 itemTypeId: result.item_type_id as string,
85 contentId: result.item_id as string,
86 content: result.item_data,
87 result: result.result ?? null,
88 environment: result.environment as string,
89 passed: result.passed as boolean,
90 ruleId: result.rule_id as string,
91 ruleName: result.rule as string,
92 policies: (result.policy_names ?? []) as string[],
93 tags: (result.tags ?? []) as string[],
94 }));
95 }
96}
97
98export default inject(
99 ['DataWarehouseDialect', 'DataWarehouse'],
100 UserHistoryQueries,
101);
102export { type UserHistoryQueries };