1
#########################################################################
2
# This file is part of CoCalc: Copyright © 2020 Sagemath, Inc.
3
# License: MS-RSL – see LICENSE.md for details
4
#########################################################################
5

6
###
7
PostgreSQL -- implementation of all the queries needed for the backend servers
8

9
These are all the non-reactive non-push queries, e.g., adding entries to logs,
10
checking on cookies, creating accounts and projects, etc.
11

12
COPYRIGHT : (c) 2017 SageMath, Inc.
13
LICENSE   : MS-RSL
14
###
15

16
# limit for async.map or async.paralleLimit, esp. to avoid high concurrency when querying in parallel
17
MAP_LIMIT = 5
18

19
async   = require('async')
20

21
random_key = require("random-key")
22

23
misc_node = require('@cocalc/backend/misc_node')
24
misc2_node = require('@cocalc/backend/misc')
25

26
{defaults} = misc = require('@cocalc/util/misc')
27
required = defaults.required
28

29
# IDK why, but if that import line is down below, where the other "./postgres/*" imports are, building manage
30
# fails with: remember-me.ts(15,31): error TS2307: Cannot find module 'async-await-utils/hof' or its corresponding type declarations.
31
{get_remember_me} = require('./postgres/remember-me')
32

33
{SCHEMA, DEFAULT_QUOTAS, PROJECT_UPGRADES, COMPUTE_STATES, RECENT_TIMES, RECENT_TIMES_KEY, site_settings_conf} = require('@cocalc/util/schema')
34

35
{ quota } = require("@cocalc/util/upgrades/quota")
36

37
PROJECT_GROUPS = misc.PROJECT_GROUPS
38

39
read = require('read')
40

41
{PROJECT_COLUMNS, one_result, all_results, count_result, expire_time} = require('./postgres-base')
42

43
# TODO is set_account_info_if_possible used here?!
44
{is_paying_customer, set_account_info_if_possible} = require('./postgres/account-queries')
45
{getStripeCustomerId, syncCustomer} = require('./postgres/stripe')
46

47
{site_license_usage_stats, projects_using_site_license, number_of_projects_using_site_license} = require('./postgres/site-license/analytics')
48
{update_site_license_usage_log} = require('./postgres/site-license/usage-log')
49
{site_license_public_info} = require('./postgres/site-license/public')
50
{site_license_manager_set} = require('./postgres/site-license/manager')
51
{matching_site_licenses, manager_site_licenses} = require('./postgres/site-license/search')
52
{project_datastore_set, project_datastore_get, project_datastore_del} = require('./postgres/project-queries')
53
{permanently_unlink_all_deleted_projects_of_user, unlink_old_deleted_projects} = require('./postgres/delete-projects')
54
{get_all_public_paths, unlist_all_public_paths} = require('./postgres/public-paths')
55
{get_personal_user} = require('./postgres/personal')
56
{set_passport_settings, get_passport_settings, get_all_passport_settings, get_all_passport_settings_cached, create_passport, passport_exists, update_account_and_passport, _passport_key} = require('./postgres/passport')
57
{projects_that_need_to_be_started} = require('./postgres/always-running');
58
{calc_stats} = require('./postgres/stats')
59
{getServerSettings, resetServerSettingsCache, getPassportsCached, setPassportsCached} = require('@cocalc/database/settings/server-settings');
60
{pii_expire} = require("./postgres/pii")
61
passwordHash = require("@cocalc/backend/auth/password-hash").default;
62
registrationTokens = require('./postgres/registration-tokens').default;
63
{updateUnreadMessageCount} = require('./postgres/messages');
64
centralLog = require('./postgres/central-log').default;
65

66
stripe_name = require('@cocalc/util/stripe/name').default;
67

68

69
exports.extend_PostgreSQL = (ext) -> class PostgreSQL extends ext
70
    # write an event to the central_log table
71
    log: (opts) =>
72
        opts = defaults opts,
73
            event : required    # string
74
            value : required    # object
75
            cb    : undefined
76
        try
77
            await centralLog(opts)
78
            opts.cb?()
79
        catch err
80
            opts.cb?(err)
81

82
    uncaught_exception: (err) =>
83
        # call when things go to hell in some unexpected way; at least
84
        # we attempt to record this in the database...
85
        try
86
            @log
87
                event : 'uncaught_exception'
88
                value : {error:"#{err}", stack:"#{err.stack}", host:require('os').hostname()}
89
        catch e
90
            # IT IS CRITICAL THAT uncaught_exception not raise an exception, since if it
91
            # did then we would hit a horrible infinite loop!
92

93
    # dump a range of data from the central_log table
94
    get_log: (opts) =>
95
        opts = defaults opts,
96
            start : undefined     # if not given start at beginning of time
97
            end   : undefined     # if not given include everything until now
98
            log   : 'central_log' # which table to query
99
            event : undefined
100
            where : undefined     # if given, restrict to records with the given json
101
                                  # containment, e.g., {account_id:'...'}, only returns
102
                                  # entries whose value has the given account_id.
103
            cb    : required
104
        @_query
105
            query  : "SELECT * FROM #{opts.log}"
106
            where  :
107
                'time  >= $::TIMESTAMP' : opts.start
108
                'time  <= $::TIMESTAMP' : opts.end
109
                'event  = $::TEXT'      : opts.event
110
                'value @> $::JSONB'     : opts.where
111
            cb     : all_results(opts.cb)
112

113
    # Return every entry x in central_log in the given period of time for
114
    # which x.event==event and x.value.account_id == account_id.
115
    get_user_log: (opts) =>
116
        opts = defaults opts,
117
            start      : undefined
118
            end        : undefined     # if not given include everything until now
119
            event      : 'successful_sign_in'
120
            account_id : required
121
            cb         : required
122
        @get_log
123
            start : opts.start
124
            end   : opts.end
125
            event : opts.event
126
            where : {account_id: opts.account_id}
127
            cb    : opts.cb
128

129
    log_client_error: (opts) =>
130
        opts = defaults opts,
131
            event      : 'event'
132
            error      : 'error'
133
            account_id : undefined
134
            cb         : undefined
135
        # get rid of the entry in 30 days
136
        expire = misc.expire_time(30 * 24 * 60 * 60)
137
        @_query
138
            query  : 'INSERT INTO client_error_log'
139
            values :
140
                'id         :: UUID'      : misc.uuid()
141
                'event      :: TEXT'      : opts.event
142
                'error      :: TEXT'      : opts.error
143
                'account_id :: UUID'      : opts.account_id
144
                'time       :: TIMESTAMP' : 'NOW()'
145
                'expire     :: TIMESTAMP' : expire
146
            cb     : opts.cb
147

148
    webapp_error: (opts) =>
149
        opts = defaults opts,
150
            account_id   : undefined
151
            name         : undefined
152
            message      : undefined
153
            comment      : undefined
154
            stacktrace   : undefined
155
            file         : undefined
156
            path         : undefined
157
            lineNumber   : undefined
158
            columnNumber : undefined
159
            severity     : undefined
160
            browser      : undefined
161
            mobile       : undefined
162
            responsive   : undefined
163
            user_agent   : undefined
164
            smc_version  : undefined
165
            build_date   : undefined
166
            smc_git_rev  : undefined
167
            uptime       : undefined
168
            start_time   : undefined
169
            id           : undefined  # ignored
170
            cb           : undefined
171
        # get rid of the entry in 30 days
172
        expire = misc.expire_time(30 * 24 * 60 * 60)
173
        @_query
174
            query       : 'INSERT INTO webapp_errors'
175
            values      :
176
                'id            :: UUID'      : misc.uuid()
177
                'account_id    :: UUID'      : opts.account_id
178
                'name          :: TEXT'      : opts.name
179
                'message       :: TEXT'      : opts.message
180
                'comment       :: TEXT'      : opts.comment
181
                'stacktrace    :: TEXT'      : opts.stacktrace
182
                'file          :: TEXT'      : opts.file
183
                'path          :: TEXT'      : opts.path
184
                'lineNumber    :: INTEGER'   : opts.lineNumber
185
                'columnNumber  :: INTEGER'   : opts.columnNumber
186
                'severity      :: TEXT'      : opts.severity
187
                'browser       :: TEXT'      : opts.browser
188
                'mobile        :: BOOLEAN'   : opts.mobile
189
                'responsive    :: BOOLEAN'   : opts.responsive
190
                'user_agent    :: TEXT'      : opts.user_agent
191
                'smc_version   :: TEXT'      : opts.smc_version
192
                'build_date    :: TEXT'      : opts.build_date
193
                'smc_git_rev   :: TEXT'      : opts.smc_git_rev
194
                'uptime        :: TEXT'      : opts.uptime
195
                'start_time    :: TIMESTAMP' : opts.start_time
196
                'time          :: TIMESTAMP' : 'NOW()'
197
                'expire        :: TIMESTAMP' : expire
198
            cb          : opts.cb
199

200
    get_client_error_log: (opts) =>
201
        opts = defaults opts,
202
            start : undefined     # if not given start at beginning of time
203
            end   : undefined     # if not given include everything until now
204
            event : undefined
205
            cb    : required
206
        opts.log = 'client_error_log'
207
        @get_log(opts)
208

209
    set_server_setting: (opts) =>
210
        opts = defaults opts,
211
            name     : required
212
            value    : required
213
            readonly : undefined  # boolean. if yes, that value is not controlled via any UI
214
            cb       : required
215
        async.series([
216
            (cb) =>
217
                values =
218
                    'name::TEXT'  : opts.name
219
                    'value::TEXT' : opts.value
220
                if opts.readonly?
221
                    values.readonly = !!opts.readonly
222
                @_query
223
                    query    : 'INSERT INTO server_settings'
224
                    values   : values
225
                    conflict : 'name'
226
                    cb       : cb
227
            # also set a timestamp
228
            (cb) =>
229
                @_query
230
                    query  : 'INSERT INTO server_settings'
231
                    values :
232
                        'name::TEXT'  : '_last_update'
233
                        'value::TEXT' : (new Date()).toISOString()
234
                    conflict : 'name'
235
                    cb     : cb
236
        ], (err) =>
237
            # clear the cache no matter what (e.g., server_settings might have partly changed then errored)
238
            @reset_server_settings_cache()
239
            opts.cb(err)
240
        )
241

242
    reset_server_settings_cache: =>
243
        resetServerSettingsCache()
244

245
    get_server_setting: (opts) =>
246
        opts = defaults opts,
247
            name  : required
248
            cb    : required
249
        @_query
250
            query : 'SELECT value FROM server_settings'
251
            where :
252
                "name = $::TEXT" : opts.name
253
            cb    : one_result('value', opts.cb)
254

255
    get_server_settings_cached: (opts) =>
256
        opts = defaults opts,
257
            cb: required
258
        try
259
            opts.cb(undefined, await getServerSettings())
260
        catch err
261
            opts.cb(err)
262

263
    get_site_settings: (opts) =>
264
        opts = defaults opts,
265
            cb : required   # (err, settings)
266
        @_query
267
            query : 'SELECT name, value FROM server_settings'
268
            cache : true
269
            where :
270
                "name = ANY($)" : misc.keys(site_settings_conf)
271
            cb : (err, result) =>
272
                if err
273
                    opts.cb(err)
274
                else
275
                    x = {}
276
                    for k in result.rows
277
                        if k.name == 'commercial' and k.value in ['true', 'false']  # backward compatibility
278
                            k.value = eval(k.value)
279
                        x[k.name] = k.value
280
                    opts.cb(undefined, x)
281

282
    server_settings_synctable: (opts={}) =>
283
        opts.table = 'server_settings'
284
        return @synctable(opts)
285

286
    set_passport_settings: (opts) =>
287
        opts = defaults opts,
288
            strategy : required
289
            conf     : required
290
            info     : undefined
291
            cb       : required
292
        return await set_passport_settings(@, opts)
293

294
    get_passport_settings: (opts) =>
295
        opts = defaults opts,
296
            strategy : required
297
        return await get_passport_settings(@, opts)
298

299
    get_all_passport_settings: () =>
300
        return await get_all_passport_settings(@)
301

302
    get_all_passport_settings_cached: () =>
303
        return await get_all_passport_settings_cached(@)
