1import os
2import secrets
3from datetime import datetime
4
5import sqlalchemy.sql
6from sqlalchemy import (
7 BigInteger,
8 Boolean,
9 Column,
10 DateTime,
11 Enum,
12 Float,
13 ForeignKey,
14 Integer,
15 String,
16 Table,
17 UniqueConstraint,
18 JSON,
19 and_,
20 func,
21 select,
22)
23from sqlalchemy.ext.hybrid import hybrid_property
24from sqlalchemy.orm import relationship, column_property
25from sqlalchemy.sql.expression import text
26
27from app.config.env import SUBSCRIPTION_URL_PREFIX
28from app.db.base import Base
29from app.models.node import NodeStatus
30from app.models.proxy import (
31 InboundHostALPN,
32 InboundHostFingerprint,
33 InboundHostSecurity,
34 ProxyTypes,
35)
36from app.models.user import (
37 ReminderType,
38 UserDataUsageResetStrategy,
39 UserStatus,
40 UserExpireStrategy,
41)
42
43admins_services = Table(
44 "admins_services",
45 Base.metadata,
46 Column("admin_id", ForeignKey("admins.id"), primary_key=True),
47 Column("service_id", ForeignKey("services.id"), primary_key=True),
48)
49
50inbounds_services = Table(
51 "inbounds_services",
52 Base.metadata,
53 Column("inbound_id", ForeignKey("inbounds.id"), primary_key=True),
54 Column("service_id", ForeignKey("services.id"), primary_key=True),
55)
56
57users_services = Table(
58 "users_services",
59 Base.metadata,
60 Column("user_id", ForeignKey("users.id"), primary_key=True),
61 Column("service_id", ForeignKey("services.id"), primary_key=True),
62)
63
64
65class Admin(Base):
66 __tablename__ = "admins"
67
68 id = Column(Integer, primary_key=True)
69 username = Column(String(32), unique=True, index=True)
70 hashed_password = Column(String(128))
71 users = relationship("User", back_populates="admin")
72 services = relationship(
73 "Service",
74 secondary=admins_services,
75 back_populates="admins",
76 lazy="joined",
77 )
78 enabled = Column(
79 Boolean,
80 nullable=False,
81 default=True,
82 server_default=sqlalchemy.sql.true(),
83 )
84 all_services_access = Column(
85 Boolean,
86 nullable=False,
87 default=False,
88 server_default=sqlalchemy.sql.false(),
89 )
90 modify_users_access = Column(
91 Boolean,
92 nullable=False,
93 default=True,
94 server_default=sqlalchemy.sql.true(),
95 )
96 created_at = Column(DateTime, default=datetime.utcnow)
97 is_sudo = Column(Boolean, default=False)
98 password_reset_at = Column(DateTime)
99 subscription_url_prefix = Column(
100 String(256),
101 nullable=False,
102 default="",
103 server_default=sqlalchemy.sql.text(""),
104 )
105
106 @property
107 def service_ids(self):
108 return [service.id for service in self.services]
109
110 @classmethod
111 def __declare_last__(cls):
112 cls.users_data_usage = column_property(
113 select(func.coalesce(func.sum(User.lifetime_used_traffic), 0))
114 .where(User.admin_id == cls.id)
115 .correlate_except(User)
116 .scalar_subquery()
117 )
118
119
120class Service(Base):
121 __tablename__ = "services"
122 id = Column(Integer, primary_key=True)
123 name = Column(String(64))
124 admins = relationship(
125 "Admin", secondary=admins_services, back_populates="services"
126 )
127 users = relationship(
128 "User", secondary=users_services, back_populates="services"
129 )
130 inbounds = relationship(
131 "Inbound", secondary=inbounds_services, back_populates="services"
132 )
133
134 @property
135 def inbound_ids(self):
136 return [inbound.id for inbound in self.inbounds]
137
138 @property
139 def user_ids(self):
140 return [user.id for user in self.users]
141
142
143class User(Base):
144 __tablename__ = "users"
145
146 id = Column(Integer, primary_key=True)
147 username = Column(String(32), unique=True, index=True)
148 key = Column(String(64), unique=True)
149 activated = Column(Boolean, nullable=False, default=True)
150 enabled = Column(
151 Boolean,
152 nullable=False,
153 default=True,
154 server_default=sqlalchemy.sql.true(),
155 )
156 removed = Column(
157 Boolean,
158 nullable=False,
159 default=False,
160 server_default=sqlalchemy.sql.false(),
161 )
162 services = relationship(
163 "Service",
164 secondary=users_services,
165 back_populates="users",
166 lazy="joined",
167 )
168 inbounds = relationship(
169 "Inbound",
170 secondary="join(users_services, inbounds_services, inbounds_services.c.service_id == users_services.c.service_id)"
171 ".join(Inbound, Inbound.id == inbounds_services.c.inbound_id)",
172 viewonly=True,
173 distinct_target_key=True,
174 )
175 used_traffic = Column(BigInteger, default=0)
176 lifetime_used_traffic = Column(
177 BigInteger, default=0, server_default="0", nullable=False
178 )
179 traffic_reset_at = Column(DateTime)
180 node_usages = relationship(
181 "NodeUserUsage",
182 back_populates="user",
183 cascade="all,delete,delete-orphan",
184 )
185 notification_reminders = relationship(
186 "NotificationReminder",
187 back_populates="user",
188 cascade="all,delete,delete-orphan",
189 )
190 data_limit = Column(BigInteger)
191 data_limit_reset_strategy = Column(
192 Enum(UserDataUsageResetStrategy),
193 nullable=False,
194 default=UserDataUsageResetStrategy.no_reset,
195 )
196 ip_limit = Column(Integer, nullable=False, default=-1)
197 settings = Column(String(1024))
198 expire_strategy = Column(
199 Enum(UserExpireStrategy),
200 nullable=False,
201 default=UserExpireStrategy.NEVER,
202 )
203 expire_date = Column(DateTime)
204 usage_duration = Column(BigInteger)
205 activation_deadline = Column(DateTime)
206 admin_id = Column(Integer, ForeignKey("admins.id"))
207 admin = relationship("Admin", back_populates="users")
208 sub_updated_at = Column(DateTime)
209 sub_last_user_agent = Column(String(512))
210 created_at = Column(DateTime, default=datetime.utcnow)
211 note = Column(String(500))
212 online_at = Column(DateTime)
213 edit_at = Column(DateTime)
214
215 @property
216 def service_ids(self):
217 return [service.id for service in self.services]
218
219 @hybrid_property
220 def expired(self):
221 if self.expire_strategy == "fixed_date":
222 return self.expire_date < datetime.utcnow()
223 return False
224
225 @expired.expression
226 def expired(cls):
227 return and_(
228 cls.expire_strategy == "fixed_date", cls.expire_date < func.now()
229 )
230
231 @hybrid_property
232 def data_limit_reached(self):
233 if self.data_limit is not None:
234 return self.used_traffic >= self.data_limit
235 return False
236
237 @data_limit_reached.expression
238 def data_limit_reached(cls):
239 return and_(
240 cls.data_limit.isnot(None), cls.used_traffic >= cls.data_limit
241 )
242
243 @hybrid_property
244 def is_active(self):
245 return (
246 self.enabled
247 and not self.expired
248 and not self.data_limit_reached
249 and not self.removed
250 )
251
252 @is_active.expression
253 def is_active(cls):
254 return and_(
255 cls.enabled == True,
256 ~cls.expired,
257 ~cls.data_limit_reached,
258 ~cls.removed,
259 )
260
261 @property
262 def status(self):
263 return UserStatus.ACTIVE if self.is_active else UserStatus.INACTIVE
264
265 @property
266 def subscription_url(self):
267 prefix = self.admin.subscription_url_prefix or SUBSCRIPTION_URL_PREFIX
268 return (
269 prefix.replace("*", secrets.token_hex(8))
270 + f"/sub/{self.username}/{self.key}"
271 )
272
273 @hybrid_property
274 def owner_username(self):
275 return self.admin.username
276
277
278class Inbound(Base):
279 __tablename__ = "inbounds"
280 __table_args__ = (UniqueConstraint("node_id", "tag"),)
281
282 id = Column(Integer, primary_key=True)
283 protocol = Column(Enum(ProxyTypes))
284 tag = Column(String(256), nullable=False)
285 config = Column(String(512), nullable=False)
286 node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
287 node = relationship("Node", back_populates="inbounds")
288 services = relationship(
289 "Service", secondary=inbounds_services, back_populates="inbounds"
290 )
291 hosts = relationship(
292 "InboundHost",
293 back_populates="inbound",
294 cascade="all, delete, delete-orphan",
295 )
296
297 @property
298 def service_ids(self):
299 return [service.id for service in self.services]
300
301
302class InboundHost(Base):
303 __tablename__ = "hosts"
304
305 id = Column(Integer, primary_key=True)
306 remark = Column(String(256), nullable=False)
307 address = Column(String(256), nullable=False)
308 port = Column(Integer)
309 path = Column(String(256))
310 sni = Column(String(1024))
311 host = Column(String(1024))
312 security = Column(
313 Enum(InboundHostSecurity),
314 nullable=False,
315 default=InboundHostSecurity.inbound_default,
316 )
317 alpn = Column(
318 Enum(InboundHostALPN),
319 nullable=False,
320 default=InboundHostSecurity.none,
321 server_default=InboundHostSecurity.none.name,
322 )
323 fingerprint = Column(
324 Enum(InboundHostFingerprint),
325 nullable=False,
326 default=InboundHostSecurity.none,
327 server_default=InboundHostSecurity.none.name,
328 )
329
330 mux = Column(
331 Boolean,
332 default=False,
333 nullable=False,
334 server_default=sqlalchemy.sql.false(),
335 )
336 fragment = Column(JSON())
337
338 inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False)
339 inbound = relationship("Inbound", back_populates="hosts")
340 allowinsecure = Column(Boolean, default=False)
341 is_disabled = Column(Boolean, default=False)
342
343
344class System(Base):
345 __tablename__ = "system"
346
347 id = Column(Integer, primary_key=True)
348 uplink = Column(BigInteger, default=0)
349 downlink = Column(BigInteger, default=0)
350
351
352class JWT(Base):
353 __tablename__ = "jwt"
354
355 id = Column(Integer, primary_key=True)
356 secret_key = Column(
357 String(64), nullable=False, default=lambda: os.urandom(32).hex()
358 )
359
360
361class TLS(Base):
362 __tablename__ = "tls"
363
364 id = Column(Integer, primary_key=True)
365 key = Column(String(4096), nullable=False)
366 certificate = Column(String(2048), nullable=False)
367
368
369class Node(Base):
370 __tablename__ = "nodes"
371 __table_args__ = (UniqueConstraint("address", "port"),)
372 id = Column(Integer, primary_key=True)
373 name = Column(String(256), unique=True)
374 connection_backend = Column(String(32))
375 address = Column(String(256))
376 port = Column(Integer)
377 xray_version = Column(String(32))
378 inbounds = relationship(
379 "Inbound", back_populates="node", cascade="all, delete"
380 )
381 status = Column(
382 Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy
383 )
384 last_status_change = Column(DateTime, default=datetime.utcnow)
385 message = Column(String(1024))
386 created_at = Column(DateTime, default=datetime.utcnow)
387 uplink = Column(BigInteger, default=0)
388 downlink = Column(BigInteger, default=0)
389 user_usages = relationship(
390 "NodeUserUsage",
391 back_populates="node",
392 cascade="all, delete, delete-orphan",
393 )
394 usages = relationship(
395 "NodeUsage",
396 back_populates="node",
397 cascade="all, delete, delete-orphan",
398 )
399 usage_coefficient = Column(
400 Float, nullable=False, server_default=text("1.0"), default=1
401 )
402
403 @property
404 def inbound_ids(self):
405 return [inbound.id for inbound in self.inbounds]
406
407
408class NodeUserUsage(Base):
409 __tablename__ = "node_user_usages"
410 __table_args__ = (UniqueConstraint("created_at", "user_id", "node_id"),)
411
412 id = Column(Integer, primary_key=True)
413 created_at = Column(DateTime, nullable=False) # one hour per record
414 user_id = Column(Integer, ForeignKey("users.id"))
415 user = relationship("User", back_populates="node_usages")
416 node_id = Column(Integer, ForeignKey("nodes.id"))
417 node = relationship("Node", back_populates="user_usages")
418 used_traffic = Column(BigInteger, default=0)
419
420
421class NodeUsage(Base):
422 __tablename__ = "node_usages"
423 __table_args__ = (UniqueConstraint("created_at", "node_id"),)
424
425 id = Column(Integer, primary_key=True)
426 created_at = Column(DateTime, nullable=False) # one hour per record
427 node_id = Column(Integer, ForeignKey("nodes.id"))
428 node = relationship("Node", back_populates="usages")
429 uplink = Column(BigInteger, default=0)
430 downlink = Column(BigInteger, default=0)
431
432
433class NotificationReminder(Base):
434 __tablename__ = "notification_reminders"
435
436 id = Column(Integer, primary_key=True)
437 user_id = Column(Integer, ForeignKey("users.id"))
438 user = relationship("User", back_populates="notification_reminders")
439 type = Column(Enum(ReminderType), nullable=False)
440 expires_at = Column(DateTime)
441 created_at = Column(DateTime, default=datetime.utcnow)
442
443
444class Settings(Base):
445 __tablename__ = "settings"
446
447 id = Column(Integer, primary_key=True, server_default=text("0"))
448 subscription = Column(JSON, nullable=False)
449 telegram = Column(JSON)