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 created_at = Column(DateTime, default=datetime.utcnow)
210 note = Column(String(500))
211 online_at = Column(DateTime)
212 edit_at = Column(DateTime)
213
214 @property
215 def service_ids(self):
216 return [service.id for service in self.services]
217
218 @hybrid_property
219 def expired(self):
220 if self.expire_strategy == "fixed_date":
221 return self.expire_date < datetime.utcnow()
222 return False
223
224 @expired.expression
225 def expired(cls):
226 return and_(
227 cls.expire_strategy == "fixed_date", cls.expire_date < func.now()
228 )
229
230 @hybrid_property
231 def data_limit_reached(self):
232 if self.data_limit is not None:
233 return self.used_traffic >= self.data_limit
234 return False
235
236 @data_limit_reached.expression
237 def data_limit_reached(cls):
238 return and_(
239 cls.data_limit.isnot(None), cls.used_traffic >= cls.data_limit
240 )
241
242 @hybrid_property
243 def is_active(self):
244 return (
245 self.enabled
246 and not self.expired
247 and not self.data_limit_reached
248 and not self.removed
249 )
250
251 @is_active.expression
252 def is_active(cls):
253 return and_(
254 cls.enabled == True,
255 ~cls.expired,
256 ~cls.data_limit_reached,
257 ~cls.removed,
258 )
259
260 @property
261 def status(self):
262 return UserStatus.ACTIVE if self.is_active else UserStatus.INACTIVE
263
264 @property
265 def subscription_url(self):
266 prefix = self.admin.subscription_url_prefix or SUBSCRIPTION_URL_PREFIX
267 return (
268 prefix.replace("*", secrets.token_hex(8))
269 + f"/sub/{self.username}/{self.key}"
270 )
271
272 @hybrid_property
273 def owner_username(self):
274 return self.admin.username
275
276
277class Backend(Base):
278 __tablename__ = "backends"
279
280 id = Column(Integer, primary_key=True)
281 name = Column(String(64), nullable=False)
282 node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
283 node = relationship("Node", back_populates="backends")
284 backend_type = Column(String(32), nullable=False)
285 version = Column(String(32))
286 running = Column(Boolean, default=True, nullable=False)
287
288
289class Inbound(Base):
290 __tablename__ = "inbounds"
291 __table_args__ = (UniqueConstraint("node_id", "tag"),)
292
293 id = Column(Integer, primary_key=True)
294 protocol = Column(Enum(ProxyTypes))
295 tag = Column(String(256), nullable=False)
296 config = Column(String(512), nullable=False)
297 node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
298 node = relationship("Node", back_populates="inbounds")
299 services = relationship(
300 "Service", secondary=inbounds_services, back_populates="inbounds"
301 )
302 hosts = relationship(
303 "InboundHost",
304 back_populates="inbound",
305 cascade="all, delete, delete-orphan",
306 )
307
308 @property
309 def service_ids(self):
310 return [service.id for service in self.services]
311
312
313class InboundHost(Base):
314 __tablename__ = "hosts"
315
316 id = Column(Integer, primary_key=True)
317 remark = Column(String(256), nullable=False)
318 address = Column(String(256), nullable=False)
319 port = Column(Integer)
320 path = Column(String(256))
321 sni = Column(String(1024))
322 host = Column(String(1024))
323 security = Column(
324 Enum(InboundHostSecurity),
325 nullable=False,
326 default=InboundHostSecurity.inbound_default,
327 )
328 alpn = Column(
329 String(32),
330 server_default=sqlalchemy.sql.null(),
331 )
332 fingerprint = Column(
333 Enum(InboundHostFingerprint),
334 nullable=False,
335 default=InboundHostSecurity.none,
336 server_default=InboundHostSecurity.none.name,
337 )
338
339 mux = Column(
340 Boolean,
341 default=False,
342 nullable=False,
343 server_default=sqlalchemy.sql.false(),
344 )
345 fragment = Column(JSON())
346
347 inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False)
348 inbound = relationship("Inbound", back_populates="hosts")
349 allowinsecure = Column(Boolean, default=False)
350 is_disabled = Column(Boolean, default=False)
351 weight = Column(Integer, default=1, nullable=False, server_default="1")
352
353
354class System(Base):
355 __tablename__ = "system"
356
357 id = Column(Integer, primary_key=True)
358 uplink = Column(BigInteger, default=0)
359 downlink = Column(BigInteger, default=0)
360
361
362class JWT(Base):
363 __tablename__ = "jwt"
364
365 id = Column(Integer, primary_key=True)
366 secret_key = Column(
367 String(64), nullable=False, default=lambda: os.urandom(32).hex()
368 )
369
370
371class TLS(Base):
372 __tablename__ = "tls"
373
374 id = Column(Integer, primary_key=True)
375 key = Column(String(4096), nullable=False)
376 certificate = Column(String(2048), nullable=False)
377
378
379class Node(Base):
380 __tablename__ = "nodes"
381 __table_args__ = (UniqueConstraint("address", "port"),)
382 id = Column(Integer, primary_key=True)
383 name = Column(String(256), unique=True)
384 connection_backend = Column(String(32))
385 address = Column(String(256))
386 port = Column(Integer)
387 xray_version = Column(String(32))
388 inbounds = relationship(
389 "Inbound", back_populates="node", cascade="all, delete"
390 )
391 backends = relationship(
392 "Backend", back_populates="node", cascade="all, delete"
393 )
394 status = Column(
395 Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy
396 )
397 last_status_change = Column(DateTime, default=datetime.utcnow)
398 message = Column(String(1024))
399 created_at = Column(DateTime, default=datetime.utcnow)
400 uplink = Column(BigInteger, default=0)
401 downlink = Column(BigInteger, default=0)
402 user_usages = relationship(
403 "NodeUserUsage",
404 back_populates="node",
405 cascade="all, delete, delete-orphan",
406 )
407 usages = relationship(
408 "NodeUsage",
409 back_populates="node",
410 cascade="all, delete, delete-orphan",
411 )
412 usage_coefficient = Column(
413 Float, nullable=False, server_default=text("1.0"), default=1
414 )
415
416 @property
417 def inbound_ids(self):
418 return [inbound.id for inbound in self.inbounds]
419
420
421class NodeUserUsage(Base):
422 __tablename__ = "node_user_usages"
423 __table_args__ = (UniqueConstraint("created_at", "user_id", "node_id"),)
424
425 id = Column(Integer, primary_key=True)
426 created_at = Column(DateTime, nullable=False) # one hour per record
427 user_id = Column(Integer, ForeignKey("users.id"))
428 user = relationship("User", back_populates="node_usages")
429 node_id = Column(Integer, ForeignKey("nodes.id"))
430 node = relationship("Node", back_populates="user_usages")
431 used_traffic = Column(BigInteger, default=0)
432
433
434class NodeUsage(Base):
435 __tablename__ = "node_usages"
436 __table_args__ = (UniqueConstraint("created_at", "node_id"),)
437
438 id = Column(Integer, primary_key=True)
439 created_at = Column(DateTime, nullable=False) # one hour per record
440 node_id = Column(Integer, ForeignKey("nodes.id"))
441 node = relationship("Node", back_populates="usages")
442 uplink = Column(BigInteger, default=0)
443 downlink = Column(BigInteger, default=0)
444
445
446class NotificationReminder(Base):
447 __tablename__ = "notification_reminders"
448
449 id = Column(Integer, primary_key=True)
450 user_id = Column(Integer, ForeignKey("users.id"))
451 user = relationship("User", back_populates="notification_reminders")
452 type = Column(Enum(ReminderType), nullable=False)
453 expires_at = Column(DateTime)
454 created_at = Column(DateTime, default=datetime.utcnow)
455
456
457class Settings(Base):
458 __tablename__ = "settings"
459
460 id = Column(Integer, primary_key=True, server_default=text("0"))
461 subscription = Column(JSON, nullable=False)
462 telegram = Column(JSON)