forked from jamesp/sasa-membership
nathanb
d024bf7fa3
- ui has been made 'kinda better' (after making it worse for a while lol - ESP rfid readers are now supported [ill upload the code for them in another repo later] - admin system has been secured a bit better and seems to be working well
504 lines
22 KiB
Python
504 lines
22 KiB
Python
from sqlalchemy import (
|
|
Column, Integer, String, DateTime, Boolean, Enum as SQLEnum,
|
|
Float, Text, ForeignKey, Date, UniqueConstraint
|
|
)
|
|
from sqlalchemy.orm import relationship
|
|
import enum
|
|
from ..core.database import Base
|
|
from ..core.datetime import utc_now
|
|
|
|
|
|
class UserRole(str, enum.Enum):
|
|
MEMBER = "member"
|
|
ADMIN = "admin"
|
|
SUPER_ADMIN = "super_admin"
|
|
|
|
|
|
class MembershipStatus(str, enum.Enum):
|
|
ACTIVE = "active"
|
|
EXPIRED = "expired"
|
|
PENDING = "pending"
|
|
CANCELLED = "cancelled"
|
|
|
|
|
|
class PaymentStatus(str, enum.Enum):
|
|
PENDING = "pending"
|
|
COMPLETED = "completed"
|
|
FAILED = "failed"
|
|
REFUNDED = "refunded"
|
|
|
|
|
|
class PaymentMethod(str, enum.Enum):
|
|
SQUARE = "square"
|
|
CASH = "cash"
|
|
CHECK = "check"
|
|
DUMMY = "dummy"
|
|
|
|
|
|
class EventStatus(str, enum.Enum):
|
|
DRAFT = "draft"
|
|
PUBLISHED = "published"
|
|
CANCELLED = "cancelled"
|
|
COMPLETED = "completed"
|
|
|
|
|
|
class RSVPStatus(str, enum.Enum):
|
|
PENDING = "pending"
|
|
ATTENDING = "attending"
|
|
NOT_ATTENDING = "not_attending"
|
|
MAYBE = "maybe"
|
|
|
|
|
|
class EspReaderType(str, enum.Enum):
|
|
CHECKIN_CHECKOUT = "checkin_checkout"
|
|
|
|
|
|
class EspReaderProvisioningStatus(str, enum.Enum):
|
|
PENDING = "pending"
|
|
APPROVED = "approved"
|
|
PROVISIONED = "provisioned"
|
|
REJECTED = "rejected"
|
|
|
|
|
|
class EspTapAction(str, enum.Enum):
|
|
CHECK_IN = "check_in"
|
|
CHECK_OUT = "check_out"
|
|
DENIED = "denied"
|
|
UNKNOWN = "unknown"
|
|
|
|
|
|
class AttendanceCheckoutSource(str, enum.Enum):
|
|
USER = "user"
|
|
SYSTEM = "system"
|
|
|
|
|
|
class RfidWriteJobStatus(str, enum.Enum):
|
|
PENDING = "pending"
|
|
CLAIMED = "claimed"
|
|
COMPLETED = "completed"
|
|
FAILED = "failed"
|
|
CANCELLED = "cancelled"
|
|
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
email = Column(String(255), unique=True, index=True, nullable=False)
|
|
hashed_password = Column(String(255), nullable=False)
|
|
first_name = Column(String(100), nullable=False)
|
|
last_name = Column(String(100), nullable=False)
|
|
phone = Column(String(20), nullable=True)
|
|
address = Column(Text, nullable=True)
|
|
role = Column(SQLEnum(UserRole, values_callable=lambda x: [e.value for e in x]), default=UserRole.MEMBER, nullable=False)
|
|
volunteer_level = Column(String(50), nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
last_login = Column(DateTime, nullable=True)
|
|
|
|
# Relationships
|
|
memberships = relationship("Membership", back_populates="user", cascade="all, delete-orphan")
|
|
payments = relationship("Payment", back_populates="user", cascade="all, delete-orphan")
|
|
event_rsvps = relationship("EventRSVP", back_populates="user", cascade="all, delete-orphan")
|
|
volunteer_assignments = relationship("VolunteerAssignment", back_populates="user", cascade="all, delete-orphan")
|
|
certificates = relationship("Certificate", back_populates="user", cascade="all, delete-orphan")
|
|
profile_answers = relationship(
|
|
"UserProfileAnswer",
|
|
back_populates="user",
|
|
cascade="all, delete-orphan",
|
|
foreign_keys="UserProfileAnswer.user_id"
|
|
)
|
|
rfid_cards = relationship("RfidCard", back_populates="user")
|
|
attendance_sessions = relationship("AttendanceSession", back_populates="user")
|
|
|
|
|
|
class ProfileQuestion(Base):
|
|
__tablename__ = "profile_questions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
key = Column(String(100), unique=True, nullable=False, index=True)
|
|
label = Column(String(255), nullable=False)
|
|
help_text = Column(Text, nullable=True)
|
|
input_type = Column(String(30), nullable=False) # text, number, boolean, date, select
|
|
placeholder = Column(String(255), nullable=True)
|
|
options_json = Column(Text, nullable=True) # JSON array: [{"label":"Yes","value":"true"}]
|
|
is_required = Column(Boolean, default=False, nullable=False)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
admin_only_edit = Column(Boolean, default=False, nullable=False)
|
|
display_order = Column(Integer, default=0, nullable=False)
|
|
depends_on_question_id = Column(Integer, ForeignKey("profile_questions.id"), nullable=True)
|
|
depends_on_value = Column(String(255), nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
depends_on_question = relationship("ProfileQuestion", remote_side=[id], backref="dependent_questions")
|
|
answers = relationship("UserProfileAnswer", back_populates="question", cascade="all, delete-orphan")
|
|
|
|
|
|
class UserProfileAnswer(Base):
|
|
__tablename__ = "user_profile_answers"
|
|
__table_args__ = (
|
|
UniqueConstraint("user_id", "question_id", name="uq_user_profile_answer"),
|
|
)
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
|
|
question_id = Column(Integer, ForeignKey("profile_questions.id"), nullable=False, index=True)
|
|
value_text = Column(Text, nullable=True)
|
|
updated_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
user = relationship("User", foreign_keys=[user_id], back_populates="profile_answers")
|
|
question = relationship("ProfileQuestion", back_populates="answers")
|
|
updated_by_user = relationship("User", foreign_keys=[updated_by_user_id])
|
|
|
|
|
|
class MembershipTier(Base):
|
|
__tablename__ = "membership_tiers"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String(100), unique=True, nullable=False)
|
|
description = Column(Text, nullable=True)
|
|
annual_fee = Column(Float, nullable=False)
|
|
benefits = Column(Text, nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
memberships = relationship("Membership", back_populates="tier")
|
|
|
|
|
|
class Membership(Base):
|
|
__tablename__ = "memberships"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
tier_id = Column(Integer, ForeignKey("membership_tiers.id"), nullable=False)
|
|
status = Column(SQLEnum(MembershipStatus, values_callable=lambda x: [e.value for e in x]), default=MembershipStatus.PENDING, nullable=False)
|
|
start_date = Column(Date, nullable=False)
|
|
end_date = Column(Date, nullable=False)
|
|
auto_renew = Column(Boolean, default=False, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
user = relationship("User", back_populates="memberships")
|
|
tier = relationship("MembershipTier", back_populates="memberships")
|
|
payments = relationship("Payment", back_populates="membership")
|
|
|
|
|
|
class Payment(Base):
|
|
__tablename__ = "payments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
membership_id = Column(Integer, ForeignKey("memberships.id"), nullable=True)
|
|
amount = Column(Float, nullable=False)
|
|
payment_method = Column(SQLEnum(PaymentMethod, values_callable=lambda x: [e.value for e in x]), nullable=False)
|
|
status = Column(SQLEnum(PaymentStatus, values_callable=lambda x: [e.value for e in x]), default=PaymentStatus.PENDING, nullable=False)
|
|
transaction_id = Column(String(255), nullable=True)
|
|
payment_date = Column(DateTime, nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
user = relationship("User", back_populates="payments")
|
|
membership = relationship("Membership", back_populates="payments")
|
|
|
|
|
|
class Event(Base):
|
|
__tablename__ = "events"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
title = Column(String(255), nullable=False)
|
|
description = Column(Text, nullable=True)
|
|
event_date = Column(DateTime, nullable=False)
|
|
event_time = Column(String(10), nullable=True) # HH:MM format
|
|
location = Column(String(255), nullable=True)
|
|
max_attendees = Column(Integer, nullable=True)
|
|
status = Column(SQLEnum(EventStatus, values_callable=lambda x: [e.value for e in x]), default=EventStatus.DRAFT, nullable=False)
|
|
created_by = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
rsvps = relationship("EventRSVP", back_populates="event", cascade="all, delete-orphan")
|
|
|
|
|
|
class EventRSVP(Base):
|
|
__tablename__ = "event_rsvps"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
event_id = Column(Integer, ForeignKey("events.id"), nullable=False)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
status = Column(SQLEnum(RSVPStatus, values_callable=lambda x: [e.value for e in x]), default=RSVPStatus.PENDING, nullable=False)
|
|
attended = Column(Boolean, default=False, nullable=False)
|
|
notes = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
event = relationship("Event", back_populates="rsvps")
|
|
user = relationship("User", back_populates="event_rsvps")
|
|
|
|
|
|
class EspReader(Base):
|
|
__tablename__ = "esp_readers"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
device_id = Column(String(100), unique=True, index=True, nullable=False)
|
|
name = Column(String(255), nullable=False)
|
|
location = Column(String(255), nullable=True)
|
|
reader_type = Column(SQLEnum(EspReaderType, values_callable=lambda x: [e.value for e in x]), default=EspReaderType.CHECKIN_CHECKOUT, nullable=False)
|
|
provisioning_status = Column(SQLEnum(EspReaderProvisioningStatus, values_callable=lambda x: [e.value for e in x]), default=EspReaderProvisioningStatus.PENDING, nullable=False)
|
|
api_key_hash = Column(String(255), nullable=True)
|
|
pending_api_key = Column(String(255), nullable=True)
|
|
registration_token_hash = Column(String(255), nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
can_write_cards = Column(Boolean, default=False, nullable=False)
|
|
firmware_version = Column(String(100), nullable=True)
|
|
last_seen_at = Column(DateTime, nullable=True)
|
|
approved_at = Column(DateTime, nullable=True)
|
|
provisioned_at = Column(DateTime, nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
taps = relationship("RfidTap", back_populates="reader")
|
|
attendance_sessions = relationship("AttendanceSession", back_populates="reader")
|
|
write_jobs = relationship("RfidCardWriteJob", back_populates="reader")
|
|
|
|
|
|
class RfidCard(Base):
|
|
__tablename__ = "rfid_cards"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
uid = Column(String(100), unique=True, index=True, nullable=False)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=True, index=True)
|
|
label = Column(String(255), nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
user = relationship("User", back_populates="rfid_cards")
|
|
taps = relationship("RfidTap", back_populates="card")
|
|
|
|
|
|
class RfidCardWriteJob(Base):
|
|
__tablename__ = "rfid_card_write_jobs"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
reader_id = Column(Integer, ForeignKey("esp_readers.id"), nullable=False, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
|
|
card_id = Column(Integer, ForeignKey("rfid_cards.id"), nullable=True, index=True)
|
|
label = Column(String(255), nullable=False)
|
|
status = Column(SQLEnum(RfidWriteJobStatus, values_callable=lambda x: [e.value for e in x]), default=RfidWriteJobStatus.PENDING, nullable=False, index=True)
|
|
requested_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
card_uid = Column(String(100), nullable=True, index=True)
|
|
write_payload = Column(Text, nullable=True)
|
|
claimed_at = Column(DateTime, nullable=True)
|
|
completed_at = Column(DateTime, nullable=True)
|
|
error_message = Column(String(500), nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
reader = relationship("EspReader", back_populates="write_jobs")
|
|
user = relationship("User", foreign_keys=[user_id])
|
|
requested_by_user = relationship("User", foreign_keys=[requested_by_user_id])
|
|
card = relationship("RfidCard")
|
|
|
|
|
|
class RfidTap(Base):
|
|
__tablename__ = "rfid_taps"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
reader_id = Column(Integer, ForeignKey("esp_readers.id"), nullable=False, index=True)
|
|
card_id = Column(Integer, ForeignKey("rfid_cards.id"), nullable=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=True, index=True)
|
|
card_uid = Column(String(100), nullable=False, index=True)
|
|
action = Column(SQLEnum(EspTapAction, values_callable=lambda x: [e.value for e in x]), default=EspTapAction.UNKNOWN, nullable=False)
|
|
accepted = Column(Boolean, default=False, nullable=False)
|
|
message = Column(String(255), nullable=True)
|
|
raw_payload = Column(Text, nullable=True)
|
|
tapped_at = Column(DateTime, default=utc_now, nullable=False, index=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
|
|
reader = relationship("EspReader", back_populates="taps")
|
|
card = relationship("RfidCard", back_populates="taps")
|
|
user = relationship("User")
|
|
|
|
|
|
class AttendanceSession(Base):
|
|
__tablename__ = "attendance_sessions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
|
|
reader_id = Column(Integer, ForeignKey("esp_readers.id"), nullable=False, index=True)
|
|
check_in_tap_id = Column(Integer, ForeignKey("rfid_taps.id"), nullable=False)
|
|
check_out_tap_id = Column(Integer, ForeignKey("rfid_taps.id"), nullable=True)
|
|
checked_in_at = Column(DateTime, nullable=False, index=True)
|
|
checked_out_at = Column(DateTime, nullable=True, index=True)
|
|
checkout_source = Column(SQLEnum(AttendanceCheckoutSource, values_callable=lambda x: [e.value for e in x]), nullable=True)
|
|
system_flag_reason = Column(String(255), nullable=True)
|
|
duration_seconds = Column(Integer, nullable=True)
|
|
is_open = Column(Boolean, default=True, nullable=False, index=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
user = relationship("User", back_populates="attendance_sessions")
|
|
reader = relationship("EspReader", back_populates="attendance_sessions")
|
|
check_in_tap = relationship("RfidTap", foreign_keys=[check_in_tap_id])
|
|
check_out_tap = relationship("RfidTap", foreign_keys=[check_out_tap_id])
|
|
|
|
|
|
class VolunteerRole(Base):
|
|
__tablename__ = "volunteer_roles"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
name = Column(String(100), nullable=False)
|
|
description = Column(Text, nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
assignments = relationship("VolunteerAssignment", back_populates="role", cascade="all, delete-orphan")
|
|
|
|
|
|
class VolunteerAssignment(Base):
|
|
__tablename__ = "volunteer_assignments"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
role_id = Column(Integer, ForeignKey("volunteer_roles.id"), nullable=False)
|
|
assigned_date = Column(Date, nullable=False)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
notes = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
user = relationship("User", back_populates="volunteer_assignments")
|
|
role = relationship("VolunteerRole", back_populates="assignments")
|
|
schedules = relationship("VolunteerSchedule", back_populates="assignment", cascade="all, delete-orphan")
|
|
|
|
|
|
class VolunteerSchedule(Base):
|
|
__tablename__ = "volunteer_schedules"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
assignment_id = Column(Integer, ForeignKey("volunteer_assignments.id"), nullable=False)
|
|
schedule_date = Column(Date, nullable=False)
|
|
start_time = Column(DateTime, nullable=False)
|
|
end_time = Column(DateTime, nullable=False)
|
|
location = Column(String(255), nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
completed = Column(Boolean, default=False, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
assignment = relationship("VolunteerAssignment", back_populates="schedules")
|
|
|
|
|
|
class Certificate(Base):
|
|
__tablename__ = "certificates"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
certificate_name = Column(String(255), nullable=False)
|
|
issuing_organization = Column(String(255), nullable=True)
|
|
issue_date = Column(Date, nullable=False)
|
|
expiry_date = Column(Date, nullable=True)
|
|
certificate_number = Column(String(100), nullable=True)
|
|
file_path = Column(String(500), nullable=True)
|
|
notes = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
user = relationship("User", back_populates="certificates")
|
|
|
|
|
|
class File(Base):
|
|
__tablename__ = "files"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
filename = Column(String(255), nullable=False)
|
|
original_filename = Column(String(255), nullable=False)
|
|
file_path = Column(String(500), nullable=False)
|
|
file_size = Column(Integer, nullable=False)
|
|
mime_type = Column(String(100), nullable=False)
|
|
min_tier_id = Column(Integer, ForeignKey("membership_tiers.id"), nullable=True)
|
|
description = Column(Text, nullable=True)
|
|
uploaded_by = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
|
|
class Notification(Base):
|
|
__tablename__ = "notifications"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
subject = Column(String(255), nullable=False)
|
|
message = Column(Text, nullable=False)
|
|
email_sent = Column(Boolean, default=False, nullable=False)
|
|
sent_at = Column(DateTime, nullable=True)
|
|
error_message = Column(Text, nullable=True)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
|
|
|
|
class PasswordResetToken(Base):
|
|
__tablename__ = "password_reset_tokens"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
|
|
token = Column(String(255), unique=True, nullable=False, index=True)
|
|
expires_at = Column(DateTime, nullable=False)
|
|
used = Column(Boolean, default=False, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
|
|
# Relationships
|
|
user = relationship("User", backref="password_reset_tokens")
|
|
|
|
|
|
class EmailTemplate(Base):
|
|
__tablename__ = "email_templates"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
template_key = Column(String(100), unique=True, nullable=False, index=True)
|
|
name = Column(String(255), nullable=False)
|
|
subject = Column(String(255), nullable=False)
|
|
html_body = Column(Text, nullable=False)
|
|
text_body = Column(Text, nullable=True)
|
|
variables = Column(Text, nullable=True) # JSON string of available variables
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|
|
|
|
|
|
class BounceType(str, enum.Enum):
|
|
HARD = "hard"
|
|
SOFT = "soft"
|
|
COMPLAINT = "complaint"
|
|
UNSUBSCRIBE = "unsubscribe"
|
|
|
|
|
|
class EmailBounce(Base):
|
|
__tablename__ = "email_bounces"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
email = Column(String(255), nullable=False, index=True)
|
|
bounce_type = Column(SQLEnum(BounceType, values_callable=lambda x: [e.value for e in x]), nullable=False)
|
|
bounce_reason = Column(String(500), nullable=True)
|
|
smtp2go_message_id = Column(String(255), nullable=True, index=True)
|
|
bounce_date = Column(DateTime, nullable=False)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime, default=utc_now, nullable=False)
|
|
updated_at = Column(DateTime, default=utc_now, onupdate=utc_now, nullable=False)
|