304

305
    create_passport: (opts) =>
306
        return await create_passport(@, opts)
307

308
    passport_exists: (opts) =>
309
        return await passport_exists(@, opts)
310

311
    update_account_and_passport: (opts) =>
312
        return await update_account_and_passport(@, opts)
313

314
    ###
315
    Creating an account using SSO only.
316
    This needs to be rewritten in @cocalc/server like
317
    all the other account creation.  This is horrible
318
    because
319
    ###
320
    create_sso_account: (opts={}) =>
321
        opts = defaults opts,
322
            first_name        : undefined
323
            last_name         : undefined
324

325
            created_by        : undefined  #  ip address of computer creating this account
326

327
            email_address     : undefined
328
            password_hash     : undefined
329
            lti_id            : undefined  # 2-tuple <string[]>[iss, user_id]
330

331
            passport_strategy : undefined
332
            passport_id       : undefined
333
            passport_profile  : undefined
334
            usage_intent      : undefined
335
            cb                : required       # cb(err, account_id)
336

337
        dbg = @_dbg("create_sso_account(#{opts.first_name}, #{opts.last_name}, #{opts.lti_id}, #{opts.email_address}, #{opts.passport_strategy}, #{opts.passport_id}), #{opts.usage_intent}")
338
        dbg()
339

340
        for name in ['first_name', 'last_name']
341
            if opts[name]
342
                test = misc2_node.is_valid_username(opts[name])
343
                if test?
344
                    opts.cb("#{name} not valid: #{test}")
345
                    return
346

347
        if opts.email_address # canonicalize the email address, if given
348
            opts.email_address = misc.lower_email_address(opts.email_address)
349

350
        account_id = misc.uuid()
351

352
        passport_key = undefined
353
        if opts.passport_strategy?
354
            # This is to make it impossible to accidentally create two accounts with the same passport
355
            # due to calling create_account twice at once.   See TODO below about changing schema.
356
            # This should be enough for now since a given user only makes their account through a single
357
            # server via the persistent websocket...
358
            @_create_account_passport_keys ?= {}
359
            passport_key = _passport_key(strategy:opts.passport_strategy, id:opts.passport_id)
360
            last = @_create_account_passport_keys[passport_key]
361
            if last? and new Date() - last <= 60*1000
362
                opts.cb("recent attempt to make account with this passport strategy")
363
                return
364
            @_create_account_passport_keys[passport_key] = new Date()
365

366
        async.series([
367
            (cb) =>
368
                if not opts.passport_strategy?
369
                    cb(); return
370
                dbg("verify that no account with passport (strategy='#{opts.passport_strategy}', id='#{opts.passport_id}') already exists")
371
                # **TODO:** need to make it so insertion into the table still would yield an error due to
372
                # unique constraint; this will require probably moving the passports
373
                # object to a separate table.  This is important, since this is exactly the place where
374
                # a race condition might cause trouble!
375
                @passport_exists
376
                    strategy : opts.passport_strategy
377
                    id       : opts.passport_id
378
                    cb       : (err, account_id) ->
379
                        if err
380
                            cb(err)
381
                        else if account_id
382
                            cb("account with email passport strategy '#{opts.passport_strategy}' and id '#{opts.passport_id}' already exists")
383
                        else
384
                            cb()
385
            (cb) =>
386
                dbg("create the actual account")
387
                @_query
388
                    query  : "INSERT INTO accounts"
389
                    values :
390
                        'account_id     :: UUID'      : account_id
391
                        'first_name     :: TEXT'      : opts.first_name
392
                        'last_name      :: TEXT'      : opts.last_name
393
                        'lti_id         :: TEXT[]'    : opts.lti_id
394
                        'created        :: TIMESTAMP' : new Date()
395
                        'created_by     :: INET'      : opts.created_by
396
                        'password_hash  :: CHAR(173)' : opts.password_hash
397
                        'email_address  :: TEXT'      : opts.email_address
398
                        'sign_up_usage_intent :: TEXT': opts.usage_intent
399
                    cb : cb
400
            (cb) =>
401
                if opts.passport_strategy?
402
                    dbg("add passport authentication strategy")
403
                    @create_passport
404
                        account_id : account_id
405
                        strategy   : opts.passport_strategy
406
                        id         : opts.passport_id
407
                        profile    : opts.passport_profile
408
                        cb         : cb
409
                else
410
                    cb()
411
        ], (err) =>
412
            if err
413
                dbg("error creating account -- #{err}")
414
                opts.cb(err)
415
            else
416
                dbg("successfully created account")
417
                opts.cb(undefined, account_id)
418
        )
419

420
    is_admin: (opts) =>
421
        opts = defaults opts,
422
            account_id : required
423
            cb         : required
424
        @_query
425
            query : "SELECT groups FROM accounts"
426
            where : 'account_id = $::UUID':opts.account_id
427
            cache : true
428
            cb    : one_result 'groups', (err, groups) =>
429
                opts.cb(err, groups? and 'admin' in groups)
430

431
    user_is_in_group: (opts) =>
432
        opts = defaults opts,
433
            account_id : required
434
            group      : required
435
            cb         : required
436
        @_query
437
            query : "SELECT groups FROM accounts"
438
            where : 'account_id = $::UUID':opts.account_id
439
            cache : true
440
            cb    : one_result 'groups', (err, groups) =>
441
                opts.cb(err, groups? and opts.group in groups)
442

443
    make_user_admin: (opts) =>
444
        opts = defaults opts,
445
            account_id    : undefined
446
            email_address : undefined
447
            cb            : required
448
        if not opts.account_id? and not opts.email_address?
449
            opts.cb?("account_id or email_address must be given")
450
            return
451
        async.series([
452
            (cb) =>
453
                if opts.account_id?
454
                    cb()
455
                else
456
                    @get_account
457
                        email_address : opts.email_address
458
                        columns       : ['account_id']
459
                        cb            : (err, x) =>
460
                            if err
461
                                cb(err)
462
                            else if not x?
463
                                cb("no such email address")
464
                            else
465
                                opts.account_id = x.account_id
466
                                cb()
467
            (cb) =>
468
                @clear_cache()  # caching is mostly for permissions so this is exactly when it would be nice to clear it.
469
                @_query
470
                    query : "UPDATE accounts"
471
                    where : 'account_id = $::UUID':opts.account_id
472
                    set   :
473
                        groups : ['admin']
474
                    cb    : cb
475
        ], opts.cb)
476

477
    count_accounts_created_by: (opts) =>
478
        opts = defaults opts,
479
            ip_address : required
480
            age_s      : required
481
            cb         : required
482
        @_count
483
            table : 'accounts'
484
            where :
485
                "created_by  = $::INET"      : opts.ip_address
486
                "created    >= $::TIMESTAMP" : misc.seconds_ago(opts.age_s)
487
            cb    : opts.cb
488

489
    # Completely delete the given account from the database.  This doesn't
490
    # do any sort of cleanup of things associated with the account!  There
491
    # is no reason to ever use this, except for testing purposes.
492
    delete_account: (opts) =>
493
        opts = defaults opts,
494
            account_id : required
495
            cb         : required
496
        if not @_validate_opts(opts) then return
497
        @_query
498
            query : "DELETE FROM accounts"
499
            where : "account_id = $::UUID" : opts.account_id
500
            cb    : opts.cb
501

502
    # Mark the account as deleted, thus freeing up the email
503
    # address for use by another account, etc.  The actual
504
    # account entry remains in the database, since it may be
505
    # referred to by many other things (projects, logs, etc.).
506
    # However, the deleted field is set to true, so the account
507
    # is excluded from user search.
508
    # TODO: rewritten in packages/server/accounts/delete.ts
509
    mark_account_deleted: (opts) =>
510
        opts = defaults opts,
511
            account_id    : undefined
512
            email_address : undefined
513
            cb            : required
514
        if not opts.account_id? and not opts.email_address?
515
            opts.cb("one of email address or account_id must be specified -- make sure you are signed in")
516
            return
517

518
        query = undefined
519
        email_address = undefined
520
        async.series([
521
            (cb) =>
522
                if opts.account_id?
523
                    cb()
524
                else
525
                    @account_exists
526
                        email_address : opts.email_address
527
                        cb            : (err, account_id) =>
528
                            if err
529
                                cb(err)
530
                            else if not account_id
531
                                cb("no such email address known")
532
                            else
533
                                opts.account_id = account_id
534
                                cb()
535
            (cb) =>
536
                @_query
537
                    query : "SELECT email_address FROM accounts"
538
                    where : "account_id = $::UUID" : opts.account_id
539
                    cb    : one_result 'email_address', (err, x) =>
540
                        email_address = x; cb(err)
541
            (cb) =>
542
                @_query
543
                    query  : "UPDATE accounts"
544
                    set    :
545
                        "deleted::BOOLEAN"                  : true
546
                        "email_address_before_delete::TEXT" : email_address
547
                        "email_address"                     : null
548
                        "passports"                         : null
549
                    where  : "account_id = $::UUID"             : opts.account_id
550
                    cb     : cb
551
        ], opts.cb)
552

553
    account_exists: (opts) =>
554
        opts = defaults opts,
555
            email_address : required
556
            cb            : required   # cb(err, account_id or undefined) -- actual account_id if it exists; err = problem with db connection...
557
        @_query
558
            query : 'SELECT account_id FROM accounts'
559
            where : "email_address = $::TEXT" : opts.email_address
560
            cb    : one_result('account_id', opts.cb)
561

562
    # set an account creation action, or return all of them for the given email address
563
    account_creation_actions: (opts) =>
564
        opts = defaults opts,
565
            email_address : required
566
            action        : undefined   # if given, adds this action; if not, returns all non-expired actions
567
            ttl           : 60*60*24*14 # add action with this ttl in seconds (default: 2 weeks)
568
            cb            : required    # if ttl not given cb(err, [array of actions])
569
        if opts.action?
570
            # add action
571
            @_query
572
                query  : 'INSERT INTO account_creation_actions'
573
                values :
574
                    'id            :: UUID'      : misc.uuid()
575
                    'email_address :: TEXT'      : opts.email_address
576
                    'action        :: JSONB'     : opts.action
577
                    'expire        :: TIMESTAMP' : expire_time(opts.ttl)
578
                cb : opts.cb
579
        else
580
            # query for actions
581
            @_query
582
                query : 'SELECT action FROM account_creation_actions'
583
                where :
584
                    'email_address  = $::TEXT'       : opts.email_address
585
                    'expire        >= $::TIMESTAMP'  : new Date()
586
                cb    : all_results('action', opts.cb)
587

588
    account_creation_actions_success: (opts) =>
589
        opts = defaults opts,
590
            account_id : required
591
            cb         : required
592
        @_query
593
            query : 'UPDATE accounts'
594
            set   :
595
                'creation_actions_done::BOOLEAN' : true
596
            where :
597
                'account_id = $::UUID' : opts.account_id
598
            cb     : opts.cb
599

600
    # DEPRECATED: use import accountCreationActions from "@cocalc/server/accounts/account-creation-actions"; instead!!!!
601
    do_account_creation_actions: (opts) =>
602
        opts = defaults opts,
603
            email_address : required
604
            account_id    : required
605
            cb            : required
606
        dbg = @_dbg("do_account_creation_actions(email_address='#{opts.email_address}')")
607
        dbg("**DEPRECATED!**  This will miss doing important things, e.g., creating initial project.")
608
        @account_creation_actions
609
            email_address : opts.email_address
610
            cb            : (err, actions) =>
611
                if err
612
                    opts.cb(err); return
613
                f = (action, cb) =>
614
                    dbg("account_creation_actions: action = #{misc.to_json(action)}")
615
                    if action.action == 'add_to_project'
616
                        @add_user_to_project
617
                            project_id : action.project_id
618
                            account_id : opts.account_id
619
                            group      : action.group
620
                            cb         : (err) =>
621
                                if err
622
                                    dbg("Error adding user to project: #{err}")
623
                                cb(err)
624
                    else
625
                        dbg("ERROR: skipping unknown action -- #{action.action}")
626
                        # also store in database so we can look into this later.
627
                        @log
628
                            event : 'unknown_action'
629
                            value :
