StellarSplit/backend/src/migrations/1737684000000-CreateActivitiesTable.ts at main · ghzhost/StellarSplit · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
import { MigrationInterface, QueryRunner, Table, TableIndex } from "typeorm";

export class CreateActivitiesTable1737684000000 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(
            new Table({
                name: "activities",
                columns: [
                    {
                        name: "id",
                        type: "uuid",
                        isPrimary: true,
                        default: "gen_random_uuid()",
                    },
                    {
                        name: "userId",
                        type: "varchar",
                        isNullable: false,
                    },
                    {
                        name: "activityType",
                        type: "enum",
                        enum: [
                            "split_created",
                            "participant_added",
                            "payment_made",
                            "payment_received",
                            "split_completed",
                            "reminder_sent",
                            "split_edited",
                        ],
                        isNullable: false,
                    },
                    {
                        name: "splitId",
                        type: "uuid",
                        isNullable: true,
                    },
                    {
                        name: "metadata",
                        type: "jsonb",
                        default: "'{}'",
                        isNullable: false,
                    },
                    {
                        name: "isRead",
                        type: "boolean",
                        default: false,
                        isNullable: false,
                    },
                    {
                        name: "createdAt",
                        type: "timestamp",
                        default: "CURRENT_TIMESTAMP",
                        isNullable: false,
                    },
                ],
            }),
            true,
        );

        // Create composite index for userId and createdAt (most common query)
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_userId_createdAt",
                columnNames: ["userId", "createdAt"],
            }),
        );

        // Create composite index for userId and activityType (filtering)
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_userId_activityType",
                columnNames: ["userId", "activityType"],
            }),
        );

        // Create composite index for userId and isRead (unread count queries)
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_userId_isRead",
                columnNames: ["userId", "isRead"],
            }),
        );

        // Create index for splitId (filtering by split)
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_splitId",
                columnNames: ["splitId"],
            }),
        );

        // Create standalone index for userId
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_userId",
                columnNames: ["userId"],
            }),
        );

        // Create standalone index for activityType
        await queryRunner.createIndex(
            "activities",
            new TableIndex({
                name: "idx_activities_activityType",
                columnNames: ["activityType"],
            }),
        );
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropTable("activities", true);
    }
}