Coverage for app/backend/src/couchers/models/logging.py: 100%

1import enum 

2from datetime import datetime 

3from typing import Any 

4 

5from sqlalchemy import BigInteger, Boolean, DateTime, Enum, Float, Index, String, UniqueConstraint, func 

6from sqlalchemy import LargeBinary as Binary 

7from sqlalchemy.dialects.postgresql import JSONB 

8from sqlalchemy.orm import Mapped, mapped_column 

9from sqlalchemy.sql import expression 

10 

11from couchers.config import config 

12from couchers.models.base import Base 

13from couchers.models.rest import ClientPlatform 

14 

15 

16class EventSource(enum.Enum): 

17 backend = enum.auto() 

18 frontend = enum.auto() 

19 

20 

21class ExposureSource(enum.Enum): 

22 backend = enum.auto() 

23 client = enum.auto() 

24 

25 

26class APICall(Base, kw_only=True): 

27 """ 

28 API call logs 

29 """ 

30 

31 __tablename__ = "api_calls" 

32 __table_args__ = {"schema": "logging"} 

33 

34 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

35 

36 # whether the call was made using an api key or session cookies 

37 is_api_key: Mapped[bool] = mapped_column(Boolean, server_default=expression.false()) 

38 

39 # backend version (normally e.g. develop-31469e3), allows us to figure out which proto definitions were used 

40 # note that `default` is a python side default, not hardcoded into DB schema 

41 version: Mapped[str] = mapped_column(String, default=config.VERSION) 

42 

43 # approximate time of the call 

44 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

45 

46 # the method call name, e.g. "/org.couchers.api.core.API/ListFriends" 

47 method: Mapped[str] = mapped_column(String) 

48 

49 # gRPC status code name, e.g. FAILED_PRECONDITION, None if success 

50 status_code: Mapped[str | None] = mapped_column(String, default=None) 

51 

52 # handler duration (excluding serialization, etc) 

53 duration: Mapped[float] = mapped_column(Float) 

54 

55 # user_id of caller, None means not logged in 

56 user_id: Mapped[int | None] = mapped_column(BigInteger, default=None) 

57 

58 # sanitized request bytes 

59 request: Mapped[bytes | None] = mapped_column(Binary, default=None) 

60 

61 # sanitized response bytes 

62 response: Mapped[bytes | None] = mapped_column(Binary, default=None) 

63 

64 # whether response bytes have been truncated 

65 response_truncated: Mapped[bool] = mapped_column(Boolean, server_default=expression.false()) 

66 

67 # the exception traceback, if any 

68 traceback: Mapped[str | None] = mapped_column(String, default=None) 

69 

70 # human readable perf report 

71 perf_report: Mapped[str | None] = mapped_column(String, default=None) 

72 

73 # per-request resource accounting, covering the handler span (see couchers/perf.py) 

74 db_query_count: Mapped[int | None] = mapped_column(BigInteger, default=None) 

75 # counts only SQLAlchemy rendered insert/update/delete 

76 db_write_query_count: Mapped[int | None] = mapped_column(BigInteger, default=None) 

77 db_time_ms: Mapped[float | None] = mapped_column(Float, default=None) 

78 cpu_ms: Mapped[float | None] = mapped_column(Float, default=None) 

79 

80 client_platform: Mapped[ClientPlatform | None] = mapped_column(Enum(ClientPlatform), default=None) 

81 

82 # details of the browser, if available 

83 ip_address: Mapped[str | None] = mapped_column(String, default=None) 

84 user_agent: Mapped[str | None] = mapped_column(String, default=None) 

85 

86 sofa: Mapped[str | None] = mapped_column(String, default=None) 

87 

88 

89class EventLog(Base, kw_only=True): 

90 """ 

91 Analytics event log for tracking user behavior and business metrics. 

92 

93 Append-only table for ELT extraction. Do not query this table for user-facing features. 

94 """ 

95 

96 __tablename__ = "event_log" 

97 

98 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

99 

100 # when the row was inserted into the DB 

101 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

102 

103 # when the event actually happened (same as created for backend; may differ for frontend events) 

104 occurred: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

105 

106 # backend/frontend version 

107 version: Mapped[str] = mapped_column(String, default=config.VERSION) 

108 

109 # sofa, null for background/system events 

110 sofa: Mapped[str | None] = mapped_column(String, default=None) 

111 

112 # hierarchical event type, e.g. "host_request.sent", "account.login" 

113 event_type: Mapped[str] = mapped_column(String) 

114 

115 # user who triggered the event, nullable for system events 

116 user_id: Mapped[int | None] = mapped_column(BigInteger, default=None) 

117 

118 # flexible event-specific properties 

119 properties: Mapped[dict[str, Any]] = mapped_column(JSONB) 

120 

121 # numeric value (duration, count, etc.) 

122 value: Mapped[float] = mapped_column(Float, server_default="1.0", default=1.0) 

123 

124 # where the event originated 