630
                                error      : "unknown_action"
631
                                action     : action
632
                                account_id : opts.account_id
633
                                host       : require('os').hostname()
634
                        cb()
635
                async.map actions, f, (err) =>
636
                    if not err
637
                        @account_creation_actions_success
638
                            account_id : opts.account_id
639
                            cb         : opts.cb
640
                    else
641
                        opts.cb(err)
642

643
    verify_email_create_token: (opts) =>  # has been rewritten in backend/email/verify.ts
644
        opts = defaults opts,
645
            account_id    : required
646
            cb            : undefined
647

648
        locals =
649
            email_address : undefined
650
            token         : undefined
651
            old_challenge : undefined
652

653
        async.series([
654
            (cb) =>
655
                @_query
656
                    query : "SELECT email_address, email_address_challenge FROM accounts"
657
                    where : "account_id = $::UUID" : opts.account_id
658
                    cb    : one_result (err, x) =>
659
                        locals.email_address = x?.email_address
660
                        locals.old_challenge = x?.email_address_challenge
661
                        cb(err)
662
            (cb) =>
663
                # TODO maybe expire tokens after some time
664
                if locals.old_challenge?
665
                    old = locals.old_challenge
666
                    # return the same token if there is one for the same email
667
                    if old.token? and old.email == locals.email_address
668
                        locals.token = locals.old_challenge.token
669
                        cb()
670
                        return
671

672
                {generate} = require("random-key")
673
                locals.token = generate(16).toLowerCase()
674
                data =
675
                    email : locals.email_address
676
                    token : locals.token
677
                    time  : new Date()
678

679
                @_query
680
                    query  : "UPDATE accounts"
681
                    set    :
682
                        'email_address_challenge::JSONB' : data
683
                    where  :
684
                        "account_id = $::UUID"       : opts.account_id
685
                    cb     : cb
686
        ], (err) ->
687
            opts.cb?(err, locals)
688
        )
689

690

691
    verify_email_check_token: (opts) =>   # rewritten in server/auth/redeem-verify-email.ts
692
        opts = defaults opts,
693
            email_address : required
694
            token         : required
695
            cb            : undefined
696

697
        locals =
698
            account_id          : undefined
699
            email_address_challenge : undefined
700

701
        async.series([
702
            (cb) =>
703
                @get_account
704
                    email_address : opts.email_address
705
                    columns       : ['account_id', 'email_address_challenge']
706
                    cb            : (err, x) =>
707
                        if err
708
                            cb(err)
709
                        else if not x?
710
                            cb("no such email address")
711
                        else
712
                            locals.account_id          = x.account_id
713
                            locals.email_address_challenge = x.email_address_challenge
714
                            cb()
715
            (cb) =>
716
                if not locals.email_address_challenge?
717
                    @is_verified_email
718
                        email_address : opts.email_address
719
                        cb            : (err, verified) ->
720
                            if not err and verified
721
                                cb("This email address is already verified.")
722
                            else
723
                                cb("For this email address no account verification is setup.")
724

725
                else if locals.email_address_challenge.email != opts.email_address
726
                    cb("The account's email address does not match the token's email address.")
727

728
                else if locals.email_address_challenge.time < misc.hours_ago(24)
729
                    cb("The account verification token is no longer valid. Get a new one!")
730

731
                else
732
                    if locals.email_address_challenge.token == opts.token
733
                        cb()
734
                    else
735
                        cb("Provided token does not match.")
736
            (cb) =>
737
                # we're good, save it
738
                @_query
739
                    query  : "UPDATE accounts"
740
                    jsonb_set :
741
                        email_address_verified:
742
                            "#{opts.email_address}" : new Date()
743
                    where  : "account_id = $::UUID" : locals.account_id
744
                    cb     : cb
745
            (cb) =>
746
                # now delete the token
747
                @_query
748
                    query  : 'UPDATE accounts'
749
                    set    :
750
                        'email_address_challenge::JSONB' : null
751
                    where  :
752
                        "account_id = $::UUID" : locals.account_id
753
                    cb     : cb
754
        ], opts.cb)
755

756
    # returns the email address and whether or not it is verified
757
    verify_email_get: (opts) =>
758
        opts = defaults opts,
759
            account_id    : required
760
            cb            : undefined
761
        @_query
762
            query : "SELECT email_address, email_address_verified FROM accounts"
763
            where : "account_id = $::UUID" : opts.account_id
764
            cb    : one_result (err, x) ->
765
                opts.cb?(err, x)
766

767
    # answers the question as cb(null, [true or false])
768
    is_verified_email: (opts) =>  # rewritten in server/auth/redeem-verify-email.ts
769
        opts = defaults opts,
770
            email_address : required
771
            cb            : required
772
        @get_account
773
            email_address : opts.email_address
774
            columns       : ['email_address_verified']
775
            cb            : (err, x) =>
776
                if err
777
                    opts.cb(err)
778
                else if not x?
779
                    opts.cb("no such email address")
780
                else
781
                    verified = !!x.email_address_verified?[opts.email_address]
782
                    opts.cb(undefined, verified)
783

784
    ###
785
    Auxiliary billing related queries
786
    ###
787
    get_coupon_history: (opts) =>
788
        opts = defaults opts,
789
            account_id : required
790
            cb         : undefined
791
        @_dbg("Getting coupon history")
792
        @_query
793
            query : "SELECT coupon_history FROM accounts"
794
            where : 'account_id = $::UUID' : opts.account_id
795
            cb    : one_result("coupon_history", opts.cb)
796

797
    update_coupon_history: (opts) =>
798
        opts = defaults opts,
799
            account_id     : required
800
            coupon_history : required
801
            cb             : undefined
802
        @_dbg("Setting to #{opts.coupon_history}")
803
        @_query
804
            query : 'UPDATE accounts'
805
            set   : 'coupon_history::JSONB' : opts.coupon_history
806
            where : 'account_id = $::UUID'  : opts.account_id
807
            cb    : opts.cb
808

809
    ###
810
    Querying for searchable information about accounts.
811
    ###
812
    account_ids_to_usernames: (opts) =>
813
        opts = defaults opts,
814
            account_ids : required
815
            cb          : required # (err, mapping {account_id:{first_name:?, last_name:?}})
816
        if not @_validate_opts(opts) then return
817
        if opts.account_ids.length == 0 # easy special case -- don't waste time on a db query
818
            opts.cb(undefined, [])
819
            return
820
        @_query
821
            query : 'SELECT account_id, first_name, last_name FROM accounts'
822
            where : 'account_id = ANY($::UUID[])' : opts.account_ids
823
            cb    : (err, result) =>
824
                if err
825
                    opts.cb(err)
826
                else
827
                    v = misc.dict(([r.account_id, {first_name:r.first_name, last_name:r.last_name}] for r in result.rows))
828
                    # fill in unknown users (should never be hit...)
829
                    for id in opts.account_ids
830
                        if not v[id]?
831
                            v[id] = {first_name:undefined, last_name:undefined}
832
                    opts.cb(err, v)
833

834
    _account_where: (opts) =>
835
        # account_id > email_address > lti_id
836
        if opts.account_id
837
            return {"account_id = $::UUID" : opts.account_id}
838
        else if opts.email_address
839
            return {"email_address = $::TEXT" : opts.email_address}
840
        else if opts.lti_id
841
            return {"lti_id = $::TEXT[]" : opts.lti_id}
842
        else
843
            throw Error("postgres-server-queries::_account_where neither account_id, nor email_address, nor lti_id specified and nontrivial")
844

845
    get_account: (opts) =>
846
        opts = defaults opts,
847
            email_address : undefined     # provide one of email, account_id, or lti_id (pref is account_id, then email_address, then lti_id)
848
            account_id    : undefined
849
            lti_id        : undefined
850
            columns       : ['account_id',
851
                             'password_hash',
852
                             'password_is_set',  # true or false, depending on whether a password is set (since don't send password_hash to user!)
853
                             'first_name',
854
                             'last_name',
855
                             'email_address',
856
                             'evaluate_key',
857
                             'autosave',
858
                             'terminal',
859
                             'editor_settings',
860
                             'other_settings',
861
                             'groups',
862
                             'passports'
863
                            ]
864
            cb            : required
865
        if not @_validate_opts(opts) then return
866
        columns = misc.copy(opts.columns)
867
        if 'password_is_set' in columns
868
            if 'password_hash' not in columns
869
                remove_password_hash = true
870
                columns.push('password_hash')
871
            misc.remove(columns, 'password_is_set')
872
            password_is_set = true
873
        @_query
874
            query : "SELECT #{columns.join(',')} FROM accounts"
875
            where : @_account_where(opts)
876
            cb    : one_result (err, z) =>
877
                if err
878
                    opts.cb(err)
879
                else if not z?
880
                    opts.cb("no such account")
881
                else
882
                    if password_is_set
883
                        z.password_is_set = !!z.password_hash
884
                        if remove_password_hash
885
                            delete z.password_hash
886
                    for c in columns
887
                        if not z[c]?     # for same semantics as rethinkdb... (for now)
888
                            delete z[c]
889
                    opts.cb(undefined, z)
890

891
    # check whether or not a user is banned
892
    is_banned_user: (opts) =>
893
        opts = defaults opts,
894
            email_address : undefined
895
            account_id    : undefined
896
            cb            : required    # cb(err, true if banned; false if not banned)
897
        if not @_validate_opts(opts) then return
898
        @_query
899
            query : 'SELECT banned FROM accounts'
900
            where : @_account_where(opts)
901
            cb    : one_result('banned', (err, banned) => opts.cb(err, !!banned))
902

903
    _touch_account: (account_id, cb) =>
904
        if @_throttle('_touch_account', 120, account_id)
905
            cb()
906
            return
907
        @_query
908
            query : 'UPDATE accounts'
909
            set   : {last_active: 'NOW()'}
910
            where : "account_id = $::UUID" : account_id
911
            cb    : cb
912

913
    _touch_project: (project_id, account_id, cb) =>
914
        if @_throttle('_user_touch_project', 60, project_id, account_id)
915
            cb()
916
            return
917
        NOW = new Date()
918
        @_query
919
            query       : "UPDATE projects"
920
            set         : {last_edited : NOW}
921
            jsonb_merge : {last_active:{"#{account_id}":NOW}}
922
            where       : "project_id = $::UUID" : project_id
923
            cb          : cb
924

925
    # Indicate activity by a user, possibly on a specific project, and
926
    # then possibly on a specific path in that project.
927
    touch: (opts) =>
928
        opts = defaults opts,
929
            account_id : required
930
            project_id : undefined
931
            path       : undefined
932
            action     : 'edit'
933
            ttl_s      : 50        # min activity interval; calling this function with same input again within this interval is ignored
934
            cb         : undefined
935
        if opts.ttl_s
936
            if @_throttle('touch', opts.ttl_s, opts.account_id, opts.project_id, opts.path, opts.action)
937
                opts.cb?()
938
                return
939

940
        now = new Date()
941
        async.parallel([
942
            (cb) =>
943
                @_touch_account(opts.account_id, cb)
944
            (cb) =>
945
                if not opts.project_id?
946
                    cb(); return
947
                @_touch_project(opts.project_id, opts.account_id, cb)
948
            (cb) =>
949
                if not opts.path? or not opts.project_id?
950
                    cb(); return
951
                @record_file_use(project_id:opts.project_id, path:opts.path, action:opts.action, account_id:opts.account_id, cb:cb)
952
        ], (err)->opts.cb?(err))
953

954

955
    # Invalidate all outstanding remember me cookies for the given account by
956
    # deleting them from the remember_me key:value store.
957
    invalidate_all_remember_me: (opts) =>
958
        opts = defaults opts,
959
            account_id    : undefined
960
            email_address : undefined
961
            cb            : undefined
962
        if not @_validate_opts(opts) then return
963
        @_query
964
            query : 'DELETE FROM remember_me'
965
            where : @_account_where(opts)
966
            cb    : opts.cb
967

968
    # Get remember me cookie with given hash.  If it has expired,
969
    # **get back undefined instead**.  (Actually deleting expired).
970
    # We use retry_until_success, since an intermittent database
971
    # reconnect can result in a cb error that will very soon
