1import os
  2from datetime import datetime
  3
  4import sqlalchemy.sql
  5from sqlalchemy import (
  6    BigInteger,
  7    Boolean,
  8    Column,
  9    DateTime,
 10    Enum,
 11    Float,
 12    ForeignKey,
 13    Integer,
 14    String,
 15    Table,
 16    UniqueConstraint,
 17    JSON,
 18)
 19from sqlalchemy.orm import relationship
 20from sqlalchemy.sql.expression import text
 21
 22from app.db.base import Base
 23from app.models.node import NodeStatus
 24from app.models.proxy import (
 25    InboundHostALPN,
 26    InboundHostFingerprint,
 27    InboundHostSecurity,
 28    ProxyTypes,
 29)
 30from app.models.user import ReminderType, UserDataLimitResetStrategy, UserStatus
 31
 32
 33class Admin(Base):
 34    __tablename__ = "admins"
 35
 36    id = Column(Integer, primary_key=True)
 37    username = Column(String(32), unique=True, index=True)
 38    hashed_password = Column(String(128))
 39    users = relationship("User", back_populates="admin")
 40    created_at = Column(DateTime, default=datetime.utcnow)
 41    is_sudo = Column(Boolean, default=False)
 42    password_reset_at = Column(DateTime)
 43
 44
 45inbounds_services = Table(
 46    "inbounds_services",
 47    Base.metadata,
 48    Column("inbound_id", ForeignKey("inbounds.id"), primary_key=True),
 49    Column("service_id", ForeignKey("services.id"), primary_key=True),
 50)
 51
 52users_services = Table(
 53    "users_services",
 54    Base.metadata,
 55    Column("user_id", ForeignKey("users.id"), primary_key=True),
 56    Column("service_id", ForeignKey("services.id"), primary_key=True),
 57)
 58
 59
 60class Service(Base):
 61    __tablename__ = "services"
 62    id = Column(Integer, primary_key=True)
 63    name = Column(String(64))
 64    # modifications = Column(String(1024))
 65    users = relationship("User", secondary=users_services, back_populates="services")
 66    inbounds = relationship(
 67        "Inbound", secondary=inbounds_services, back_populates="services"
 68    )
 69
 70
 71class User(Base):
 72    __tablename__ = "users"
 73
 74    id = Column(Integer, primary_key=True)
 75    username = Column(String(32), unique=True, index=True)
 76    key = Column(String(64), unique=True)
 77    enabled = Column(
 78        Boolean, nullable=False, default=True, server_default=sqlalchemy.sql.true()
 79    )
 80    services = relationship(
 81        "Service", secondary=users_services, back_populates="users", lazy="joined"
 82    )
 83    inbounds = relationship(
 84        "Inbound",
 85        secondary="join(users_services, inbounds_services, inbounds_services.c.service_id == users_services.c.service_id)"
 86        ".join(Inbound, Inbound.id == inbounds_services.c.inbound_id)",
 87        viewonly=True,
 88        distinct_target_key=True,
 89        lazy="joined",
 90    )
 91    # proxies = relationship("Proxy", back_populates="user", cascade="all, delete-orphan")
 92    status = Column(Enum(UserStatus), nullable=False, default=UserStatus.active)
 93    used_traffic = Column(BigInteger, default=0)
 94    lifetime_used_traffic = Column(
 95        BigInteger, default=0, server_default="0", nullable=False
 96    )
 97    traffic_reset_at = Column(DateTime)
 98    node_usages = relationship(
 99        "NodeUserUsage",
100        back_populates="user",
101        cascade="all,delete,delete-orphan",
102        lazy="joined",
103    )
104    notification_reminders = relationship(
105        "NotificationReminder",
106        back_populates="user",
107        cascade="all,delete,delete-orphan",
108    )
109    data_limit = Column(BigInteger)
110    data_limit_reset_strategy = Column(
111        Enum(UserDataLimitResetStrategy),
112        nullable=False,
113        default=UserDataLimitResetStrategy.no_reset,
114    )
115    ip_limit = Column(Integer, nullable=False, default=-1)
116    settings = Column(String(1024))
117    expire = Column(DateTime)
118    admin_id = Column(Integer, ForeignKey("admins.id"))
119    admin = relationship("Admin", back_populates="users")
120    sub_updated_at = Column(DateTime)
121    sub_last_user_agent = Column(String(512))
122    created_at = Column(DateTime, default=datetime.utcnow)
123    note = Column(String(500))
124    online_at = Column(DateTime)
125    on_hold_expire_duration = Column(BigInteger)
126    on_hold_timeout = Column(DateTime)
127    edit_at = Column(DateTime)
128
129
130class Inbound(Base):
131    __tablename__ = "inbounds"
132    __table_args__ = (UniqueConstraint("node_id", "tag"),)
133
134    id = Column(Integer, primary_key=True)
135    protocol = Column(Enum(ProxyTypes))
136    tag = Column(String(256), nullable=False)
137    config = Column(String(512), nullable=False)
138    node_id = Column(Integer, ForeignKey("nodes.id"), index=True)
139    node = relationship("Node", back_populates="inbounds")
140    services = relationship(
141        "Service", secondary=inbounds_services, back_populates="inbounds"
142    )
143    hosts = relationship(
144        "InboundHost", back_populates="inbound", cascade="all, delete, delete-orphan"
145    )
146
147
148class InboundHost(Base):
149    __tablename__ = "hosts"
150
151    id = Column(Integer, primary_key=True)
152    remark = Column(String(256), nullable=False)
153    address = Column(String(256), nullable=False)
154    port = Column(Integer)
155    path = Column(String(256))
156    sni = Column(String(1024))
157    host = Column(String(1024))
158    security = Column(
159        Enum(InboundHostSecurity),
160        nullable=False,
161        default=InboundHostSecurity.inbound_default,
162    )
163    alpn = Column(
164        Enum(InboundHostALPN),
165        nullable=False,
166        default=InboundHostSecurity.none,
167        server_default=InboundHostSecurity.none.name,
168    )
169    fingerprint = Column(
170        Enum(InboundHostFingerprint),
171        nullable=False,
172        default=InboundHostSecurity.none,
173        server_default=InboundHostSecurity.none.name,
174    )
175
176    mux = Column(
177        Boolean, default=False, nullable=False, server_default=sqlalchemy.sql.true()
178    )
179    fragment = Column(JSON())
180
181    inbound_id = Column(Integer, ForeignKey("inbounds.id"), nullable=False)
182    inbound = relationship("Inbound", back_populates="hosts")
183    allowinsecure = Column(Boolean, default=False)
184    is_disabled = Column(Boolean, default=False)
185
186
187class System(Base):
188    __tablename__ = "system"
189
190    id = Column(Integer, primary_key=True)
191    uplink = Column(BigInteger, default=0)
192    downlink = Column(BigInteger, default=0)
193
194
195class JWT(Base):
196    __tablename__ = "jwt"
197
198    id = Column(Integer, primary_key=True)
199    secret_key = Column(
200        String(64), nullable=False, default=lambda: os.urandom(32).hex()
201    )
202
203
204class TLS(Base):
205    __tablename__ = "tls"
206
207    id = Column(Integer, primary_key=True)
208    key = Column(String(4096), nullable=False)
209    certificate = Column(String(2048), nullable=False)
210
211
212class Node(Base):
213    __tablename__ = "nodes"
214    __table_args__ = (UniqueConstraint("address", "port"),)
215    id = Column(Integer, primary_key=True)
216    name = Column(String(256), unique=True)
217    connection_backend = Column(String(32))
218    address = Column(String(256))
219    port = Column(Integer)
220    xray_version = Column(String(32))
221    inbounds = relationship("Inbound", back_populates="node", cascade="all, delete")
222    status = Column(Enum(NodeStatus), nullable=False, default=NodeStatus.unhealthy)
223    last_status_change = Column(DateTime, default=datetime.utcnow)
224    message = Column(String(1024))
225    created_at = Column(DateTime, default=datetime.utcnow)
226    uplink = Column(BigInteger, default=0)
227    downlink = Column(BigInteger, default=0)
228    user_usages = relationship(
229        "NodeUserUsage", back_populates="node", cascade="all, delete, delete-orphan"
230    )
231    usages = relationship(
232        "NodeUsage", back_populates="node", cascade="all, delete, delete-orphan"
233    )
234    usage_coefficient = Column(
235        Float, nullable=False, server_default=text("1.0"), default=1
236    )
237
238
239class NodeUserUsage(Base):
240    __tablename__ = "node_user_usages"
241    __table_args__ = (UniqueConstraint("created_at", "user_id", "node_id"),)
242
243    id = Column(Integer, primary_key=True)
244    created_at = Column(DateTime, nullable=False)  # one hour per record
245    user_id = Column(Integer, ForeignKey("users.id"))
246    user = relationship("User", back_populates="node_usages")
247    node_id = Column(Integer, ForeignKey("nodes.id"))
248    node = relationship("Node", back_populates="user_usages")
249    used_traffic = Column(BigInteger, default=0)
250
251
252class NodeUsage(Base):
253    __tablename__ = "node_usages"
254    __table_args__ = (UniqueConstraint("created_at", "node_id"),)
255
256    id = Column(Integer, primary_key=True)
257    created_at = Column(DateTime, nullable=False)  # one hour per record
258    node_id = Column(Integer, ForeignKey("nodes.id"))
259    node = relationship("Node", back_populates="usages")
260    uplink = Column(BigInteger, default=0)
261    downlink = Column(BigInteger, default=0)
262
263
264class NotificationReminder(Base):
265    __tablename__ = "notification_reminders"
266
267    id = Column(Integer, primary_key=True)
268    user_id = Column(Integer, ForeignKey("users.id"))
269    user = relationship("User", back_populates="notification_reminders")
270    type = Column(Enum(ReminderType), nullable=False)
271    expires_at = Column(DateTime)
272    created_at = Column(DateTime, default=datetime.utcnow)