WIP checkpoint: Wallet & ledger models + Alembic migration (wallets, transactions)

2026-08-18 16:34:12 +00:00 · 2026-08-18 16:01:28 +00:00
7 changed files with 78 additions and 436 deletions
@@ -8,7 +8,7 @@ from sqlalchemy.ext.asyncio import create_async_engine
 from app.config import settings
 from app.database import Base
-from app.models import user, session, world, asset, conversation, message  # noqa: F401
+from app.models import user, session, world, asset, wallet  # noqa: F401
 # this is the Alembic Config object, which provides
 # access to the values within the .ini file in use.
@@ -1,228 +0,0 @@
 """chat data models
 Revision ID: 1a1983be633a
 Revises: 0001_initial
 Create Date: 2026-08-18 15:06:05.948446
 """
 from typing import Sequence, Union
 from alembic import op
 import sqlalchemy as sa
 from sqlalchemy.dialects import postgresql
 # revision identifiers, used by Alembic.
 revision: str = "1a1983be633a"
 down_revision: Union[str, None] = "0001_initial"
 branch_labels: Union[str, Sequence[str], None] = None
 depends_on: Union[str, Sequence[str], None] = None
 def upgrade() -> None:
    """Create chat tables: conversations, conversation_participants,
    messages, message_reports."""
    # ── ENUM types ──────────────────────────────────────────────────
    msg_report_reason = postgresql.ENUM(
        "spam", "inappropriate", "off_topic", "other",
        name="message_report_reason",
    )
    msg_report_reason.create(op.get_bind(), checkfirst=True)
    msg_report_status = postgresql.ENUM(
        "pending", "resolved", "dismissed",
        name="message_report_status",
    )
    msg_report_status.create(op.get_bind(), checkfirst=True)
    # ── conversations ───────────────────────────────────────────────
    op.create_table(
        "conversations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "latest_message_id",
            sa.Integer(),
            sa.ForeignKey("messages.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            onupdate=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_conversations_id"), "conversations", ["id"], unique=False)
    # Unique pair constraint — populated later via participants
    op.create_unique_constraint(
        "uq_conversation_participants",
        "conversations",
        ["participant_1_id", "participant_2_id"],
    )
    # ── conversation_participants ───────────────────────────────────
    op.create_table(
        "conversation_participants",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "conversation_id",
            sa.Integer(),
            sa.ForeignKey("conversations.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "user_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("left_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_read_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(
        op.f("ix_conversation_participants_id"),
        "conversation_participants",
        ["id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_conversation_participants_user_id"),
        "conversation_participants",
        ["user_id"],
        unique=False,
    )
    op.create_unique_constraint(
        "uq_conversation_user",
        "conversation_participants",
        ["conversation_id", "user_id"],
    )
    # ── messages ────────────────────────────────────────────────────
    op.create_table(
        "messages",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "sender_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "conversation_id",
            sa.Integer(),
            sa.ForeignKey("conversations.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column(
            "world_id",
            sa.Integer(),
            sa.ForeignKey("worlds.id", ondelete="CASCADE"),
            nullable=True,
        ),
        sa.Column("room_id", sa.String(length=36), nullable=True),
        sa.Column("body", sa.Text(), nullable=False),
        sa.Column("is_filtered", sa.Boolean(), nullable=False, server_default="false"),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_messages_id"), "messages", ["id"], unique=False)
    op.create_index(
        "ix_messages_conversation_created",
        "messages",
        ["conversation_id", "created_at"],
    )
    op.create_index(
        "ix_messages_world_created",
        "messages",
        ["world_id", "created_at"],
    )
    # ── message_reports ─────────────────────────────────────────────
    op.create_table(
        "message_reports",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "message_id",
            sa.Integer(),
            sa.ForeignKey("messages.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "reporter_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=False,
        ),
        sa.Column(
            "reason",
            postgresql.ENUM(
                "spam", "inappropriate", "off_topic", "other",
                name="message_report_reason",
                create_type=False,
            ),
            nullable=False,
        ),
        sa.Column(
            "status",
            postgresql.ENUM(
                "pending", "resolved", "dismissed",
                name="message_report_status",
                create_type=False,
            ),
            nullable=False,
            server_default="pending",
        ),
        sa.Column(
            "moderator_id",
            sa.Integer(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
    )
    op.create_index(op.f("ix_message_reports_id"), "message_reports", ["id"], unique=False)
 def downgrade() -> None:
    """Drop chat tables in reverse dependency order."""
    op.drop_index(op.f("ix_message_reports_id"), table_name="message_reports")
    op.drop_table("message_reports")
    op.drop_index(op.f("ix_messages_world_created"), table_name="messages")
    op.drop_index(op.f("ix_messages_conversation_created"), table_name="messages")
    op.drop_index(op.f("ix_messages_id"), table_name="messages")
    op.drop_table("messages")
    op.drop_index(op.f("ix_conversation_participants_user_id"), table_name="conversation_participants")
    op.drop_index(op.f("ix_conversation_participants_id"), table_name="conversation_participants")
    op.drop_table("conversation_participants")
    op.drop_index(op.f("ix_conversations_id"), table_name="conversations")
    op.drop_table("conversations")
    # Drop ENUM types
    postgresql.ENUM(name="message_report_status").drop(op.get_bind(), checkfirst=True)
    postgresql.ENUM(name="message_report_reason").drop(op.get_bind(), checkfirst=True)
@@ -2,16 +2,14 @@ from app.models.user import User
 from app.models.session import Session
 from app.models.world import World
 from app.models.asset import Asset
-from app.models.conversation import Conversation, ConversationParticipant
+from app.models.wallet import Transaction, TransactionType, Wallet
 from app.models.message import Message, MessageReport
 __all__ = [
    "User",
    "Session",
    "World",
    "Asset",
-    "Conversation",
+    "Wallet",
-    "ConversationParticipant",
+    "Transaction",
-    "Message",
+    "TransactionType",
    "MessageReport",
 ]
@@ -1,94 +0,0 @@
 """Conversation and participant models for DM chat."""
 from sqlalchemy import (
    Column,
    Integer,
    String,
    DateTime,
    ForeignKey,
    UniqueConstraint,
    Index,
 )
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 from app.database import Base
 class Conversation(Base):
    """A DM conversation between two users.
    Each conversation is uniquely identified by the pair of participant
    user IDs (regardless of order).  A conversation is soft-deletable
    via ``left_at`` on the participant row.
    """
    __tablename__ = "conversations"
    id = Column(Integer, primary_key=True, index=True)
    # The most recent message's ID in this conversation (denormalised
    # for fast listing; updated by triggers or app logic).
    latest_message_id = Column(
        Integer, ForeignKey("messages.id", ondelete="SET NULL"), nullable=True
    )
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    # Relationships
    latest_message = relationship(
        "Message",
        foreign_keys=[latest_message_id],
        lazy="select",
    )
    participants = relationship(
        "ConversationParticipant",
        back_populates="conversation",
        cascade="all, delete-orphan",
        order_by="ConversationParticipant.id",
    )
    __table_args__ = (
        # Ensure exactly one conversation per unique pair of users
        UniqueConstraint("participant_1_id", "participant_2_id", name="uq_conversation_participants"),
    )
    def __repr__(self) -> str:
        return f"<Conversation id={self.id}>"
 class ConversationParticipant(Base):
    """Link table between conversations and users.
    Stores per-participant metadata such as when they left the
    conversation (for soft-delete / archival).
    """
    __tablename__ = "conversation_participants"
    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(
        Integer,
        ForeignKey("conversations.id", ondelete="CASCADE"),
        nullable=False,
    )
    user_id = Column(
        Integer,
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
    )
    # When the participant left (NULL = still active).
    left_at = Column(DateTime(timezone=True), nullable=True)
    # Timestamp of the last message read by this participant.
    last_read_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    conversation = relationship("Conversation", back_populates="participants")
    user = relationship("User")
    __table_args__ = (
        UniqueConstraint("conversation_id", "user_id", name="uq_conversation_user"),
        Index("ix_conversation_participants_user_id", "user_id"),
    )
    def __repr__(self) -> str:
        return f"<ConversationParticipant conversation_id={self.conversation_id} user_id={self.user_id}>"
@@ -1,96 +0,0 @@
 """Message models — messages, message reports."""
 from sqlalchemy import (
    Column,
    Integer,
    String,
    Text,
    DateTime,
    ForeignKey,
    Boolean,
    Index,
 )
 from sqlalchemy.dialects.postgresql import ENUM
 from sqlalchemy.orm import relationship
 from sqlalchemy.sql import func
 import enum
 from app.database import Base
 class MessageReportStatus(str, enum.Enum):
    PENDING = "pending"
    RESOLVED = "resolved"
    DISMISSED = "dismissed"
 class MessageReportReason(str, enum.Enum):
    SPAM = "spam"
    INAPPROPRIATE = "inappropriate"
    OFF_TOPIC = "off_topic"
    OTHER = "other"
 class Message(Base):
    __tablename__ = "messages"
    id = Column(Integer, primary_key=True, index=True)
    sender_id = Column(
        Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False
    )
    conversation_id = Column(
        Integer, ForeignKey("conversations.id", ondelete="CASCADE"), nullable=True
    )
    world_id = Column(
        Integer, ForeignKey("worlds.id", ondelete="CASCADE"), nullable=True
    )
    room_id = Column(String(36), nullable=True)
    body = Column(Text, nullable=False)
    is_filtered = Column(Boolean, default=False, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    # Relationships
    sender = relationship("User", back_populates="sent_messages")
    conversation = relationship("Conversation", back_populates="messages")
    world = relationship("World")
    reports = relationship(
        "MessageReport", back_populates="message", cascade="all, delete-orphan"
    )
    # Indexes for history queries
    __table_args__ = (
        Index("ix_messages_conversation_created", "conversation_id", "created_at"),
        Index("ix_messages_world_created", "world_id", "created_at"),
    )
 class MessageReport(Base):
    __tablename__ = "message_reports"
    id = Column(Integer, primary_key=True, index=True)
    message_id = Column(
        Integer, ForeignKey("messages.id", ondelete="CASCADE"), nullable=False
    )
    reporter_id = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=False
    )
    reason = Column(
        ENUM(MessageReportReason, name="message_report_reason", create_type=False),
        nullable=False,
    )
    details = Column(Text, nullable=True)
    status = Column(
        ENUM(MessageReportStatus, name="message_report_status", create_type=False),
        default=MessageReportStatus.PENDING,
        nullable=False,
    )
    moderator_id = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    # Relationships
    message = relationship("Message", back_populates="reports")
    reporter = relationship("User", foreign_keys=[reporter_id])
    moderator = relationship("User", foreign_keys=[moderator_id])
@@ -19,14 +19,3 @@ class User(Base):
    sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan")
    worlds = relationship("World", back_populates="owner")
    assets = relationship("Asset", back_populates="owner")
    sent_messages = relationship("Message", back_populates="sender")
    reported_messages = relationship(
        "MessageReport",
        foreign_keys="MessageReport.reporter_id",
        back_populates="reporter",
    )
    moderated_reports = relationship(
        "MessageReport",
        foreign_keys="MessageReport.moderator_id",
        back_populates="moderator",
    )
@@ -0,0 +1,73 @@
 """Wallet and transaction ledger models."""
 import enum
 from sqlalchemy import (
    BigInteger,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    Text,
 )
 from sqlalchemy.dialects.postgresql import ENUM, JSONB
 from sqlalchemy.sql import func
 from app.database import Base
 class TransactionType(str, enum.Enum):
    CREDIT_PURCHASE = "credit_purchase"
    MARKETPLACE_PURCHASE = "marketplace_purchase"
    MARKETPLACE_SALE = "marketplace_sale"
    ESCROW_RELEASE = "escrow_release"
    PAYOUT = "payout"
    REFUND = "refund"
    ADMIN_ADJUSTMENT = "admin_adjustment"
 class Wallet(Base):
    __tablename__ = "wallets"
    __table_args__ = (
        CheckConstraint("balance >= 0", name="ck_wallets_balance_nonnegative"),
    )
    id = Column(BigInteger, primary_key=True, index=True)
    user_id = Column(
        Integer,
        ForeignKey("users.id", ondelete="CASCADE"),
        unique=True,
        index=True,
        nullable=False,
    )
    balance = Column(BigInteger, nullable=False, default=0, server_default="0")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )
 class Transaction(Base):
    __tablename__ = "transactions"
    id = Column(BigInteger, primary_key=True, index=True)
    wallet_id = Column(
        BigInteger, ForeignKey("wallets.id"), nullable=False
    )
    type = Column(
        ENUM(
            TransactionType,
            name="transaction_type",
            create_type=False,
            values_callable=lambda enum_cls: [member.value for member in enum_cls],
        ),
        nullable=False,
    )
    amount = Column(BigInteger, nullable=False)
    balance_after = Column(BigInteger, nullable=False)
    reference_type = Column(Text, nullable=True)
    reference_id = Column(Text, nullable=True)
    idempotency_key = Column(Text, unique=True, index=True, nullable=True)
    metadata_json = Column(JSONB, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
Author	SHA1	Message	Date
architect-bot	34fe164347	WIP checkpoint: Wallet & ledger models + Alembic migration (wallets, transactions)	2026-08-18 16:34:12 +00:00
architect-bot	cef6968766	WIP checkpoint: Wallet & ledger models + Alembic migration (wallets, transactions)	2026-08-18 16:01:28 +00:00