972
    # work fine, and we don't to flat out sign the client out
973
    # just because of this.
974
    get_remember_me: (opts) =>
975
        opts = defaults opts,
976
            hash                : required
977
            cache               : true
978
            cb                  : required   # cb(err, signed_in_message | undefined)
979
        account_id = undefined
980
        try
981
            account_id = await get_remember_me(@, opts.hash, opts.cache)
982
        catch err
983
            opts.cb(err)
984
            return
985
        if account_id
986
            opts.cb(undefined, {event:"signed_in", account_id:account_id})
987
        else
988
            opts.cb()
989

990
    delete_remember_me: (opts) =>
991
        opts = defaults opts,
992
            hash : required
993
            cb   : undefined
994
        @_query
995
            query : 'DELETE FROM remember_me'
996
            where :
997
                'hash = $::TEXT' : opts.hash.slice(0,127)
998
            cb    : opts.cb
999

1000
    # ASYNC FUNCTION
1001
    get_personal_user: () =>
1002
        return await get_personal_user(@)
1003

1004
    ###
1005
    # Changing password/email, etc. sensitive info about a user
1006
    ###
1007

1008
    # Change the password for the given account.
1009
    change_password: (opts={}) =>
1010
        opts = defaults opts,
1011
            account_id             : required
1012
            password_hash          : required
1013
            invalidate_remember_me : true
1014
            cb                     : required
1015
        if not @_validate_opts(opts) then return
1016
        if opts.password_hash.length > 173
1017
            opts.cb("password_hash must be at most 173 characters")
1018
            return
1019
        async.series([  # don't do in parallel -- don't kill remember_me if password failed!
1020
            (cb) =>
1021
                @_query
1022
                    query : 'UPDATE accounts'
1023
                    set   : {password_hash : opts.password_hash}
1024
                    where : @_account_where(opts)
1025
                    cb    : cb
1026
            (cb) =>
1027
                if opts.invalidate_remember_me
1028
                    @invalidate_all_remember_me
1029
                        account_id : opts.account_id
1030
                        cb         : cb
1031
                else
1032
                    cb()
1033
        ], opts.cb)
1034

1035
    # Reset Password MEANT FOR INTERACTIVE USE -- if password is not given, will prompt for it.
1036
    reset_password: (opts) =>
1037
        opts = defaults opts,
1038
            email_address : undefined
1039
            account_id    : undefined
1040
            password      : undefined
1041
            random        : true      # if true (the default), will generate and print a random password.
1042
            cb            : undefined
1043
        dbg = @_dbg("reset_password")
1044
        async.series([
1045
            (cb) =>
1046
                if opts.account_id?
1047
                    cb()
1048
                    return
1049
                @get_account
1050
                    email_address : opts.email_address
1051
                    columns       : ['account_id']
1052
                    cb            : (err, data) =>
1053
                        opts.account_id = data?.account_id
1054
                        cb(err)
1055
            (cb) =>
1056
                if opts.password?
1057
                    cb()
1058
                    return
1059
                if opts.random
1060
                    require('crypto').randomBytes 16, (err, buffer) =>
1061
                        opts.password = buffer.toString('hex')
1062
                        cb()
1063
                    return
1064
                read {prompt:'Password: ', silent:true}, (err, passwd) =>
1065
                    opts.passwd0 = passwd; cb(err)
1066
            (cb) =>
1067
                if opts.password?
1068
                    cb()
1069
                    return
1070
                read {prompt:'Retype password: ', silent:true}, (err, passwd1) =>
1071
                    if err
1072
                        cb(err)
1073
                    else
1074
                        if passwd1 != opts.passwd0
1075
                            cb("Passwords do not match.")
1076
                        else
1077
                            opts.password = passwd1
1078
                            cb()
1079
            (cb) =>
1080
                # change the user's password in the database.
1081
                @change_password
1082
                    account_id    : opts.account_id
1083
                    password_hash : passwordHash(opts.password)
1084
                    cb            : cb
1085
        ], (err) =>
1086
            if err
1087
                console.warn("Error -- #{err}")
1088
            else
1089
                console.log("Password changed for #{opts.email_address}")
1090
                if opts.random
1091
                    console.log("Random Password:\n\n\t\t#{opts.password}\n\n")
1092
            opts.cb?(err)
1093
        )
1094

1095
    # Change the email address, unless the email_address we're changing to is already taken.
1096
    # If there is a stripe customer ID, we also call the update process to maybe sync the changed email address
1097
    change_email_address: (opts={}) =>
1098
        opts = defaults opts,
1099
            account_id    : required
1100
            email_address : required
1101
            stripe        : required
1102
            cb            : required
1103
        if not @_validate_opts(opts) then return
1104
        async.series([
1105
            (cb) =>
1106
                @account_exists
1107
                    email_address : opts.email_address
1108
                    cb            : (err, exists) =>
1109
                        if err
1110
                            cb(err)
1111
                            return
1112
                        if exists
1113
                            cb("email_already_taken")
1114
                            return
1115
                        cb()
1116
            (cb) =>
1117
                @_query
1118
                    query : 'UPDATE accounts'
1119
                    set   : {email_address: opts.email_address}
1120
                    where : @_account_where(opts)
1121
                    cb    : cb
1122
            (cb) =>
1123
                @_query
1124
                    query : "SELECT stripe_customer_id FROM accounts"
1125
                    where : "account_id = $::UUID" : opts.account_id
1126
                    cb    : one_result (err, x) =>
1127
                        if err
1128
                            cb(err)
1129
                            return
1130
                        if x.stripe_customer_id
1131
                            try
1132
                                await syncCustomer
1133
                                    account_id  : opts.account_id
1134
                                    stripe      : opts.stripe
1135
                                    customer_id : x.stripe_customer_id
1136
                                cb()
1137
                            catch err
1138
                                cb(err)
1139
                        else
1140
                            cb()
1141
        ], (err) =>
1142
            opts.cb(err)
1143
        )
1144

1145
    ###
1146
    Password reset
1147
    ###
1148
    set_password_reset: (opts) =>
1149
        opts = defaults opts,
1150
            email_address : required
1151
            ttl           : required
1152
            cb            : required   # cb(err, uuid)
1153
        id = misc.uuid()
1154
        @_query
1155
            query : "INSERT INTO password_reset"
1156
            values :
1157
                "id            :: UUID"      : id
1158
                "email_address :: TEXT"      : opts.email_address
1159
                "expire        :: TIMESTAMP" : expire_time(opts.ttl)
1160
            cb : (err) =>
1161
                opts.cb(err, id)
1162

1163
    get_password_reset: (opts) =>
1164
        opts = defaults opts,
1165
            id : required
1166
            cb : required   # cb(err, true if allowed and false if not)
1167
        @_query
1168
            query : 'SELECT expire, email_address FROM password_reset'
1169
            where : 'id = $::UUID': opts.id
1170
            cb    : one_result('email_address', opts.cb)
1171

1172
    delete_password_reset: (opts) =>
1173
        opts = defaults opts,
1174
            id : required
1175
            cb : required   # cb(err, true if allowed and false if not)
1176
        @_query
1177
            query : 'DELETE FROM password_reset'
1178
            where : 'id = $::UUID': opts.id
1179
            cb    : opts.cb
1180

1181
    record_password_reset_attempt: (opts) =>
1182
        opts = defaults opts,
1183
            email_address : required
1184
            ip_address    : required
1185
            ttl           : required
1186
            cb            : required   # cb(err)
1187
        @_query
1188
            query  : 'INSERT INTO password_reset_attempts'
1189
            values :
1190
                "id            :: UUID"      : misc.uuid()
1191
                "email_address :: TEXT "     : opts.email_address
1192
                "ip_address    :: INET"      : opts.ip_address
1193
                "time          :: TIMESTAMP" : "NOW()"
1194
                "expire        :: TIMESTAMP" : expire_time(opts.ttl)
1195
            cb     : opts.cb
1196

1197
    count_password_reset_attempts: (opts) =>
1198
        opts = defaults opts,
1199
            email_address : undefined  # must give one of email_address or ip_address
1200
            ip_address    : undefined
1201
            age_s         : required   # at most this old
1202
            cb            : required   # cb(err)
1203
        @_query
1204
            query : 'SELECT COUNT(*) FROM password_reset_attempts'
1205
            where :
1206
                'time          >= $::TIMESTAMP' : misc.seconds_ago(opts.age_s)
1207
                'email_address  = $::TEXT     ' : opts.email_address
1208
                'ip_address     = $::INET     ' : opts.ip_address
1209
            cb    : count_result(opts.cb)
1210

1211
    ###
1212
    Tracking file access
1213

1214
    log_file_access is throttled in each server, in the sense that
1215
    if it is called with the same input within a minute, those
1216
    subsequent calls are ignored.  Of course, if multiple servers
1217
    are recording file_access then there can be more than one
1218
    entry per minute.
1219
    ###
1220
    log_file_access: (opts) =>
1221
        opts = defaults opts,
1222
            project_id : required
1223
            account_id : required
1224
            filename   : required
1225
            cb         : undefined
1226
        if not @_validate_opts(opts) then return
1227
        if @_throttle('log_file_access', 60, opts.project_id, opts.account_id, opts.filename)
1228
            opts.cb?()
1229
            return
1230

1231
        # If expire no pii expiration is set, use 1 year as a fallback
1232
        expire = await pii_expire() ? expire_time(365*24*60*60)
1233

1234
        @_query
1235
            query  : 'INSERT INTO file_access_log'
1236
            values :
1237
                'id         :: UUID     ' : misc.uuid()
1238
                'project_id :: UUID     ' : opts.project_id
1239
                'account_id :: UUID     ' : opts.account_id
1240
                'filename   :: TEXT     ' : opts.filename
1241
                'time       :: TIMESTAMP' : 'NOW()'
1242
                'expire     :: TIMESTAMP' : expire
1243
            cb     : opts.cb
1244

1245
    ###
1246
    Efficiently get all files access times subject to various constraints...
1247

1248
    NOTE: this was not available in RethinkDB version (too painful to implement!), but here it is,
1249
    easily sliceable in any way.  This could be VERY useful for users!
1250
    ###
1251
    get_file_access: (opts) =>
1252
        opts = defaults opts,
1253
            start      : undefined   # start time
1254
            end        : undefined  # end time
1255
            project_id : undefined
1256
            account_id : undefined
1257
            filename   : undefined
1258
            cb    : required
1259
        @_query
1260
            query : 'SELECT project_id, account_id, filename, time FROM file_access_log'
1261
            where :
1262
                'time >= $::TIMESTAMP' : opts.start
1263
                'time <= $::TIMESTAMP' : opts.end
1264
                'project_id = $::UUID' : opts.project_id
1265
                'account_id = $::UUID' : opts.account_id
1266
                'filename   = $::TEXT' : opts.filename
1267
            cb   : all_results(opts.cb)
1268

1269
    ###
1270
    File editing activity -- users modifying files in any way
1271
      - one single table called file_activity
1272
      - table also records info about whether or not activity has been seen by users
1273
    ###
1274
    record_file_use: (opts) =>
1275
        opts = defaults opts,
1276
            project_id : required
1277
            path       : required
1278
            account_id : required
1279
            action     : required  # 'edit', 'read', 'seen', 'chat', etc.?
1280
            cb         : required
1281
        # Doing what's done below (with two queries) is really, really ugly.
1282
        # See comment in db-schema.coffee about file_use table -- will redo
1283
        # for postgres later...
1284
        now = new Date()
1285
        entry =
1286
            id         : @sha1(opts.project_id, opts.path)
1287
            project_id : opts.project_id
1288
            path       : opts.path
1289
        if opts.action == 'edit' or opts.action == 'chat'
1290
            entry.last_edited = now
1291
        async.series([
1292
            (cb) =>
1293
                @_query
1294
                    query       : 'INSERT INTO file_use'
1295
                    conflict    : 'id'
1296
                    values      : entry
1297
                    cb          : cb
1298
            (cb) =>
1299
                @_query
1300
                    query       : 'UPDATE file_use'
1301
                    jsonb_merge :
1302
                        users : {"#{opts.account_id}": {"#{opts.action}": now}}
1303
                    where : {id : entry.id}
1304
                    cb          : cb
1305
        ], opts.cb)