125 source: Mapped[EventSource] = mapped_column(Enum(EventSource)) 

126 

127 __table_args__ = ( 

128 Index("ix_logging_event_log_created", "created"), 

129 Index("ix_logging_event_log_event_type_created", "event_type", "created"), 

130 Index("ix_logging_event_log_user_id_created", "user_id", "created"), 

131 {"schema": "logging"}, 

132 ) 

133 

134 

135class ExperimentExposure(Base, kw_only=True): 

136 """ 

137 Records the first time a user is exposed to a particular experiment variation. 

138 

139 Populated by GrowthBook's on_experiment_viewed callback. One row per 

140 (user, experiment, variation) - subsequent exposures collide on the 

141 unique constraint and are dropped via ON CONFLICT DO NOTHING, so 

142 `created` and `data` reflect the first exposure. 

143 """ 

144 

145 __tablename__ = "experiment_exposures" 

146 

147 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

148 

149 # when the first exposure was recorded 

150 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

151 

152 # backend version when the first exposure was recorded 

153 version: Mapped[str] = mapped_column(String, default=config.VERSION) 

154 

155 # user exposed to the experiment 

156 user_id: Mapped[int] = mapped_column(BigInteger) 

157 

158 # experiment identifier from GrowthBook 

159 experiment_key: Mapped[str] = mapped_column(String) 

160 

161 # the variation the user was bucketed into 

162 variation_id: Mapped[int] = mapped_column(BigInteger) 

163 

164 source: Mapped[ExposureSource] = mapped_column(Enum(ExposureSource)) 

165 

166 # remaining GrowthBook fields (variation_key, hash_attribute, hash_value, 

167 # bucket, in_experiment, feature_id, sticky_bucket_used, etc.) 

168 data: Mapped[dict[str, Any]] = mapped_column(JSONB) 

169 

170 __table_args__ = ( 

171 UniqueConstraint("user_id", "experiment_key", "variation_id", name="uq_experiment_exposures_user_exp_var"), 

172 Index("ix_logging_experiment_exposures_experiment_key_created", "experiment_key", "created"), 

173 Index("ix_logging_experiment_exposures_user_id_created", "user_id", "created"), 

174 {"schema": "logging"}, 

175 ) 

176 

177 

178class FeatureUsage(Base, kw_only=True): 

179 """ 

180 Append-only log of feature flag evaluations. 

181 

182 Populated by GrowthBook's on_feature_usage callback - one row per check. 

183 """ 

184 

185 __tablename__ = "feature_usage" 

186 

187 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

188 

189 # when the feature was checked 

190 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

191 

192 # user the feature was checked for 

193 user_id: Mapped[int] = mapped_column(BigInteger) 

194 

195 # feature identifier from GrowthBook 

196 feature_key: Mapped[str] = mapped_column(String) 

197 

198 # the feature value the user received 

199 value: Mapped[Any] = mapped_column(JSONB) 

200 

201 __table_args__ = ( 

202 Index("ix_logging_feature_usage_feature_key_time", "feature_key", "time"), 

203 Index("ix_logging_feature_usage_user_id_time", "user_id", "time"), 

204 {"schema": "logging"}, 

205 ) 

206 

207 

208class NonvisibleUserAccessType(enum.Enum): 

209 login_attempt = enum.auto() 

210 profile_view = enum.auto() 

211 ghost_served = enum.auto() 

212 

213 

214class NonvisibleUserState(enum.Enum): 

215 banned = enum.auto() 

216 shadowed = enum.auto() 

217 deleted = enum.auto() 

218 

219 

220class NonvisibleUserAccess(Base, kw_only=True): 

221 __tablename__ = "nonvisible_user_access" 

222 

223 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

224 

225 time: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

226 

227 version: Mapped[str] = mapped_column(String, default=config.VERSION) 

228 

229 access_type: Mapped[NonvisibleUserAccessType] = mapped_column(Enum(NonvisibleUserAccessType)) 

230 

231 target_user_id: Mapped[int] = mapped_column(BigInteger) 

232 target_state: Mapped[NonvisibleUserState] = mapped_column(Enum(NonvisibleUserState)) 

233 

234 actor_user_id: Mapped[int | None] = mapped_column(BigInteger, default=None) 

235 

236 ip_address: Mapped[str | None] = mapped_column(String, default=None) 

237 user_agent: Mapped[str | None] = mapped_column(String, default=None) 

238 sofa: Mapped[str | None] = mapped_column(String, default=None) 

239 

240 __table_args__ = ( 

241 Index("ix_logging_nonvisible_user_access_target_user_id_time", "target_user_id", "time"), 

242 Index("ix_logging_nonvisible_user_access_actor_user_id_time", "actor_user_id", "time"), 

243 Index("ix_logging_nonvisible_user_access_sofa", "sofa"), 

244 Index("ix_logging_nonvisible_user_access_ip_address", "ip_address"), 

245 {"schema": "logging"}, 

246 )