17914181837

Committed 22 Sep 2025 11:31AM UTC coverage: 57.332%. First build

Build # 17914181837

Build Type

Pull #247

github

Committed by

altergui

Commit Message

db: implement migrations

Pull Request Pull Request #247: db: implement migrations

Run Details

220 of 349 new or added lines in 6 files covered. (63.04%)

5794 of 10106 relevant lines covered (57.33%)

33.32 hits per line

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

73.58

/migrations/0001_initial_indexes.go

package migrations

import (
        "context"
        "fmt"

        "go.mongodb.org/mongo-driver/bson"
        "go.mongodb.org/mongo-driver/mongo"
        "go.mongodb.org/mongo-driver/mongo/options"
)

func init() {
        addMigration(1, "initial_indexes", upInitialIndexes, downInitialIndexes)
}

func upInitialIndexes(ctx context.Context, database *mongo.Database) error {
        // Get collections
        users := database.Collection("users")
        verifications := database.Collection("verifications")
        organizationInvites := database.Collection("organizationInvites")
        orgMembers := database.Collection("orgMembers")
        censusParticipants := database.Collection("censusParticipants")
        cspTokensStatus := database.Collection("cspTokensStatus")
        jobs := database.Collection("jobs")

        // create an index for the 'email' field on users
        if _, err := users.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys:    bson.D{{Key: "email", Value: 1}}, // 1 for ascending order
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on email for users: %w", err)
        }

        // create an index for the ('code', 'type') tuple on user verifications (must be unique)
        if _, err := verifications.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "code", Value: 1}, // 1 for ascending order
                        {Key: "type", Value: 1}, // 1 for ascending order
                },
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on code and type for verifications: %w", err)
        }

        if _, err := organizationInvites.Indexes().CreateMany(ctx, []mongo.IndexModel{
                // create an index for the 'invitationCode' field on organization invites (must be unique)
                {
                        Keys:    bson.D{{Key: "invitationCode", Value: 1}}, // 1 for ascending order
                        Options: options.Index().SetUnique(true),
                },
                // create a ttl index for the 'expiration' field on organization invites
                {
                        Keys:    bson.D{{Key: "expiration", Value: 1}}, // 1 for ascending order
                        Options: options.Index().SetExpireAfterSeconds(0),
                },
                // create an index to ensure that the tuple ('organizationAddress', 'newUserEmail') is unique
                {
                        Keys: bson.D{
                                {Key: "organizationAddress", Value: 1}, // 1 for ascending order
                                {Key: "newUserEmail", Value: 1},        // 1 for ascending order
                        },
                        Options: options.Index().SetUnique(true),
                },
        }); err != nil {
                return fmt.Errorf("failed to create many indexes for organization invites: %w", err)
        }

        // create an index for the id field on organization members
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "_id", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on _id for orgMembers: %w", err)
        }

        // create an index for the orgAddress/id field on organization members
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "orgAddress", Value: 1}, // 1 for ascending order
                        {Key: "_id", Value: 1},        // 1 for ascending order
                },
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress and id for orgMembers: %w", err)
        }

        // create an index for the orgAddress
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "orgAddress", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress for orgMembers: %w", err)
        }

        // create an index for the tuple orgAddress and memberNumber
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "orgAddress", Value: 1},   // 1 for ascending order
                        {Key: "memberNumber", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress and memberNumber for orgMembers: %w", err)
        }

        // create an index for the tuple orgAddress and email on organization members
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "orgAddress", Value: 1}, // 1 for ascending order
                        {Key: "email", Value: 1},      // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress and email for orgMembers: %w", err)
        }

        // create an index for the tuple orgAddress and hashedPhone on organization members
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "orgAddress", Value: 1},  // 1 for ascending order
                        {Key: "hashedPhone", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress and hashedPhone for orgMembers: %w", err)
        }

        // index for the censusId
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "censusId", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on censusId for censusParticipants: %w", err)
        }

        // index for the participantID
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "participantID", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on participantID for censusParticipants: %w", err)
        }

        // index for the censusId and participantID tuple
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "censusId", Value: 1},      // 1 for ascending order
                        {Key: "participantID", Value: 1}, // 1 for ascending order
                },
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on censusId and participantID for censusParticipants: %w", err)
        }

        // index for the censusId and loginhash
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "censusId", Value: 1},  // 1 for ascending order
                        {Key: "loginHash", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on censusId and loginHash for censusParticipants: %w", err)
        }
        // index for the censusId and loginhashPhone
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "censusId", Value: 1},       // 1 for ascending order
                        {Key: "loginHashPhone", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on censusId and loginHashPhone for censusParticipants: %w", err)
        }
        // index for the censusId and loginhashEmail
        if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "censusId", Value: 1},       // 1 for ascending order
                        {Key: "loginHashEmail", Value: 1}, // 1 for ascending order
                },
        }); err != nil {
                return fmt.Errorf("failed to create index on censusId and loginHashEmail for censusParticipants: %w", err)
        }

        // unique index over userID and processID
        if _, err := cspTokensStatus.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "userid", Value: 1},
                        {Key: "processid", Value: 1},
                },
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on userid and processid for cspTokensStatus: %w", err)
        }

        // member properties text index for filtering
        if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{
                        {Key: "email", Value: "text"},
                        {Key: "memberNumber", Value: "text"},
                        {Key: "nationalID", Value: "text"},
                        {Key: "name", Value: "text"},
                        {Key: "surname", Value: "text"},
                        {Key: "birthDate", Value: "text"},
                },
        }); err != nil {
                return fmt.Errorf("failed to create text index on orgMembers: %w", err)
        }

        // Individual indexes for regex search optimization on orgMembers
        // These indexes improve performance for regex queries on individual fields
        orgMemberRegexIndexes := []struct {
                field string
                key   string
        }{
                {"email", "email"},
                {"memberNumber", "memberNumber"},
                {"nationalID", "nationalID"},
                {"name", "name"},
                {"surname", "surname"},
                {"birthDate", "birthDate"},
        }

        for _, idx := range orgMemberRegexIndexes {
                if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{
                        Keys: bson.D{{Key: idx.key, Value: 1}}, // 1 for ascending order
                }); err != nil {
                        return fmt.Errorf("failed to create index on %s for orgMembers: %w", idx.field, err)
                }
        }

        // create an index for the jobId field on jobs (must be unique)
        if _, err := jobs.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys:    bson.D{{Key: "jobId", Value: 1}}, // 1 for ascending order
                Options: options.Index().SetUnique(true),
        }); err != nil {
                return fmt.Errorf("failed to create index on jobId for jobs: %w", err)
        }

        // create an index for the orgAddress field on jobs
        if _, err := jobs.Indexes().CreateOne(ctx, mongo.IndexModel{
                Keys: bson.D{{Key: "orgAddress", Value: 1}}, // 1 for ascending order
        }); err != nil {
                return fmt.Errorf("failed to create index on orgAddress for jobs: %w", err)
        }

        return nil
}