1306

1307
    get_file_use: (opts) =>
1308
        opts = defaults opts,
1309
            max_age_s   : undefined
1310
            project_id  : undefined    # don't specify both project_id and project_ids
1311
            project_ids : undefined
1312
            path        : undefined    # if given, project_id must be given
1313
            cb          : required     # one entry if path given; otherwise, an array of entries.
1314
        if opts.project_id?
1315
            if opts.project_ids?
1316
                opts.cb("don't specify both project_id and project_ids")
1317
                return
1318
            else
1319
                opts.project_ids = [opts.project_id]
1320
        else if not opts.project_ids?
1321
            opts.cb("project_id or project_ids must be defined")
1322
            return
1323
        @_query
1324
            query    : 'SELECT * FROM file_use'
1325
            where    :
1326
                'last_edited >= $::TIMESTAMP' : if opts.max_age_s then misc.seconds_ago(opts.max_age_s)
1327
                'project_id   = ANY($)'       : opts.project_ids
1328
                'path         = $::TEXT'      : opts.path
1329
            order_by : 'last_edited'
1330
            cb       : if opts.path? then one_result(opts.cb) else all_results(opts.cb)
1331

1332
    _validate_opts: (opts) =>
1333
        for k, v of opts
1334
            if k == 'lti_id'
1335
                if not (Array.isArray(v) and v.length > 0)
1336
                    opts.cb?("invalid #{k} -- can't be an empty array")
1337
                    return false
1338
                for x in v
1339
                    if not (typeof x == 'string' and x.length > 0)
1340
                        opts.cb?("invalid #{k} -- #{v}")
1341
                        return false
1342
            else if k.slice(k.length-2) == 'id'
1343
                if v? and not misc.is_valid_uuid_string(v)
1344
                    opts.cb?("invalid #{k} -- #{v}")
1345
                    return false
1346
            if k.slice(k.length-3) == 'ids'
1347
                for w in v
1348
                    if not misc.is_valid_uuid_string(w)
1349
                        opts.cb?("invalid uuid #{w} in #{k} -- #{misc.to_json(v)}")
1350
                        return false
1351
            if k == 'group' and v not in misc.PROJECT_GROUPS
1352
                opts.cb?("unknown project group '#{v}'"); return false
1353
            if k == 'groups'
1354
                for w in v
1355
                    if w not in misc.PROJECT_GROUPS
1356
                        opts.cb?("unknown project group '#{w}' in groups"); return false
1357

1358
        return true
1359

1360
    get_project: (opts) =>
1361
        opts = defaults opts,
1362
            project_id : required   # an array of id's
1363
            columns    : PROJECT_COLUMNS
1364
            cb         : required
1365
        if not @_validate_opts(opts) then return
1366
        @_query
1367
            query : "SELECT #{opts.columns.join(',')} FROM projects"
1368
            where : 'project_id :: UUID = $' : opts.project_id
1369
            cb    : one_result(opts.cb)
1370

1371
    _get_project_column: (column, project_id, cb) =>
1372
        if not misc.is_valid_uuid_string(project_id)
1373
            cb("invalid project_id -- #{project_id}: getting column #{column}")
1374
            return
1375
        @_query
1376
            query : "SELECT #{column} FROM projects"
1377
            where : 'project_id :: UUID = $' : project_id
1378
            cb    : one_result(column, cb)
1379

1380
    get_user_column: (column, account_id, cb) =>
1381
        if not misc.is_valid_uuid_string(account_id)
1382
            cb("invalid account_id -- #{account_id}: getting column #{column}")
1383
            return
1384
        @_query
1385
            query : "SELECT #{column} FROM accounts"
1386
            where : 'account_id :: UUID = $' : account_id
1387
            cb    : one_result(column, cb)
1388

1389
    add_user_to_project: (opts) =>
1390
        opts = defaults opts,
1391
            project_id   : required
1392
            account_id   : required
1393
            group        : 'collaborator'  # see misc.PROJECT_GROUPS above
1394
            cb           : required  # cb(err)
1395

1396
        if not @_validate_opts(opts) then return
1397

1398
        @_query
1399
            query       : 'UPDATE projects'
1400
            jsonb_merge :
1401
                users   :
1402
                    "#{opts.account_id}":
1403
                        group: opts.group
1404
            where       :
1405
                "project_id = $::UUID": opts.project_id
1406
            cb          : opts.cb
1407

1408
    set_project_status: (opts) =>
1409
        opts = defaults opts,
1410
            project_id : required
1411
            status     : required
1412
            cb         : undefined
1413
        @_query
1414
            query : "UPDATE projects"
1415
            set   : {"status::JSONB"   : opts.status}
1416
            where : {"project_id = $::UUID": opts.project_id}
1417
            cb    : opts.cb
1418

1419

1420
    # Remove the given collaborator from the project.
1421
    # Attempts to remove an *owner* via this function will silently fail (change their group first),
1422
    # as will attempts to remove a user not on the project, or to remove from a non-existent project.
1423
    remove_collaborator_from_project: (opts) =>
1424
        opts = defaults opts,
1425
            project_id : required
1426
            account_id : required
1427
            cb         : required  # cb(err)
1428
        if not @_validate_opts(opts) then return
1429
        @_query
1430
            query     : 'UPDATE projects'
1431
            jsonb_set : {users : {"#{opts.account_id}": null}}
1432
            where     :
1433
                'project_id :: UUID = $'                          : opts.project_id
1434
                "users#>>'{#{opts.account_id},group}' != $::TEXT" : 'owner'
1435
            cb        : opts.cb
1436

1437
    # remove any user, even an owner.
1438
    remove_user_from_project: (opts) =>
1439
        opts = defaults opts,
1440
            project_id : required
1441
            account_id : required
1442
            cb         : required  # cb(err)
1443
        if not @_validate_opts(opts) then return
1444
        @_query
1445
            query     : 'UPDATE projects'
1446
            jsonb_set : {users : {"#{opts.account_id}": null}}
1447
            where     : {'project_id :: UUID = $' : opts.project_id}
1448
            cb        : opts.cb
1449

1450
    # Return a list of the account_id's of all collaborators of the given users.
1451
    get_collaborator_ids: (opts) =>
1452
        opts = defaults opts,
1453
            account_id : required
1454
            cb         : required
1455
        dbg = @_dbg("get_collaborator_ids")
1456
        @_query
1457
            query : "SELECT DISTINCT jsonb_object_keys(users) FROM projects"
1458
            where : "users ? $::TEXT" : opts.account_id
1459
            cb    : all_results('jsonb_object_keys', opts.cb)
1460

1461
    # get list of project collaborator IDs
1462
    get_collaborators: (opts) =>
1463
        opts = defaults opts,
1464
            project_id : required
1465
            cb         : required
1466
        dbg = @_dbg("get_collaborators")
1467
        @_query
1468
            query : "SELECT DISTINCT jsonb_object_keys(users) FROM projects"
1469
            where : "project_id = $::UUID" : opts.project_id
1470
            cb    : all_results('jsonb_object_keys', opts.cb)
1471

1472

1473
    # return list of paths that are public and not disabled in the given project
1474
    get_public_paths: (opts) =>
1475
        opts = defaults opts,
1476
            project_id  : required
1477
            cb          : required
1478
        if not @_validate_opts(opts) then return
1479
        @_query
1480
            query : "SELECT path FROM public_paths"
1481
            where : [
1482
                "project_id = $::UUID" : opts.project_id,
1483
                "disabled IS NOT TRUE"
1484
            ]
1485
            cb    : all_results('path', opts.cb)
1486

1487
    has_public_path: (opts) =>
1488
        opts = defaults opts,
1489
            project_id  : required
1490
            cb          : required    # cb(err, has_public_path)
1491
        @_query
1492
            query : "SELECT COUNT(path) FROM public_paths"
1493
            where : [
1494
                "project_id = $::UUID" : opts.project_id,
1495
                "disabled IS NOT TRUE"
1496
            ]
1497
            cb    : count_result (err, n) ->
1498
                opts.cb(err, n>0)
1499

1500
    path_is_public: (opts) =>
1501
        opts = defaults opts,
1502
            project_id : required
1503
            path       : required
1504
            cb         : required
1505
        # Get all public paths for the given project_id, then check if path is "in" one according
1506
        # to the definition in misc.
1507
        # TODO: maybe (?) implement caching + changefeeds so that we only do the get once.
1508
        @get_public_paths
1509
            project_id : opts.project_id
1510
            cb         : (err, public_paths) =>
1511
                if err
1512
                    opts.cb(err)
1513
                else
1514
                    opts.cb(undefined, misc.path_is_in_public_paths(opts.path, public_paths))
1515

1516
    filter_public_paths: (opts) =>
1517
        opts = defaults opts,
1518
            project_id : required
1519
            path       : required
1520
            listing    : required   # files in path [{name:..., isdir:boolean, ....}, ...]
1521
            cb         : required
1522
        # Get all public paths for the given project_id, then check if path is "in" one according
1523
        # to the definition in misc.
1524
        @get_public_paths
1525
            project_id : opts.project_id
1526
            cb         : (err, public_paths) =>
1527
                if err
1528
                    opts.cb(err)
1529
                    return
1530
                if misc.path_is_in_public_paths(opts.path, public_paths)
1531
                    # nothing to do -- containing path is public
1532
                    listing = opts.listing
1533
                else
1534
                    listing = misc.deep_copy(opts.listing) # don't mututate input on general principle
1535
                    # some files in the listing might not be public, since the containing path isn't public, so we filter
1536
                    # WARNING: this is kind of stupid since misc.path_is_in_public_paths is badly implemented, especially
1537
                    # for this sort of iteration.  TODO: make this faster.  This could matter since is done on server.
1538
                    try
1539
                        # we use try/catch here since there is no telling what is in the listing object; the user
1540
                        # could pass in anything...
1541
                        listing.files = (x for x in listing.files when \
1542
                            misc.path_is_in_public_paths(misc.path_to_file(opts.path, x.name), public_paths))
1543
                    catch
1544
                        listing.files = []
1545
                opts.cb(undefined, listing)
1546

1547
    # Set last_edited for this project to right now, and possibly update its size.
1548
    # It is safe and efficient to call this function very frequently since it will
1549
    # actually hit the database at most once every 30s (per project, per client).  In particular,
1550
    # once called, it ignores subsequent calls for the same project for 30s.
1551
    touch_project: (opts) =>
1552
        opts = defaults opts,
1553
            project_id : required
1554
            cb         : undefined
1555
        if not @_validate_opts(opts) then return
1556
        if @_throttle('touch_project', 30, opts.project_id)
1557
            opts.cb?()
1558
            return
1559
        @_query
1560
            query : "UPDATE projects"
1561
            set   : {last_edited : 'NOW()'}
1562
            where : "project_id = $::UUID" : opts.project_id
1563
            cb    : opts.cb
1564

1565
    recently_modified_projects: (opts) =>
1566
        opts = defaults opts,
1567
            max_age_s : required
1568
            cb        : required
1569
        @_query
1570
            query : "SELECT project_id FROM projects"
1571
            where : "last_edited >= $::TIMESTAMP" : misc.seconds_ago(opts.max_age_s)
1572
            cb    : all_results('project_id', opts.cb)
1573

1574
    get_open_unused_projects: (opts) =>
1575
        opts = defaults opts,
1576
            min_age_days : 30         # project must not have been edited in this much time
1577
            max_age_days : 120        # project must have been edited at most this long ago
1578
            host         : required   # hostname of where project is opened
1579
            cb           : required
1580
        @_query
1581
            query : "SELECT project_id FROM projects"
1582
            where : [
1583
                "last_edited >= $::TIMESTAMP" : misc.days_ago(opts.max_age_days)
1584
                "last_edited <= $::TIMESTAMP" : misc.days_ago(opts.min_age_days)
1585
                "host#>>'{host}' = $::TEXT  " : opts.host,
1586
                "state#>>'{state}' = 'opened'"
1587
            ]
1588
            cb    : all_results('project_id', opts.cb)
1589

1590
    # cb(err, true if user is in one of the groups for the project **or an admin**)
