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