239 lines
13 KiB
Python
239 lines
13 KiB
Python
"""initial migration
|
|
|
|
Revision ID: 001
|
|
Revises:
|
|
Create Date: 2026-01-13 00:00:00.000000
|
|
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '001'
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# Create users table
|
|
op.create_table('users',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('email', sa.String(), nullable=False),
|
|
sa.Column('password_hash', sa.String(), nullable=False),
|
|
sa.Column('role', sa.Enum('ADMIN', 'USER', name='userrole'), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
|
|
op.create_index(op.f('ix_users_id'), 'users', ['id'], unique=False)
|
|
|
|
# Create contacts table
|
|
op.create_table('contacts',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('phone_e164', sa.String(), nullable=False),
|
|
sa.Column('first_name', sa.String(), nullable=True),
|
|
sa.Column('last_name', sa.String(), nullable=True),
|
|
sa.Column('email', sa.String(), nullable=True),
|
|
sa.Column('opted_in', sa.Boolean(), nullable=False),
|
|
sa.Column('conversation_window_open', sa.Boolean(), nullable=False),
|
|
sa.Column('source', sa.String(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('user_id', 'phone_e164', name='uq_user_phone')
|
|
)
|
|
op.create_index('ix_contacts_user_phone', 'contacts', ['user_id', 'phone_e164'], unique=False)
|
|
op.create_index(op.f('ix_contacts_id'), 'contacts', ['id'], unique=False)
|
|
op.create_index(op.f('ix_contacts_user_id'), 'contacts', ['user_id'], unique=False)
|
|
|
|
# Create contact_tags table
|
|
op.create_table('contact_tags',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_contact_tags_id'), 'contact_tags', ['id'], unique=False)
|
|
op.create_index(op.f('ix_contact_tags_user_id'), 'contact_tags', ['user_id'], unique=False)
|
|
|
|
# Create contact_tag_map table
|
|
op.create_table('contact_tag_map',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('contact_id', sa.Integer(), nullable=False),
|
|
sa.Column('tag_id', sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['contacts.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['tag_id'], ['contact_tags.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('contact_id', 'tag_id', name='uq_contact_tag')
|
|
)
|
|
op.create_index(op.f('ix_contact_tag_map_id'), 'contact_tag_map', ['id'], unique=False)
|
|
|
|
# Create dnd_list table
|
|
op.create_table('dnd_list',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('phone_e164', sa.String(), nullable=False),
|
|
sa.Column('reason', sa.Text(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('user_id', 'phone_e164', name='uq_dnd_user_phone')
|
|
)
|
|
op.create_index(op.f('ix_dnd_list_id'), 'dnd_list', ['id'], unique=False)
|
|
op.create_index(op.f('ix_dnd_list_phone_e164'), 'dnd_list', ['phone_e164'], unique=False)
|
|
op.create_index(op.f('ix_dnd_list_user_id'), 'dnd_list', ['user_id'], unique=False)
|
|
|
|
# Create lists table
|
|
op.create_table('lists',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_lists_id'), 'lists', ['id'], unique=False)
|
|
op.create_index(op.f('ix_lists_user_id'), 'lists', ['user_id'], unique=False)
|
|
|
|
# Create list_members table
|
|
op.create_table('list_members',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('list_id', sa.Integer(), nullable=False),
|
|
sa.Column('contact_id', sa.Integer(), nullable=False),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['contacts.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['list_id'], ['lists.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('list_id', 'contact_id', name='uq_list_contact')
|
|
)
|
|
op.create_index(op.f('ix_list_members_contact_id'), 'list_members', ['contact_id'], unique=False)
|
|
op.create_index(op.f('ix_list_members_id'), 'list_members', ['id'], unique=False)
|
|
op.create_index(op.f('ix_list_members_list_id'), 'list_members', ['list_id'], unique=False)
|
|
|
|
# Create templates table
|
|
op.create_table('templates',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('language', sa.String(), nullable=False),
|
|
sa.Column('body_text', sa.Text(), nullable=False),
|
|
sa.Column('is_whatsapp_template', sa.Boolean(), nullable=False),
|
|
sa.Column('provider_template_name', sa.String(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_templates_id'), 'templates', ['id'], unique=False)
|
|
op.create_index(op.f('ix_templates_user_id'), 'templates', ['user_id'], unique=False)
|
|
|
|
# Create campaigns table
|
|
op.create_table('campaigns',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('name', sa.String(), nullable=False),
|
|
sa.Column('template_id', sa.Integer(), nullable=True),
|
|
sa.Column('list_id', sa.Integer(), nullable=True),
|
|
sa.Column('status', sa.Enum('DRAFT', 'SCHEDULED', 'SENDING', 'DONE', 'FAILED', name='campaignstatus'), nullable=False),
|
|
sa.Column('scheduled_at', sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['list_id'], ['lists.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['template_id'], ['templates.id'], ondelete='SET NULL'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_campaigns_id'), 'campaigns', ['id'], unique=False)
|
|
op.create_index(op.f('ix_campaigns_status'), 'campaigns', ['status'], unique=False)
|
|
op.create_index(op.f('ix_campaigns_user_id'), 'campaigns', ['user_id'], unique=False)
|
|
|
|
# Create campaign_recipients table
|
|
op.create_table('campaign_recipients',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('campaign_id', sa.Integer(), nullable=False),
|
|
sa.Column('contact_id', sa.Integer(), nullable=False),
|
|
sa.Column('status', sa.Enum('PENDING', 'SENT', 'DELIVERED', 'READ', 'FAILED', name='recipientstatus'), nullable=False),
|
|
sa.Column('provider_message_id', sa.String(), nullable=True),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['contacts.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_campaign_recipients_campaign_status', 'campaign_recipients', ['campaign_id', 'status'], unique=False)
|
|
op.create_index(op.f('ix_campaign_recipients_campaign_id'), 'campaign_recipients', ['campaign_id'], unique=False)
|
|
op.create_index(op.f('ix_campaign_recipients_contact_id'), 'campaign_recipients', ['contact_id'], unique=False)
|
|
op.create_index(op.f('ix_campaign_recipients_id'), 'campaign_recipients', ['id'], unique=False)
|
|
|
|
# Create send_logs table
|
|
op.create_table('send_logs',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('campaign_id', sa.Integer(), nullable=True),
|
|
sa.Column('contact_id', sa.Integer(), nullable=True),
|
|
sa.Column('provider', sa.String(), nullable=False),
|
|
sa.Column('request_payload_json', postgresql.JSON(astext_type=sa.Text()), nullable=True),
|
|
sa.Column('response_payload_json', postgresql.JSON(astext_type=sa.Text()), nullable=True),
|
|
sa.Column('status', sa.String(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['contact_id'], ['contacts.id'], ondelete='CASCADE'),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_send_logs_campaign_id'), 'send_logs', ['campaign_id'], unique=False)
|
|
op.create_index(op.f('ix_send_logs_created_at'), 'send_logs', ['created_at'], unique=False)
|
|
op.create_index(op.f('ix_send_logs_id'), 'send_logs', ['id'], unique=False)
|
|
op.create_index(op.f('ix_send_logs_user_id'), 'send_logs', ['user_id'], unique=False)
|
|
|
|
# Create jobs table
|
|
op.create_table('jobs',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('type', sa.String(), nullable=False),
|
|
sa.Column('payload_json', postgresql.JSON(astext_type=sa.Text()), nullable=False),
|
|
sa.Column('status', sa.String(), nullable=False),
|
|
sa.Column('attempts', sa.Integer(), nullable=False),
|
|
sa.Column('run_after', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
|
|
sa.Column('last_error', sa.Text(), nullable=True),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index('ix_jobs_status_run_after', 'jobs', ['status', 'run_after'], unique=False)
|
|
op.create_index(op.f('ix_jobs_id'), 'jobs', ['id'], unique=False)
|
|
op.create_index(op.f('ix_jobs_user_id'), 'jobs', ['user_id'], unique=False)
|
|
|
|
# Create google_tokens table
|
|
op.create_table('google_tokens',
|
|
sa.Column('id', sa.Integer(), nullable=False),
|
|
sa.Column('user_id', sa.Integer(), nullable=False),
|
|
sa.Column('encrypted_token', sa.Text(), nullable=False),
|
|
sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=True),
|
|
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
|
|
sa.PrimaryKeyConstraint('id')
|
|
)
|
|
op.create_index(op.f('ix_google_tokens_id'), 'google_tokens', ['id'], unique=False)
|
|
op.create_index(op.f('ix_google_tokens_user_id'), 'google_tokens', ['user_id'], unique=True)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_table('google_tokens')
|
|
op.drop_table('jobs')
|
|
op.drop_table('send_logs')
|
|
op.drop_table('campaign_recipients')
|
|
op.drop_table('campaigns')
|
|
op.drop_table('templates')
|
|
op.drop_table('list_members')
|
|
op.drop_table('lists')
|
|
op.drop_table('dnd_list')
|
|
op.drop_table('contact_tag_map')
|
|
op.drop_table('contact_tags')
|
|
op.drop_table('contacts')
|
|
op.drop_table('users')
|