1591
    user_is_in_project_group: (opts) =>
1592
        opts = defaults opts,
1593
            project_id  : required
1594
            account_id  : undefined
1595
            groups      : ['owner', 'collaborator']
1596
            cache       : false  # if true cache result for a few seconds
1597
            cb          : required  # cb(err, true if in group)
1598
        if not opts.account_id?
1599
            # clearly user -- who isn't even signed in -- is not in the group
1600
            opts.cb(undefined, false)
1601
            return
1602
        if not @_validate_opts(opts) then return
1603
        @_query
1604
            query : 'SELECT COUNT(*) FROM projects'
1605
            cache : opts.cache
1606
            where :
1607
                'project_id :: UUID = $' : opts.project_id
1608
                "users#>>'{#{opts.account_id},group}' = ANY($)" : opts.groups
1609
            cb    : count_result (err, n) =>
1610
                if err
1611
                    opts.cb(err)
1612
                else if n == 0
1613
                    # one more chance -- admin?
1614
                    @is_admin
1615
                        account_id : opts.account_id
1616
                        cb         : opts.cb
1617
                else
1618
                    opts.cb(err, n > 0)
1619

1620
    # cb(err, true if user is an actual collab; ADMINS do not count)
1621
    user_is_collaborator: (opts) =>
1622
        opts = defaults opts,
1623
            project_id  : required
1624
            account_id  : required
1625
            cache       : true
1626
            cb          : required  # cb(err, true if is actual collab on project)
1627
        if not @_validate_opts(opts) then return
1628
        @_query
1629
            query : 'SELECT COUNT(*) FROM projects'
1630
            cache : opts.cache
1631
            where : ['project_id :: UUID = $1', "users ? $2"]
1632
            params: [opts.project_id, opts.account_id]
1633
            cb    : count_result (err, n) =>
1634
                if err
1635
                    opts.cb(err)
1636
                else
1637
                    opts.cb(err, n > 0)
1638

1639
    # all id's of projects having anything to do with the given account
1640
    get_project_ids_with_user: (opts) =>
1641
        opts = defaults opts,
1642
            account_id : required
1643
            is_owner   : undefined     # if set to true, only return projects with this owner.
1644
            cb         : required      # opts.cb(err, [project_id, project_id, project_id, ...])
1645
        if not @_validate_opts(opts) then return
1646

1647
        if opts.is_owner
1648
            where = {"users#>>'{#{opts.account_id},group}' = $::TEXT" : 'owner'}
1649
        else
1650
            where = {'users ? $::TEXT' : opts.account_id}
1651
        @_query
1652
            query : 'SELECT project_id FROM projects'
1653
            where : where
1654
            cb    : all_results('project_id', opts.cb)
1655

1656
    # cb(err, array of account_id's of accounts in non-invited-only groups)
1657
    # TODO: add something about invited users too and show them in UI!
1658
    get_account_ids_using_project: (opts) =>
1659
        opts = defaults opts,
1660
            project_id : required
1661
            cb         : required
1662
        if not @_validate_opts(opts) then return
1663
        @_query
1664
            query : 'SELECT users FROM projects'
1665
            where : 'project_id :: UUID = $' : opts.project_id
1666
            cb    : one_result 'users', (err, users) =>
1667
                if err
1668
                    opts.cb(err)
1669
                    return
1670
                opts.cb(undefined, if users? then (id for id,v of users when v.group?.indexOf('invite') == -1) else [])
1671

1672
    # Have we successfully (no error) sent an invite to the given email address?
1673
    # If so, returns timestamp of when.
1674
    # If not, returns 0.
1675
    when_sent_project_invite: (opts) =>
1676
        opts = defaults opts,
1677
            project_id : required
1678
            to         : required  # an email address
1679
            cb         : required
1680
        if not @_validate_opts(opts) then return
1681
        # in particular, emails like bla'[email protected] → bla''[email protected]
1682
        sani_to = @sanitize("{\"#{opts.to}\"}")
1683
        query_select = "SELECT invite#>#{sani_to} AS to FROM projects"
1684
        @_query
1685
            query : query_select
1686
            where : 'project_id :: UUID = $' : opts.project_id
1687
            cb    : one_result 'to', (err, y) =>
1688
                opts.cb(err, if not y? or y.error or not y.time then 0 else new Date(y.time))
1689

1690
    # call this to record that we have sent an email invite to the given email address
1691
    sent_project_invite: (opts) =>
1692
        opts = defaults opts,
1693
            project_id : required
1694
            to         : required   # an email address
1695
            error      : undefined  # if there was an error set it to this; leave undefined to mean that sending succeeded
1696
            cb         : undefined
1697
        x = {time: new Date()}
1698
        if opts.error?
1699
            x.error = opts.error
1700
        @_query
1701
            query : "UPDATE projects"
1702
            jsonb_merge :
1703
                {invite : "#{opts.to}" : {time: new Date(), error:opts.error}}
1704
            where : 'project_id :: UUID = $' : opts.project_id
1705
            cb : opts.cb
1706

1707
    ###
1708
    Project host, storage location, and state.
1709
    ###
1710
    set_project_host: (opts) =>
1711
        opts = defaults opts,
1712
            project_id : required
1713
            host       : required
1714
            cb         : required
1715
        assigned = new Date()
1716
        @_query
1717
            query : "UPDATE projects"
1718
            jsonb_set :
1719
                host : {host:opts.host, assigned:assigned}
1720
            where : 'project_id :: UUID = $' : opts.project_id
1721
            cb    : (err) => opts.cb(err, assigned)
1722

1723
    unset_project_host: (opts) =>
1724
        opts = defaults opts,
1725
            project_id : required
1726
            cb         : required
1727
        @_query
1728
            query : "UPDATE projects"
1729
            set   :
1730
                host : null
1731
            where : 'project_id :: UUID = $' : opts.project_id
1732
            cb    : opts.cb
1733

1734
    get_project_host: (opts) =>
1735
        opts = defaults opts,
1736
            project_id : required
1737
            cb         : required
1738
        @_query
1739
            query : "SELECT host#>>'{host}' AS host FROM projects"
1740
            where : 'project_id :: UUID = $' : opts.project_id
1741
            cb    : one_result('host', opts.cb)
1742

1743
    set_project_storage: (opts) =>
1744
        opts = defaults opts,
1745
            project_id : required
1746
            host       : required
1747
            cb         : required
1748
        @get_project_storage
1749
            project_id : opts.project_id
1750
            cb         : (err, current) =>
1751
                if err
1752
                    opts.cb(err)
1753
                    return
1754
                if current?.host? and current.host != opts.host
1755
                    opts.cb("change storage not implemented yet -- need to implement saving previous host")
1756
                else
1757
                    # easy case -- assigning for the first time
1758
                    assigned = new Date()
1759
                    @_query
1760
                        query : "UPDATE projects"
1761
                        jsonb_set :
1762
                            storage : {host:opts.host, assigned:assigned}
1763
                        where : 'project_id :: UUID = $' : opts.project_id
1764
                        cb    : (err) => opts.cb(err, assigned)
1765

1766
    get_project_storage: (opts) =>
1767
        opts = defaults opts,
1768
            project_id : required
1769
            cb         : required
1770
        @_get_project_column('storage', opts.project_id, opts.cb)
1771

1772
    update_project_storage_save: (opts) =>
1773
        opts = defaults opts,
1774
            project_id : required
1775
            cb         : required
1776
        @_query
1777
            query : "UPDATE projects"
1778
            jsonb_merge :
1779
                storage : {saved:new Date()}
1780
            where : 'project_id :: UUID = $' : opts.project_id
1781
            cb    : opts.cb
1782

1783
    set_project_storage_request: (opts) =>
1784
        opts = defaults opts,
1785
            project_id : required
1786
            action     : required    # 'save', 'close', 'open', 'move'
1787
            target     : undefined   # needed for 'open' and 'move'
1788
            cb         : required
1789
        x =
1790
            action    : opts.action
1791
            requested : new Date()
1792
        if opts.target?
1793
            x.target = opts.target
1794
        @_query
1795
            query     : "UPDATE projects"
1796
            set       :
1797
                "storage_request::JSONB" : x
1798
            where     : 'project_id :: UUID = $' : opts.project_id
1799
            cb        : opts.cb
1800

1801
    get_project_storage_request: (opts) =>
1802
        opts = defaults opts,
1803
            project_id : required
1804
            cb         : required
1805
        @_get_project_column('storage_request', opts.project_id, opts.cb)
1806

1807
    set_project_state: (opts) =>
1808
        opts = defaults opts,
1809
            project_id : required
1810
            state      : required
1811
            time       : new Date()
1812
            error      : undefined
1813
            ip         : undefined   # optional ip address
1814
            cb         : required
1815
        if typeof(opts.state) != 'string'
1816
            opts.cb("invalid state type")
1817
            return
1818
        if not COMPUTE_STATES[opts.state]?
1819
            opts.cb("state = '#{opts.state}' it not a valid state")
1820
            return
1821
        state =
1822
            state : opts.state
1823
            time  : opts.time
1824
        if opts.error
1825
            state.error = opts.error
1826
        if opts.ip
1827
            state.ip = opts.ip
1828
        @_query
1829
            query     : "UPDATE projects"
1830
            set       : "state::JSONB" : state
1831
            where     : 'project_id :: UUID = $' : opts.project_id
1832
            cb        : opts.cb
1833

1834
    get_project_state: (opts) =>
1835
        opts = defaults opts,
1836
            project_id : required
1837
            cb         : required
1838
        @_get_project_column('state', opts.project_id, opts.cb)
1839

1840
    ###
1841
    Project quotas and upgrades
1842
    ###
1843

1844
    # Returns the total quotas for the project, including any
1845
    # upgrades to the base settings.
1846
    get_project_quotas: (opts) =>
1847
        opts = defaults opts,
1848
            project_id : required
1849
            cb         : required
1850
        settings = users = site_license = server_settings = undefined
1851
        async.parallel([
1852
            (cb) =>
1853
                @_query
1854
                    query : 'SELECT settings, users, site_license FROM projects'
1855
                    where : 'project_id = $::UUID' : opts.project_id
1856
                    cb    : one_result (err, x) =>
1857
                        settings = x.settings
1858
                        site_license = x.site_license
1859
                        users = x.users
1860
                        cb(err)
1861
            (cb) =>
1862
                @get_server_settings_cached
1863
                    cb : (err, x) =>
1864
                        server_settings = x
1865
                        cb(err)
1866
        ], (err) =>
1867
            if err
1868
                opts.cb(err)
1869
            else
1870
                upgrades = quota(settings, users, site_license, server_settings)
1871
                opts.cb(undefined, upgrades)
1872
        )
1873

1874
    # Return mapping from project_id to map listing the upgrades this particular user
1875
    # applied to the given project.  This only includes project_id's of projects that
1876
    # this user may have upgraded in some way.
1877
    get_user_project_upgrades: (opts) =>
1878
        opts = defaults opts,
1879
            account_id : required
1880
            cb         : required
1881
        @_query
1882
            query : "SELECT project_id, users#>'{#{opts.account_id},upgrades}' AS upgrades FROM projects"
1883
            where : [
1884
                'users ? $::TEXT' : opts.account_id,    # this is a user of the project
1885
                "users#>'{#{opts.account_id},upgrades}' IS NOT NULL"     # upgrades are defined
1886
            ]
1887
            cb : (err, result) =>
1888
                if err
1889
                    opts.cb(err)
1890
                else
1891
                    x = {}
1892
                    for p in result.rows
1893
                        x[p.project_id] = p.upgrades
1894
                    opts.cb(undefined, x)
1895

1896
    # Ensure that all upgrades applied by the given user to projects are consistent,
1897
    # truncating any that exceed their allotment.  NOTE: Unless there is a bug,
1898
    # the only way the quotas should ever exceed their allotment would be if the
1899
    # user is trying to cheat... *OR* a subscription was canceled or ended.
1900
    ensure_user_project_upgrades_are_valid: (opts) =>
1901
        opts = defaults opts,
1902
            account_id : required
1903
            fix        : true       # if true, will fix projects in database whose quotas exceed the allotted amount; it is the caller's responsibility to actually change them.
