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