1
import { Table } from "./types";
2

3
export const retentionModels = {
4
  file_access_log: {
5
    title: "File Access Log - Retention",
6
    description:
7
      "Number of accounts in the cohort with an entry in the file_access_log during each period.",
8
  },
9
  "file_access_log:all": {
10
    title: "File Access Log - Active Users",
11
    description:
12
      "Number of accounts with an entry in the file_access_log during each period.",
13
  },
14
  openai_chatgpt_log: {
15
    title: "ChatGPT - Retention",
16
    description:
17
      "Number of accounts in the cohort that used chatgpt during period.",
18
  },
19
  "openai_chatgpt_log:all": {
20
    title: "ChatGPT - Active Users",
21
    description:
22
      "Number of accounts with an entry in the openai_chatgpt_log during each period.",
23
  },
24
  project_log: {
25
    title: "Project Log - Retention",
26
    description:
27
      "Number of accounts in the cohort with an entry in the project log during each period.",
28
  },
29
  "project_log:all": {
30
    title: "Project Log - Active Users",
31
    description:
32
      "Number of accounts with an entry in the project log during each period.",
33
  },
34
  user_tracking: {
35
    title: "User Tracking - Retention",
36
    description:
37
      "Number of accounts in the cohort with an entry in the user tracking table during each period.",
38
  },
39
  "user_tracking:all": {
40
    title: "User Tracking - Active Users",
41
    description:
42
      "Number of accounts with an entry in the user tracking table during each period.",
43
  },
44
  "client_error_log:all": {
45
    title: "Client Error Log - Active Users",
46
    description:
47
      "Number of accounts with an entry in the client error log during each period.",
48
  },
49
  "webapp_errors:all": {
50
    title: "Webapp Errors - Active Users",
51
    description:
52
      "Number of accounts with an entry in the webapp_errors log during each period.",
53
  },
54
};
55

56
export type RetentionModel = keyof typeof retentionModels;
57

58
Table({
59
  name: "crm_retention",
60
  fields: {
61
    start: {
62
      title: "Cohort Start",
63
      desc: "The cohort consists of accounts created >= start and < end, unless otherwise stated by the model definition.  E.g., if the model name ends in :all, then the cohort is simply all accounts ever created.",
64
      type: "timestamp",
65
    },
66
    stop: {
67
      title: "Cohort Stop",
68
      desc: "Defines the stoping timestamp of this cohort",
69
      type: "timestamp",
70
    },
71
    model: {
72
      desc: "The model that defines active users, e.g., 'file_access_log', 'file_access_log:all'. This determines the cohort and how active is measured, via code that is hardcoded in cocalc's source code.  E.g., 'project_log:paid' might mean that we use entries in the project_log to define activity and restrict to paying customers only to define the cohort.",
73
      type: "string",
74
    },
75
    period: {
76
      title: "Active Period",
77
      desc: "We measure activity of the cohort for the intervals [start,start+period), [start+period, start+2*period), [start+2*period, start+3*period), ..., [last_start_time, last_start_time+period), where last_start_time is defined below.",
78
      pg_type: "interval",
79
      type: "string",
80
    },
81
    last_start_time: {
82
      title: "Start of last data",
83
      desc: "The start time of the last interval that's in the active array. We can use this to easily tell if there is missing data or not.",
84
      type: "timestamp",
85
    },
86
    active: {
87
      title: "Active Accounts",
88
      desc: "The number of distinct accounts in the cohort that were active during each period.  This is an array of integers.",
89
      pg_type: "integer[]",
90
      type: "array",
91
    },
92
    size: {
93
      title: "Cohort Size",
94
      desc: "The number of accounts in this cohort.  In case of ':all' models, this is total number of users that were active during all periods considered.",
95
      type: "integer",
96
    },
97
  },
98
  rules: {
99
    desc: "CRM Retention Table",
100
    primary_key: ["start", "stop", "model", "period"],
101
    user_query: {
102
      get: {
103
        pg_where: [],
104
        admin: true,
105
        fields: {
106
          start: null,
107
          stop: null,
108
          model: null,
109
          period: null,
110
          last_start_time: null,
111
          active: null,
112
          size: null,
113
        },
114
        async check_hook(db, obj, _account_id, _project_id, cb) {
115
          // The check hook ensures the data that is being requested exists
116
          // and is up to date.  It's not really "checking" for validity, but
117
          // that it is up to date.
118
          try {
119
            await db.updateRetentionData(obj);
120
            cb();
121
          } catch (err) {
122
            cb(err);
123
          }
124
        },
125
      },
126
    },
127
  },
128
});
129

130