Spaces:
Running
Running
| # models_sql.py | |
| # ========================================== | |
| # 🗄️ SQL 数据库模型定义 | |
| # ========================================== | |
| # 🚀 P1性能优化：添加复合索引加速查询 | |
| # ========================================== | |
| from sqlalchemy import Column, Integer, String, DateTime, Boolean, Index, func, Text, DDL, event | |
| from sqlalchemy.orm import declarative_base | |
| import datetime | |
| Base = declarative_base() | |
| class OpenApiOperation(Base): | |
| __tablename__ = "open_api_operations" | |
| request_key = Column(String(64), primary_key=True) | |
| request_hash = Column(String(64), nullable=False) | |
| transaction_id = Column(String(100), nullable=False, unique=True) | |
| response_payload = Column(Text, nullable=False) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False) | |
| event.listen(OpenApiOperation.__table__, "after_create", | |
| DDL("ALTER TABLE %(fullname)s ENABLE ROW LEVEL SECURITY").execute_if(dialect="postgresql")) | |
| event.listen(OpenApiOperation.__table__, "after_create", | |
| DDL("REVOKE ALL ON TABLE %(fullname)s FROM PUBLIC").execute_if(dialect="postgresql")) | |
| event.listen(OpenApiOperation.__table__, "after_create", DDL(""" | |
| DO $$ BEGIN | |
| IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'anon') THEN | |
| REVOKE ALL ON TABLE %(fullname)s FROM anon; | |
| END IF; | |
| IF EXISTS (SELECT FROM pg_roles WHERE rolname = 'authenticated') THEN | |
| REVOKE ALL ON TABLE %(fullname)s FROM authenticated; | |
| END IF; | |
| END; $$ | |
| """).execute_if(dialect="postgresql")) | |
| class ResourcePublication(Base): | |
| """同次资源发布的持久原始请求和恢复内容；不参与资金变更。""" | |
| __tablename__ = "resource_publications" | |
| request_key = Column(String(64), primary_key=True) | |
| item_id = Column(String(100), nullable=False, unique=True) | |
| author = Column(String, nullable=False) | |
| request_payload = Column(Text, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False) | |
| class TaskPublication(Base): | |
| """与任务资金冻结同提交的待发布记录；保留原始内容供失败恢复。""" | |
| __tablename__ = "task_publications" | |
| task_id = Column(String(64), primary_key=True) | |
| publisher = Column(String, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False) | |
| class TaskRevision(Base): | |
| """任务编辑的持久字段投影，与改价资金同事务；版本按钱包锁串行递增。""" | |
| __tablename__ = "task_revisions" | |
| task_id = Column(String(64), primary_key=True) | |
| revision = Column(Integer, primary_key=True) | |
| publisher = Column(String, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow, nullable=False) | |
| class TaskAssignment(Base): | |
| """指派和订金SQL提交后的状态投影，不能因JSON失败再次扣订金。""" | |
| __tablename__ = "task_assignments" | |
| task_id = Column(String(64), primary_key=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| deposit = Column(Integer, nullable=False) | |
| assigned_at = Column(Integer, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| class TaskDelivery(Base): | |
| """交付/退回最新版本先持久保存，JSON失败不能丢失成果或恢复旧验收。""" | |
| __tablename__ = "task_deliveries" | |
| task_id = Column(String(64), primary_key=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| revision = Column(Integer, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| updated_at = Column(Integer, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| class TaskSettlement(Base): | |
| """验收付款和待完成状态同事务；一任务只结算一次。""" | |
| __tablename__ = "task_settlements" | |
| task_id = Column(String(64), primary_key=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| amount = Column(Integer, nullable=False) | |
| deposit = Column(Integer, nullable=False) | |
| payment_tx_id = Column(String, nullable=False) | |
| income_tx_id = Column(String, nullable=False) | |
| completed_at = Column(Integer, nullable=False) | |
| feedback = Column(Text, nullable=True) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| class TaskResolution(Base): | |
| """仲裁资金和裁决同提交；一任务只允许一次最终分配。""" | |
| __tablename__ = "task_resolutions" | |
| task_id = Column(String(64), primary_key=True) | |
| dispute_id = Column(String(64), nullable=False, unique=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| amount = Column(Integer, nullable=False) | |
| deposit = Column(Integer, nullable=False) | |
| publisher_refund = Column(Integer, nullable=False) | |
| assignee_income = Column(Integer, nullable=False) | |
| initiator_role = Column(String(16), nullable=False) | |
| resolution = Column(String(32), nullable=False) | |
| ratio = Column(Integer, nullable=True) | |
| note = Column(Text, nullable=True) | |
| resolved_by = Column(String, nullable=False) | |
| resolved_at = Column(Integer, nullable=False) | |
| payment_tx_id = Column(String, nullable=False) | |
| refund_tx_id = Column(String, nullable=False) | |
| income_tx_id = Column(String, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| class TaskDisputeRecord(Base): | |
| """申诉先持久保存；JSON同步期间也禁止普通验收结算。""" | |
| __tablename__ = "task_dispute_records" | |
| task_id = Column(String(64), primary_key=True) | |
| dispute_id = Column(String(64), nullable=False, unique=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| created_at = Column(Integer, nullable=False) | |
| class TaskDisputeResponseRecord(Base): | |
| """首次申诉回应持久记录；一个任务只保存一份原始回应。""" | |
| __tablename__ = "task_dispute_responses" | |
| task_id = Column(String(64), primary_key=True) | |
| dispute_id = Column(String(64), nullable=False, unique=True) | |
| publisher = Column(String, nullable=False) | |
| assignee = Column(String, nullable=False) | |
| respondent = Column(String, nullable=False) | |
| payload = Column(Text, nullable=False) | |
| projected = Column(Boolean, nullable=False, default=False, index=True) | |
| class Wallet(Base): | |
| """用户钱包表""" | |
| __tablename__ = "wallets" | |
| account = Column(String, primary_key=True, index=True) | |
| balance = Column(Integer, default=0, nullable=False) # 用于消费的余额 (充值获得) | |
| earn_balance = Column(Integer, default=0, nullable=False) # 创作者销售收益余额 (别人购买获得) | |
| tip_balance = Column(Integer, default=0, nullable=False) # 【新增】创作者打赏收益余额 (粉丝赞助获得) | |
| task_balance = Column(Integer, default=0, nullable=False) # 【新增】任务相关余额 | |
| frozen_balance = Column(Integer, default=0, nullable=False) # 提现审核冻结中的余额 | |
| # 预留版本列；未配置 version_id_col，不提供 ORM 乐观锁保护 | |
| version = Column(Integer, default=1) | |
| class Ownership(Base): | |
| """资源所有权表""" | |
| __tablename__ = "ownerships" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| account = Column(String, index=True) | |
| item_id = Column(String, index=True) | |
| purchased_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| # 🔄 P7后悔模式增强 | |
| price_paid = Column(Integer, default=0) # 购买时支付的价格 | |
| is_refunded = Column(Boolean, default=False) # 是否已退款 | |
| refunded_at = Column(DateTime, nullable=True) # 退款时间 | |
| # 🚀 P1性能优化：复合索引 | |
| __table_args__ = ( | |
| Index('ix_ownerships_account_item', 'account', 'item_id'), | |
| Index('ix_ownerships_account_refunded', 'account', 'is_refunded'), | |
| ) | |
| class Refund(Base): | |
| """🔄 P7后悔模式：退款记录表""" | |
| __tablename__ = "refunds" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| account = Column(String, index=True) # 退款用户 | |
| item_id = Column(String, index=True) # 退款商品 | |
| amount = Column(Integer) # 退款金额 | |
| refunded_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| ban_until = Column(DateTime) # 禁止购买截止时间（30天后） | |
| class RechargeOrder(Base): | |
| """💰 充值订单预存表（订单号→账号映射持久化，防止实例重启导致回调找不到订单）""" | |
| __tablename__ = "recharge_orders" | |
| order_id = Column(String, primary_key=True) # 商户订单号 PAY_xxx | |
| account = Column(String, index=True) # 充值用户 | |
| amount = Column(Integer) # 充值金额 | |
| status = Column(String, default="pending") # pending/paid | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| class Transaction(Base): | |
| """交易流水表""" | |
| __tablename__ = "transactions" | |
| tx_id = Column(String, primary_key=True) | |
| account = Column(String, index=True) | |
| tx_type = Column(String) # 枚举: RECHARGE, PURCHASE, SALE, TIP_OUT, TIP_IN, WITHDRAW, WITHDRAW_FEE, REFUND, SALE_REFUND, TASK_FREEZE, TASK_PRICE_ADJUST, TASK_DEPOSIT, TASK_PAYMENT, TASK_INCOME, TASK_REFUND, TASK_CANCEL_REFUND | |
| amount = Column(Integer) | |
| related_account = Column(String, nullable=True) | |
| item_id = Column(String, nullable=True) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow, index=True) # 🚀 P1: 添加时间索引 | |
| prev_hash = Column(String) # 上一笔订单的哈希 | |
| tx_hash = Column(String) # 本笔订单的哈希 (防篡改) | |
| # 💰 提现相关字段 (仅 WITHDRAW_APPLY 类型交易使用) | |
| alipay_account = Column(String, nullable=True) # 收款支付宝账号 | |
| real_name = Column(String, nullable=True) # 收款人姓名 | |
| withdraw_status = Column(String, nullable=True) # pending / completed | |
| payment_order_id = Column(String, nullable=True) # 管理员填写的打款订单号 | |
| net_amount = Column(Integer, nullable=True) # 实际到账金额（用于解冻时准确扣减） | |
| # 交易详情增强字段 | |
| description = Column(String, nullable=True) # 交易自描述文本 | |
| item_title = Column(String, nullable=True) # 资源/任务标题 | |
| item_type = Column(String, nullable=True) # 资源类型 (tool/skill/app/recommend/post/task/prompt) | |
| related_user_name = Column(String, nullable=True) # 对方用户名 | |
| # 🌐 开放API：来源插件标识（如 "nodecraft_ai"），用于按工具结构化追踪消费 | |
| source_plugin = Column(String(50), nullable=True, index=True) | |
| # 🚀 P1性能优化：复合索引 | |
| __table_args__ = ( | |
| Index('ix_transactions_account_type', 'account', 'tx_type'), | |
| Index('ix_transactions_account_created', 'account', 'created_at'), | |
| Index('ix_transactions_source_plugin_created', 'source_plugin', 'created_at'), | |
| ) | |
| class SystemConfig(Base): | |
| """系统配置表 - 持久化存储，替代 /tmp/system_config.json""" | |
| __tablename__ = "system_configs" | |
| config_key = Column(String, primary_key=True) | |
| config_value = Column(String, nullable=False, default="{}") | |
| updated_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| updated_by = Column(String, nullable=True) | |
| class RegisteredPlugin(Base): | |
| """🌐 开放API：第三方插件注册信息表""" | |
| __tablename__ = "registered_plugins" | |
| plugin_id = Column(String(50), primary_key=True) # 英文标识如 "nodecraft_ai" | |
| plugin_key = Column(String(100), unique=True, nullable=False, index=True) | |
| name = Column(String(100), nullable=False) # 显示名 | |
| description = Column(String(500), nullable=True) | |
| enabled = Column(Boolean, default=True, nullable=False) | |
| rate_limit = Column(Integer, default=100) # 每分钟最大请求数 | |
| max_deduct_per_tx = Column(Integer, default=100) # 单笔扣款上限 | |
| max_deduct_per_day = Column(Integer, default=1000) # 日扣款上限 | |
| permissions = Column(String(200), default='["verify","balance","deduct","refund"]') # JSON数组 | |
| created_at = Column(DateTime, default=func.now()) | |
| created_by = Column(String(50)) # 创建者管理员账号 | |
| updated_at = Column(DateTime, onupdate=func.now()) | |
| last_rotated_at = Column(DateTime, nullable=True) # 上次轮换key时间 | |
| class OpenApiRateLimitBucket(Base): | |
| """跨 worker 持久化的开放 API 插件分钟窗口计数。""" | |
| __tablename__ = "open_api_rate_limit_buckets" | |
| plugin_id = Column(String(50), primary_key=True) | |
| window_start = Column(Integer, nullable=False) | |
| request_count = Column(Integer, nullable=False, default=0) | |
| updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) | |
| class ApiRateLimitBucket(Base): | |
| """跨 worker 共享的常规 API 限流分钟窗口。""" | |
| __tablename__ = "api_rate_limit_buckets" | |
| bucket_key = Column(String(64), primary_key=True) | |
| window_start = Column(Integer, nullable=False) | |
| request_count = Column(Integer, nullable=False, default=0) | |
| updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) | |
| class VerificationContactCooldown(Base): | |
| """验证码联系方式发送冷却；只持久化脱敏哈希键，不保存原始邮箱/手机号。""" | |
| __tablename__ = "verification_contact_cooldowns" | |
| contact_key = Column(String(64), primary_key=True) | |
| last_sent_at = Column(Integer, nullable=False) | |
| claim_id = Column(String(32), nullable=False) | |
| class VerificationCode(Base): | |
| """跨 worker 一次性验证码；仅保存联系方式哈希和带密钥的验证码摘要。""" | |
| __tablename__ = "verification_codes" | |
| contact_key = Column(String(64), primary_key=True) | |
| code_digest = Column(String(64), nullable=False) | |
| expires_at = Column(Integer, nullable=False, index=True) | |
| updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) | |