1904
            cb         : required   # cb(err, excess)
1905
        dbg = @_dbg("ensure_user_project_upgrades_are_valid(account_id='#{opts.account_id}')")
1906
        dbg()
1907
        excess = stripe_data = project_upgrades = undefined
1908
        async.series([
1909
            (cb) =>
1910
                async.parallel([
1911
                    (cb) =>
1912
                        @_query
1913
                            query : 'SELECT stripe_customer FROM accounts'
1914
                            where : 'account_id = $::UUID' : opts.account_id
1915
                            cb    : one_result 'stripe_customer', (err, stripe_customer) =>
1916
                                stripe_data = stripe_customer?.subscriptions?.data
1917
                                cb(err)
1918
                    (cb) =>
1919
                        @get_user_project_upgrades
1920
                            account_id : opts.account_id
1921
                            cb         : (err, x) =>
1922
                                project_upgrades = x
1923
                                cb(err)
1924
                ], cb)
1925
            (cb) =>
1926
                excess = require('@cocalc/util/upgrades').available_upgrades(stripe_data, project_upgrades).excess
1927
                if opts.fix
1928
                    fix = (project_id, cb) =>
1929
                        dbg("fixing project_id='#{project_id}' with excess #{JSON.stringify(excess[project_id])}")
1930
                        upgrades = undefined
1931
                        async.series([
1932
                            (cb) =>
1933
                                @_query
1934
                                    query : "SELECT users#>'{#{opts.account_id},upgrades}' AS upgrades FROM projects"
1935
                                    where : 'project_id = $::UUID' : project_id
1936
                                    cb    : one_result 'upgrades', (err, x) =>
1937
                                        upgrades = x; cb(err)
1938
                            (cb) =>
1939
                                if not upgrades?
1940
                                    cb(); return
1941
                                # WORRY: this is dangerous since if something else changed about a user
1942
                                # between the read/write here, then we would have trouble.  (This is milliseconds of time though...)
1943
                                for k, v of excess[project_id]
1944
                                    upgrades[k] -= v
1945
                                @_query
1946
                                    query       : "UPDATE projects"
1947
                                    where       : 'project_id = $::UUID' : project_id
1948
                                    jsonb_merge :
1949
                                        users : {"#{opts.account_id}": {upgrades: upgrades}}
1950
                                    cb          : cb
1951
                        ], cb)
1952
                    async.map(misc.keys(excess), fix, cb)
1953
                else
1954
                    cb()
1955
        ], (err) =>
1956
            opts.cb(err, excess)
1957
        )
1958

1959
    # Loop through every user of cocalc that is connected with stripe (so may have a subscription),
1960
    # and ensure that any upgrades that have applied to projects are valid.  It is important to
1961
    # run this periodically or there is a really natural common case where users can cheat:
1962
    #    (1) they apply upgrades to a project
1963
    #    (2) their subscription expires
1964
    #    (3) they do NOT touch upgrades on any projects again.
1965
    ensure_all_user_project_upgrades_are_valid: (opts) =>
1966
        opts = defaults opts,
1967
            limit : 1          # We only default to 1 at a time, since there is no hurry.
1968
            cb    : required
1969
        dbg = @_dbg("ensure_all_user_project_upgrades_are_valid")
1970
        locals = {}
1971
        async.series([
1972
            (cb) =>
1973
                @_query
1974
                    query : "SELECT account_id FROM accounts"
1975
                    where : "stripe_customer_id IS NOT NULL"
1976
                    timeout_s: 300
1977
                    cb    : all_results 'account_id', (err, account_ids) =>
1978
                        locals.account_ids = account_ids
1979
                        cb(err)
1980
            (cb) =>
1981
                m = 0
1982
                n = locals.account_ids.length
1983
                dbg("got #{n} accounts with stripe")
1984
                f = (account_id, cb) =>
1985
                    m += 1
1986
                    dbg("#{m}/#{n}")
1987
                    @ensure_user_project_upgrades_are_valid
1988
                        account_id : account_id
1989
                        cb         : cb
1990
                async.mapLimit(locals.account_ids, opts.limit, f, cb)
1991
        ], opts.cb)
1992

1993
    # Return the sum total of all user upgrades to a particular project
1994
    get_project_upgrades: (opts) =>
1995
        opts = defaults opts,
1996
            project_id : required
1997
            cb         : required
1998
        @_query
1999
            query : 'SELECT users FROM projects'
2000
            where : 'project_id = $::UUID' : opts.project_id
2001
            cb    : one_result 'users', (err, users) =>
2002
                if err
2003
                    opts.cb(err); return
2004
                upgrades = undefined
2005
                if users?
2006
                    for account_id, info of users
2007
                        upgrades = misc.map_sum(upgrades, info.upgrades)
2008
                opts.cb(undefined, upgrades)
2009

2010
    # Remove all upgrades to all projects applied by this particular user.
2011
    remove_all_user_project_upgrades: (opts) =>
2012
        opts = defaults opts,
2013
            account_id : required
2014
            projects   : undefined  # if given, only remove from projects with id in this array.
2015
            cb         : required
2016
        if not misc.is_valid_uuid_string(opts.account_id)
2017
            opts.cb("invalid account_id")
2018
            return
2019
        query =  "UPDATE projects SET users=jsonb_set(users, '{#{opts.account_id}}', jsonb(users#>'{#{opts.account_id}}') - 'upgrades')"
2020
        where = [
2021
                'users ? $::TEXT' : opts.account_id,                     # this is a user of the project
2022
                "users#>'{#{opts.account_id},upgrades}' IS NOT NULL"     # upgrades are defined
2023
            ]
2024
        if opts.projects
2025
            if not misc.is_array(opts.projects)
2026
                opts.cb("projects must be an array")
2027
                return
2028
            w = []
2029
            for project_id in opts.projects
2030
                if not misc.is_valid_uuid_string(project_id)
2031
                    opts.cb('each entry in projects must be a valid uuid')
2032
                    return
2033
                w.push("'#{project_id}'")
2034
            where.push("project_id in (#{w.join(',')})")
2035

2036
        @_query
2037
            query : query
2038
            where : where
2039
            cb: opts.cb
2040
        # TODO: any impacted project that is currently running should also (optionally?) get restarted.
2041
        # I'm not going to bother for now, but this DOES need to get implemented, since otherwise users
2042
        # can cheat too easily.  Alternatively, have a periodic control loop on all running projects that
2043
        # confirms that everything is legit (and remove the verification code for user_query) --
2044
        # that's probably better.  This could be a service called manage-upgrades.
2045

2046
    ###
2047
    Project settings
2048
    ###
2049
    get_project_settings: (opts) =>
2050
        opts = defaults opts,
2051
            project_id : required
2052
            cb         : required
2053
        @_query
2054
            query : "SELECT settings FROM projects"
2055
            where : 'project_id = $::UUID' : opts.project_id
2056
            cb    : one_result 'settings', (err, settings) =>
2057
                if err
2058
                    opts.cb(err)
2059
                else if not settings?
2060
                    opts.cb(undefined, misc.copy(DEFAULT_QUOTAS))
2061
                else
2062
                    settings = misc.coerce_codomain_to_numbers(settings)
2063
                    quotas = {}
2064
                    for k, v of DEFAULT_QUOTAS
2065
                        quotas[k] = if not settings[k]? then v else settings[k]
2066
                    opts.cb(undefined, quotas)
2067

2068
    set_project_settings: (opts) =>
2069
        opts = defaults opts,
2070
            project_id : required
2071
            settings   : required   # can be any subset of the map
2072
            cb         : required
2073
        @_query
2074
            query       : "UPDATE projects"
2075
            where       : 'project_id = $::UUID' : opts.project_id
2076
            jsonb_merge : {settings: opts.settings}
2077
            cb          : opts.cb
2078

2079
    get_project_extra_env: (opts) =>
2080
        opts = defaults opts,
2081
            project_id : required
2082
            cb         : required
2083
        @_query
2084
            query : "SELECT env FROM projects"
2085
            where : 'project_id = $::UUID' : opts.project_id
2086
            cb    : one_result 'env', (err, env) =>
2087
                if err
2088
                    opts.cb(err)
2089
                else
2090
                    opts.cb(undefined, env ? {})
2091

2092

2093
    recent_projects: (opts) =>
2094
        opts = defaults opts,
2095
            age_m     : required   # return results at most this old
2096
            min_age_m : 0          # only returns results at least this old
2097
            pluck     : undefined  # if not given, returns list of project_id's; if given (as an array), returns objects with these fields
2098
            cb        : required   # cb(err, list of strings or objects)
2099

2100
        if opts.pluck?
2101
            columns = opts.pluck.join(',')
2102
            cb = all_results(opts.cb)
2103
        else
2104
            columns = 'project_id'
2105
            cb = all_results('project_id', opts.cb)
2106
        @_query
2107
            query : "SELECT #{columns} FROM projects"
2108
            where :
2109
                "last_edited >= $::TIMESTAMP" : misc.minutes_ago(opts.age_m)
2110
                "last_edited <= $::TIMESTAMP" : misc.minutes_ago(opts.min_age_m)
2111
            cb    : cb
2112

2113
    get_stats_interval: (opts) =>
2114
        opts = defaults opts,
2115
            start : required
2116
            end   : required
2117
            cb    : required
2118
        @_query
2119
            query    : 'SELECT * FROM stats'
2120
            where    :
2121
                "time >= $::TIMESTAMP" : opts.start
2122
                "time <= $::TIMESTAMP" : opts.end
2123
            order_by : 'time'
2124
            cb       : all_results(opts.cb)
2125

2126
    # If there is a cached version of stats (which has given ttl) return that -- this could have
2127
    # been computed by any of the hubs.  If there is no cached version, compute new one and store
2128
    # in cache for ttl seconds.
2129
    get_stats: (opts) =>
2130
        opts = defaults opts,
2131
            ttl_dt : 15       # 15 secs subtracted from ttl to compensate for computation duration when called via a cronjob
2132
            ttl    : 5*60     # how long cached version lives (in seconds)
2133
            ttl_db : 30       # how long a valid result from a db query is cached in any case
2134
            update : true     # true: recalculate if older than ttl; false: don't recalculate and pick it from the DB (locally cached for ttl secs)
2135
            cb     : undefined
2136
        return await calc_stats(@, opts)
2137

2138
    get_active_student_stats: (opts) =>
2139
        opts = defaults opts,
2140
            cb  : required
2141
        dbg = @_dbg('get_active_student_stats')
2142
        dbg()
2143
        @_query
2144
            query  : "SELECT project_id, course, last_edited, settings, users FROM projects WHERE course IS NOT NULL AND last_edited >= $1"
2145
            params : [misc.days_ago(30)]
2146
            cb     : all_results (err, t) =>
2147
                if err
2148
                    opts.cb(err)
2149
                    return
2150
                days14 = misc.days_ago(14)
2151
                days7  = misc.days_ago(7)
2152
                days1  = misc.days_ago(1)
2153
                # student pay means that the student is required to pay
2154
                num_student_pay = (x for x in t when x.course.pay).length
2155
                # prof pay means that student isn't required to pay but
2156
                # nonetheless project is on members only host
2157
                num_prof_pay    = 0
2158
                for x in t
2159
                    if not x.course.pay  # student isn't paying
2160
                        if x.settings?.member_host
2161
                            num_prof_pay += 1
2162
                            continue
2163
                        for _, d of x.users
2164
                            if d.upgrades?.member_host
2165
                                num_prof_pay += 1
2166
                                continue
2167
                # free - neither student pays, and also project not on members only server
2168
                num_free        = t.length - num_prof_pay - num_student_pay
2169
                conversion_rate = if t.length then 100*(num_student_pay + num_prof_pay) / t.length else 0
2170
                data =
2171
                    conversion_rate : conversion_rate
2172
                    num_student_pay : num_student_pay
2173
                    num_prof_pay    : num_prof_pay
2174
                    num_free        : num_free
2175
                    num_1days       : (x for x in t when x.last_edited >= days1).length
2176
                    num_7days       : (x for x in t when x.last_edited >= days7).length
2177
                    num_14days      : (x for x in t when x.last_edited >= days14).length
2178
                    num_30days      : t.length