func downInitialIndexes(ctx context.Context, database *mongo.Database) error {
        // Drop all indexes from all collections
        for _, collName := range []string{
                "users",
                "verifications",
                "organizationInvites",
                "orgMembers",
                "censusParticipants",
                "cspTokensStatus",
                "jobs",
        } {
                collection := database.Collection(collName)
                if _, err := collection.Indexes().DropAll(ctx); err != nil {
                        return fmt.Errorf("failed to drop indexes for collection %s: %w", collName, err)
                }
        }

        return nil
}

1	package migrations
2
3	import (
4	"context"
5	"fmt"
6
7	"go.mongodb.org/mongo-driver/bson"
8	"go.mongodb.org/mongo-driver/mongo"
9	"go.mongodb.org/mongo-driver/mongo/options"
10	)
11
12	func init() {	5✔
13	addMigration(1, "initial_indexes", upInitialIndexes, downInitialIndexes)	5✔
14	}	5✔
15
16	func upInitialIndexes(ctx context.Context, database *mongo.Database) error {	106✔
17	// Get collections	106✔
18	users := database.Collection("users")	106✔
19	verifications := database.Collection("verifications")	106✔
20	organizationInvites := database.Collection("organizationInvites")	106✔
21	orgMembers := database.Collection("orgMembers")	106✔
22	censusParticipants := database.Collection("censusParticipants")	106✔
23	cspTokensStatus := database.Collection("cspTokensStatus")	106✔
24	jobs := database.Collection("jobs")	106✔
25		106✔
26	// create an index for the 'email' field on users	106✔
27	if _, err := users.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
28	Keys: bson.D{{Key: "email", Value: 1}}, // 1 for ascending order	106✔
29	Options: options.Index().SetUnique(true),	106✔
30	}); err != nil {	106✔
NEW 31	return fmt.Errorf("failed to create index on email for users: %w", err)	×
NEW 32	}	×
33
34	// create an index for the ('code', 'type') tuple on user verifications (must be unique)
35	if _, err := verifications.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
36	Keys: bson.D{	106✔
37	{Key: "code", Value: 1}, // 1 for ascending order	106✔
38	{Key: "type", Value: 1}, // 1 for ascending order	106✔
39	},	106✔
40	Options: options.Index().SetUnique(true),	106✔
41	}); err != nil {	106✔
NEW 42	return fmt.Errorf("failed to create index on code and type for verifications: %w", err)	×
NEW 43	}	×
44
45	if _, err := organizationInvites.Indexes().CreateMany(ctx, []mongo.IndexModel{	106✔
46	// create an index for the 'invitationCode' field on organization invites (must be unique)	106✔
47	{	106✔
48	Keys: bson.D{{Key: "invitationCode", Value: 1}}, // 1 for ascending order	106✔
49	Options: options.Index().SetUnique(true),	106✔
50	},	106✔
51	// create a ttl index for the 'expiration' field on organization invites	106✔
52	{	106✔
53	Keys: bson.D{{Key: "expiration", Value: 1}}, // 1 for ascending order	106✔
54	Options: options.Index().SetExpireAfterSeconds(0),	106✔
55	},	106✔
56	// create an index to ensure that the tuple ('organizationAddress', 'newUserEmail') is unique	106✔
57	{	106✔
58	Keys: bson.D{	106✔
59	{Key: "organizationAddress", Value: 1}, // 1 for ascending order	106✔
60	{Key: "newUserEmail", Value: 1}, // 1 for ascending order	106✔
61	},	106✔
62	Options: options.Index().SetUnique(true),	106✔
63	},	106✔
64	}); err != nil {	106✔
NEW 65	return fmt.Errorf("failed to create many indexes for organization invites: %w", err)	×
NEW 66	}	×
67
68	// create an index for the id field on organization members
69	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
70	Keys: bson.D{	106✔
71	{Key: "_id", Value: 1}, // 1 for ascending order	106✔
72	},	106✔
73	}); err != nil {	106✔
NEW 74	return fmt.Errorf("failed to create index on _id for orgMembers: %w", err)	×
NEW 75	}	×
76
77	// create an index for the orgAddress/id field on organization members
78	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
79	Keys: bson.D{	106✔
80	{Key: "orgAddress", Value: 1}, // 1 for ascending order	106✔
81	{Key: "_id", Value: 1}, // 1 for ascending order	106✔
82	},	106✔
83	Options: options.Index().SetUnique(true),	106✔
84	}); err != nil {	106✔
NEW 85	return fmt.Errorf("failed to create index on orgAddress and id for orgMembers: %w", err)	×
NEW 86	}	×
87
88	// create an index for the orgAddress
89	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
90	Keys: bson.D{	106✔
91	{Key: "orgAddress", Value: 1}, // 1 for ascending order	106✔
92	},	106✔
93	}); err != nil {	106✔
NEW 94	return fmt.Errorf("failed to create index on orgAddress for orgMembers: %w", err)	×
NEW 95	}	×
96
97	// create an index for the tuple orgAddress and memberNumber
98	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
99	Keys: bson.D{	106✔
100	{Key: "orgAddress", Value: 1}, // 1 for ascending order	106✔
101	{Key: "memberNumber", Value: 1}, // 1 for ascending order	106✔
102	},	106✔
103	}); err != nil {	106✔
NEW 104	return fmt.Errorf("failed to create index on orgAddress and memberNumber for orgMembers: %w", err)	×
NEW 105	}	×
106
107	// create an index for the tuple orgAddress and email on organization members
108	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
109	Keys: bson.D{	106✔
110	{Key: "orgAddress", Value: 1}, // 1 for ascending order	106✔
111	{Key: "email", Value: 1}, // 1 for ascending order	106✔
112	},	106✔
113	}); err != nil {	106✔
NEW 114	return fmt.Errorf("failed to create index on orgAddress and email for orgMembers: %w", err)	×
NEW 115	}	×
116
117	// create an index for the tuple orgAddress and hashedPhone on organization members
118	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
119	Keys: bson.D{	106✔
120	{Key: "orgAddress", Value: 1}, // 1 for ascending order	106✔
121	{Key: "hashedPhone", Value: 1}, // 1 for ascending order	106✔
122	},	106✔
123	}); err != nil {	106✔
NEW 124	return fmt.Errorf("failed to create index on orgAddress and hashedPhone for orgMembers: %w", err)	×
NEW 125	}	×
126
127	// index for the censusId
128	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
129	Keys: bson.D{	106✔
130	{Key: "censusId", Value: 1}, // 1 for ascending order	106✔
131	},	106✔
132	}); err != nil {	106✔
NEW 133	return fmt.Errorf("failed to create index on censusId for censusParticipants: %w", err)	×
NEW 134	}	×
135
136	// index for the participantID
137	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
138	Keys: bson.D{	106✔
139	{Key: "participantID", Value: 1}, // 1 for ascending order	106✔
140	},	106✔
141	}); err != nil {	106✔
NEW 142	return fmt.Errorf("failed to create index on participantID for censusParticipants: %w", err)	×
NEW 143	}	×
144
145	// index for the censusId and participantID tuple
146	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
147	Keys: bson.D{	106✔
148	{Key: "censusId", Value: 1}, // 1 for ascending order	106✔
149	{Key: "participantID", Value: 1}, // 1 for ascending order	106✔
150	},	106✔
151	Options: options.Index().SetUnique(true),	106✔
152	}); err != nil {	106✔
NEW 153	return fmt.Errorf("failed to create index on censusId and participantID for censusParticipants: %w", err)	×
NEW 154	}	×
155
156	// index for the censusId and loginhash
157	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
158	Keys: bson.D{	106✔
159	{Key: "censusId", Value: 1}, // 1 for ascending order	106✔
160	{Key: "loginHash", Value: 1}, // 1 for ascending order	106✔
161	},	106✔
162	}); err != nil {	106✔
NEW 163	return fmt.Errorf("failed to create index on censusId and loginHash for censusParticipants: %w", err)	×
NEW 164	}	×
165	// index for the censusId and loginhashPhone
166	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
167	Keys: bson.D{	106✔
168	{Key: "censusId", Value: 1}, // 1 for ascending order	106✔
169	{Key: "loginHashPhone", Value: 1}, // 1 for ascending order	106✔
170	},	106✔
171	}); err != nil {	106✔
NEW 172	return fmt.Errorf("failed to create index on censusId and loginHashPhone for censusParticipants: %w", err)	×
NEW 173	}	×
174	// index for the censusId and loginhashEmail
175	if _, err := censusParticipants.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
176	Keys: bson.D{	106✔
177	{Key: "censusId", Value: 1}, // 1 for ascending order	106✔
178	{Key: "loginHashEmail", Value: 1}, // 1 for ascending order	106✔
179	},	106✔
180	}); err != nil {	106✔
NEW 181	return fmt.Errorf("failed to create index on censusId and loginHashEmail for censusParticipants: %w", err)	×
NEW 182	}	×
183
184	// unique index over userID and processID
185	if _, err := cspTokensStatus.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
186	Keys: bson.D{	106✔
187	{Key: "userid", Value: 1},	106✔
188	{Key: "processid", Value: 1},	106✔
189	},	106✔
190	Options: options.Index().SetUnique(true),	106✔
191	}); err != nil {	106✔
NEW 192	return fmt.Errorf("failed to create index on userid and processid for cspTokensStatus: %w", err)	×
NEW 193	}	×
194
195	// member properties text index for filtering
196	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
197	Keys: bson.D{	106✔
198	{Key: "email", Value: "text"},	106✔
199	{Key: "memberNumber", Value: "text"},	106✔
200	{Key: "nationalID", Value: "text"},	106✔
201	{Key: "name", Value: "text"},	106✔
202	{Key: "surname", Value: "text"},	106✔
203	{Key: "birthDate", Value: "text"},	106✔
204	},	106✔
205	}); err != nil {	106✔
NEW 206	return fmt.Errorf("failed to create text index on orgMembers: %w", err)	×
NEW 207	}	×
208
209	// Individual indexes for regex search optimization on orgMembers
210	// These indexes improve performance for regex queries on individual fields
211	orgMemberRegexIndexes := []struct {	106✔
212	field string	106✔
213	key string	106✔
214	}{	106✔
215	{"email", "email"},	106✔
216	{"memberNumber", "memberNumber"},	106✔
217	{"nationalID", "nationalID"},	106✔
218	{"name", "name"},	106✔
219	{"surname", "surname"},	106✔
220	{"birthDate", "birthDate"},	106✔
221	}	106✔
222		106✔
223	for _, idx := range orgMemberRegexIndexes {	742✔
224	if _, err := orgMembers.Indexes().CreateOne(ctx, mongo.IndexModel{	636✔
225	Keys: bson.D{{Key: idx.key, Value: 1}}, // 1 for ascending order	636✔
226	}); err != nil {	636✔
NEW 227	return fmt.Errorf("failed to create index on %s for orgMembers: %w", idx.field, err)	×
NEW 228	}	×
229	}
230
231	// create an index for the jobId field on jobs (must be unique)
232	if _, err := jobs.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
233	Keys: bson.D{{Key: "jobId", Value: 1}}, // 1 for ascending order	106✔
234	Options: options.Index().SetUnique(true),	106✔
235	}); err != nil {	106✔
NEW 236	return fmt.Errorf("failed to create index on jobId for jobs: %w", err)	×
NEW 237	}	×
238
239	// create an index for the orgAddress field on jobs
240	if _, err := jobs.Indexes().CreateOne(ctx, mongo.IndexModel{	106✔
241	Keys: bson.D{{Key: "orgAddress", Value: 1}}, // 1 for ascending order	106✔
242	}); err != nil {	106✔
NEW 243	return fmt.Errorf("failed to create index on orgAddress for jobs: %w", err)	×
NEW 244	}	×
245
246	return nil	106✔
247	}
248
NEW 249	func downInitialIndexes(ctx context.Context, database *mongo.Database) error {	×
NEW 250	// Drop all indexes from all collections	×
NEW 251	for _, collName := range []string{	×
NEW 252	"users",	×
NEW 253	"verifications",	×
NEW 254	"organizationInvites",	×
NEW 255	"orgMembers",	×
NEW 256	"censusParticipants",	×
NEW 257	"cspTokensStatus",	×
NEW 258	"jobs",	×
NEW 259	} {	×
NEW 260	collection := database.Collection(collName)	×
NEW 261	if _, err := collection.Indexes().DropAll(ctx); err != nil {	×
NEW 262	return fmt.Errorf("failed to drop indexes for collection %s: %w", collName, err)	×
NEW 263	}	×
264	}
265
NEW 266	return nil	×
267	}

vocdoni / saas-backend / 17914181837

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