2179
                opts.cb(undefined, data)
2180

2181

2182
    ###
2183
    Hub servers
2184
    ###
2185
    register_hub: (opts) =>
2186
        opts = defaults opts,
2187
            host    : required
2188
            port    : required
2189
            clients : required
2190
            ttl     : required
2191
            cb      : required
2192
        # Since multiple hubs can run on the same host (but with different ports) and the host is the primary
2193
        # key, we combine the host and port number in the host name for the db.  The hub_servers table is only
2194
        # used for tracking connection stats, so this is safe.
2195
        @_query
2196
            query  : "INSERT INTO hub_servers"
2197
            values :
2198
                "host    :: TEXT     " : "#{opts.host}-#{opts.port}"
2199
                "port    :: INTEGER  " : opts.port
2200
                "clients :: INTEGER  " : opts.clients
2201
                "expire  :: TIMESTAMP" : expire_time(opts.ttl)
2202
            conflict : 'host'
2203
            cb : opts.cb
2204

2205
    get_hub_servers: (opts) =>
2206
        opts = defaults opts,
2207
            cb   : required
2208
        @_query
2209
            query : "SELECT * FROM hub_servers"
2210
            cb    : all_results (err, v) =>
2211
                if err
2212
                    opts.cb(err)
2213
                    return
2214
                w = []
2215
                to_delete = []
2216
                now = new Date()
2217
                for x in v
2218
                    if x.expire and x.expire <= now
2219
                        to_delete.push(x.host)
2220
                    else
2221
                        w.push(x)
2222
                if to_delete.length > 0
2223
                    @_query
2224
                        query : "DELETE FROM hub_servers"
2225
                        where : "host = ANY($)" : to_delete
2226
                        cb    : (err) => opts.cb(err, w)
2227
                else
2228
                    opts.cb(undefined, w)
2229

2230
    ###
2231
    Custom software images
2232
    ###
2233

2234
    # this is 100% for cc-in-cc dev projects only!
2235
    insert_random_compute_images: (opts) =>
2236
        opts = defaults opts,
2237
            cb     : required
2238

2239
        dbg = @_dbg("database::insert_random_compute_images")
2240
        dbg()
2241

2242
        capitalize = require('@cocalc/util/misc').capitalize
2243

2244
        words = [
2245
                    'wizard', 'jupyter', 'carrot', 'python', 'science', 'gold', 'eagle',
2246
                    'advanced', 'course', 'yellow', 'bioinformatics', 'R', 'electric', 'sheep',
2247
                    'theory', 'math', 'physics', 'calculate', 'primer', 'DNA', 'tech', 'space'
2248
                ]
2249

2250
        # deterministically sample distinct words (such that this is stable after a restart)
2251
        sample = (idx=0, n=1) ->
2252
            N = words.length
2253
            K = (idx * 997) %% N
2254
            ret = []
2255
            for i in [0..n]
2256
                for j in [0..N]
2257
                    w = words[(K + 97 * i + j) %% N]
2258
                    if ret.includes(w)
2259
                        continue
2260
                    else
2261
                        ret.push(w)
2262
                        break
2263
            return ret
2264

2265
        rseed = 123
2266
        random = ->
2267
            x = Math.sin(rseed++)
2268
            r = x - Math.floor(x)
2269
            return r
2270

2271
        create = (idx, cb) =>
2272
            rnd  = sample(idx, 3)
2273
            id   = rnd[...2].join('-') + "-#{idx}"
2274
            provider = ['github.com', 'gitlab.com', 'bitbucket.org'][idx % 3]
2275
            src = "https://#{provider}/#{rnd[2]}/#{id}.git"
2276

2277
            # not all of them have a display-title, url, desc, ...
2278
            if random() > .25
2279
                if random() > .5
2280
                    extra = "(#{sample(idx + 2)})"
2281
                else
2282
                    extra = sample(idx+5, 2)
2283
                disp = (capitalize(_) for _ in rnd[...2].concat(extra)).join(' ')
2284
            else
2285
                if random() > .5
2286
                    disp = undefined
2287
                else
2288
                    disp = ''
2289

2290
            if random() > .5
2291
                url = "https://www.google.com/search?q=#{rnd.join('%20')}"
2292
            else
2293
                url = undefined
2294

2295
            if random() > .5
2296
                if random() > .5
2297
                    verylong = Array(100).fill('very long *text* for **testing**, ').join(" ")
2298
                if url?
2299
                    other_page = ", or point to [yet another page](#{url})"
2300
                else
2301
                    other_page = ""
2302
                desc = """
2303
                       This is some text describing what **#{disp or id}** is.
2304
                       Here could also be an [external link](https://doc.cocalc.com).
2305
                       It might also mention `#{id}`#{other_page}.
2306

2307
                       #{verylong ? ''}
2308
                       """
2309
            else
2310
                desc = undefined
2311

2312
            path = if random() > .5 then "index.ipynb" else "subdir/"
2313
            tag = if random() > .25 then "master" else null
2314

2315

2316
            @_query
2317
                query  : "INSERT INTO compute_images"
2318
                values :
2319
                    "id      :: TEXT     " : id
2320
                    "src     :: TEXT     " : src
2321
                    "type    :: TEXT     " : 'custom'
2322
                    "desc    :: TEXT     " : desc
2323
                    "display :: TEXT     " : disp
2324
                    "path    :: TEXT     " : path
2325
                    "url     :: TEXT     " : url
2326
                    "disabled:: BOOLEAN  " : idx == 1
2327
                cb     : cb
2328

2329
        # first we wipe the table's content, then we generate some random stuff
2330
        async.series([
2331
            (cb) =>
2332
                @_query
2333
                    query  : 'DELETE FROM compute_images'
2334
                    where  : '1 = 1'
2335
                    cb     : cb
2336

2337
            (cb) =>
2338
                async.mapSeries([0..20], create, cb)
2339

2340
        ], (err) =>
2341
            dbg("all done")
2342
            opts.cb()
2343
        )
2344

2345

2346

2347
    # Delete all patches, the blobs if archived, and the syncstring object itself
2348
    # Basically this erases everything from cocalc related to the file edit history
2349
    # of a given file... except ZFS snapshots.
2350
    delete_syncstring: (opts) =>
2351
        opts = defaults opts,
2352
            string_id : required
2353
            cb        : required
2354
        if not opts.string_id or misc.len(opts.string_id) != 40
2355
            # be extra careful!
2356
            opts.cb("invalid string_id")
2357
            return
2358

2359
        locals =
2360
            syncstring : undefined
2361
            where : {"string_id = $::CHAR(40)" : opts.string_id}
2362

2363
        async.series([
2364
            (cb) =>
2365
                @_query
2366
                    query : "SELECT * FROM syncstrings"
2367
                    where : locals.where
2368
                    cb    : (err, results) =>
2369
                        if err
2370
                            cb(err)
2371
                            return
2372
                        locals.syncstring = results.rows[0]
2373
                        cb()
2374
            (cb) =>
2375
                if not locals.syncstring?
2376
                    # no syncstring with this id.
2377
                    cb(); return
2378
                # delete the syncstring record (we do this first before deleting what if references,
2379
                # since having a syncstring record referencing missing data would be a disaster, meaning
2380
                # the user could never open their file -- with this sequence it just means some wasted
2381
                # disks pace).
2382
                @_query
2383
                    query : "DELETE FROM syncstrings"
2384
                    where : locals.where
2385
                    cb    : cb
2386
            (cb) =>
2387
                if not locals.syncstring?
2388
                    # no syncstring with this id.
2389
                    cb(); return
2390
                if locals.syncstring.archived
2391
                    # is archived, so delete the blob
2392
                    @delete_blob
2393
                        uuid : locals.syncstring.archived
2394
                        cb   : cb
2395
                else
2396
                    # is not archived, so delete the patches
2397
                    @_query
2398
                        query : "DELETE FROM patches"
2399
                        where : locals.where
2400
                        timeout_s: 300
2401
                        cb    : cb
2402
        ], opts.cb)
2403

2404
    # async function
2405
    site_license_usage_stats: () =>
2406
        return await site_license_usage_stats(@)
2407

2408
    # async function
2409
    projects_using_site_license: (opts) =>
2410
        return await projects_using_site_license(@, opts)
2411

2412
    # async function
2413
    number_of_projects_using_site_license: (opts) =>
2414
        return await number_of_projects_using_site_license(@, opts)
2415

2416
    # async function
2417
    site_license_public_info: (license_id) =>
2418
        return await site_license_public_info(@, license_id)
2419

2420
    # async function
2421
    site_license_manager_set: (license_id, info) =>
2422
        return await site_license_manager_set(@, license_id, info)
2423

2424
    # async function
2425
    update_site_license_usage_log: =>
2426
        return await update_site_license_usage_log(@)
2427

2428
    # async function
2429
    matching_site_licenses: (...args) =>
2430
        return await matching_site_licenses(@, ...args)
2431

2432
    # async function
2433
    manager_site_licenses: (...args) =>
2434
        return await manager_site_licenses(@, ...args)
2435

2436
    # async function
2437
    project_datastore_set: (...args) =>
2438
        return await project_datastore_set(@, ...args)
2439

2440
    # async function
2441
    project_datastore_get: (...args) =>
2442
        return await project_datastore_get(@, ...args)
2443

2444
    # async function
2445
    project_datastore_del: (...args) =>
2446
        return await project_datastore_del(@, ...args)
2447

2448
    # async function
2449
    permanently_unlink_all_deleted_projects_of_user: (account_id_or_email_address) =>
2450
        return await permanently_unlink_all_deleted_projects_of_user(@, account_id_or_email_address)
2451

2452
    # async function
2453
    unlink_old_deleted_projects: () =>
2454
        return await unlink_old_deleted_projects(@)
2455

2456
    # async function
2457
    unlist_all_public_paths: (account_id, is_owner) =>
2458
        return await unlist_all_public_paths(@, account_id, is_owner)
2459

2460
    # async
2461
    projects_that_need_to_be_started: () =>
2462
        return await projects_that_need_to_be_started(@)
2463

2464
    # async
2465
    # this *merges* in the run_quota; it doesn't replace it.
2466
    set_run_quota: (project_id, run_quota) =>
2467
        return await @async_query
2468
            query       : "UPDATE projects"
2469
            jsonb_merge : {run_quota:run_quota}
2470
            where       : {project_id:project_id}
2471

2472
    # async -- true if they are a manager on a license or have
2473
    # any subscriptions.
2474
    is_paying_customer: (account_id) =>
2475
        return await is_paying_customer(@, account_id)
2476

2477
    # async
2478
    get_all_public_paths: (account_id) =>
2479
        return await get_all_public_paths(@, account_id)
2480

2481
    # async
2482
    # Return true if the given account is a member or
2483
    # owner of the given organization.
2484
    accountIsInOrganization: (opts) =>
2485
        result = await @async_query
2486
            query : 'SELECT COUNT(*) FROM organizations'
2487
            cache : true
2488
            where : ['organization_id :: UUID = $1', "users ? $2"]
2489
            params: [opts.organization_id, opts.account_id]
2490
        return parseInt(result?.rows?[0]?.count) > 0
2491

2492
    # given a name, returns undefined if it is not in use,
2493
    # and the account_id or organization_id that is using it
2494
    # if it is in use.
2495
    nameToAccountOrOrganization: (name) =>
2496
        name = name.toLowerCase()
2497
        result = await @async_query
2498
            query : 'SELECT account_id FROM accounts'
2499
            cache : false
2500
            where : ['LOWER(name) = $1']
2501
            params: [name]
2502
        if result.rows.length > 0
2503
            return result.rows[0].account_id
2504
        result = await @async_query
2505
            query : 'SELECT organization_id FROM organizations'
2506
            cache : false
2507
            where : ['LOWER(name) = $1']
2508
            params: [name]
2509
        if result.rows.length > 0
2510
            return result.rows[0].organization_id
2511
        return undefined
2512

2513
    # async
2514
    registrationTokens: (options, query) =>
2515
        return await registrationTokens(@, options, query)
2516

2517
    updateUnreadMessageCount: (opts) =>
2518
        return await updateUnreadMessageCount(opts)
2519

2520