Leopoldadmin/leocrm
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
leocrm/alembic/versions/0021_unified_contacts.py
T

166 lines
9.7 KiB
Python
Raw Permalink Blame History

"""Unified contacts model — company or person with inline addresses.
Revision ID: 0021
Revises: 0020
Create Date: 2026-07-19
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID, TSVECTOR, JSON
revision = "0021_unified_contacts"
down_revision = "0020"
def upgrade():
# 1. Drop old company_contacts join table
op.execute("DROP TABLE IF EXISTS company_contacts CASCADE")
# 2. Drop old contacts table (will recreate with new schema)
op.execute("DROP TABLE IF EXISTS contacts CASCADE")
# 3. Drop old companies table
op.execute("DROP TABLE IF EXISTS companies CASCADE")
# 4. Create contacts table (without default_person_id/admin_contactperson_id FKs first)
op.create_table(
"contacts",
sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
sa.Column("tenant_id", UUID(as_uuid=True), nullable=False, index=True),
# Identity & Type
sa.Column("type", sa.String(20), nullable=False, server_default="company"),
sa.Column("displayname", sa.String(255), nullable=False, server_default=""),
sa.Column("name", sa.String(255), nullable=True),
sa.Column("firstname", sa.String(100), nullable=True),
sa.Column("surname", sa.String(100), nullable=True),
sa.Column("surfix", sa.String(50), nullable=True),
sa.Column("ext_name_line", sa.String(255), nullable=True),
sa.Column("gender", sa.String(20), nullable=True),
# Customer / Accounting
sa.Column("code", sa.String(100), nullable=True),
sa.Column("accounting_code", sa.String(100), nullable=True),
sa.Column("vendor_accounting_code", sa.String(100), nullable=True),
# Mailing Address
sa.Column("mailing_street", sa.String(255), nullable=True),
sa.Column("mailing_number", sa.String(20), nullable=True),
sa.Column("mailing_unit_number", sa.String(50), nullable=True),
sa.Column("mailing_district", sa.String(100), nullable=True),
sa.Column("mailing_extra_address_line", sa.String(255), nullable=True),
sa.Column("mailing_postalcode", sa.String(20), nullable=True),
sa.Column("mailing_city", sa.String(100), nullable=True),
sa.Column("mailing_state", sa.String(100), nullable=True),
sa.Column("mailing_country", sa.String(2), nullable=True),
# Visit Address
sa.Column("visit_street", sa.String(255), nullable=True),
sa.Column("visit_number", sa.String(20), nullable=True),
sa.Column("visit_unit_number", sa.String(50), nullable=True),
sa.Column("visit_district", sa.String(100), nullable=True),
sa.Column("visit_extra_address_line", sa.String(255), nullable=True),
sa.Column("visit_postalcode", sa.String(20), nullable=True),
sa.Column("visit_city", sa.String(100), nullable=True),
sa.Column("visit_state", sa.String(100), nullable=True),
# Invoice Address
sa.Column("invoice_street", sa.String(255), nullable=True),
sa.Column("invoice_number", sa.String(20), nullable=True),
sa.Column("invoice_unit_number", sa.String(50), nullable=True),
sa.Column("invoice_district", sa.String(100), nullable=True),
sa.Column("invoice_extra_address_line", sa.String(255), nullable=True),
sa.Column("invoice_postalcode", sa.String(20), nullable=True),
sa.Column("invoice_city", sa.String(100), nullable=True),
sa.Column("invoice_state", sa.String(100), nullable=True),
sa.Column("invoice_country", sa.String(2), nullable=True),
# General country
sa.Column("country", sa.String(2), nullable=True),
# Communication
sa.Column("phone_1", sa.String(50), nullable=True),
sa.Column("phone_2", sa.String(50), nullable=True),
sa.Column("email_1", sa.String(255), nullable=True),
sa.Column("email_2", sa.String(255), nullable=True),
sa.Column("website", sa.String(500), nullable=True),
# Financial & Tax
sa.Column("vat_code", sa.String(50), nullable=True),
sa.Column("fiscal_code", sa.String(50), nullable=True),
sa.Column("commerce_code", sa.String(100), nullable=True),
sa.Column("purchase_number", sa.String(100), nullable=True),
sa.Column("bic", sa.String(50), nullable=True),
sa.Column("bank_account", sa.String(50), nullable=True),
# Discounts
sa.Column("discount_crew", sa.Float, nullable=False, server_default="0"),
sa.Column("discount_transport", sa.Float, nullable=False, server_default="0"),
sa.Column("discount_rental", sa.Float, nullable=False, server_default="0"),
sa.Column("discount_sale", sa.Float, nullable=False, server_default="0"),
sa.Column("discount_subrent", sa.Float, nullable=False, server_default="0"),
sa.Column("discount_total", sa.Float, nullable=False, server_default="0"),
# Geo
sa.Column("latitude", sa.Float, nullable=True),
sa.Column("longitude", sa.Float, nullable=True),
# Notes & Warnings
sa.Column("projectnote", sa.Text, nullable=True),
sa.Column("projectnote_title", sa.String(255), nullable=True),
sa.Column("contact_warning", sa.Text, nullable=True),
sa.Column("tags", sa.String(500), nullable=True),
sa.Column("image", sa.Text, nullable=True),
# Custom fields
sa.Column("custom", JSON, nullable=True, server_default=sa.text("'{}'::json")),
# FTS
sa.Column("search_tsv", TSVECTOR, sa.Computed(
"to_tsvector('german', coalesce(name, '') || ' ' || coalesce(displayname, '') || ' ' || coalesce(firstname, '') || ' ' || coalesce(surname, '') || ' ' || coalesce(email_1, '') || ' ' || coalesce(email_2, '') || ' ' || coalesce(code, '') || ' ' || coalesce(phone_1, '') || ' ' || coalesce(phone_2, '') || ' ' || coalesce(mailing_city, '') || ' ' || coalesce(mailing_postalcode, '') || ' ' || coalesce(tags, ''))",
persisted=True,
), nullable=True),
# Audit
sa.Column("created_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
sa.Column("updated_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
)
op.create_index("ix_contacts_tenant_deleted", "contacts", ["tenant_id", "deleted_at"])
op.create_index("ix_contacts_tenant_type", "contacts", ["tenant_id", "type"])
op.create_index("ix_contacts_tenant_name", "contacts", ["tenant_id", "name"])
op.create_index("ix_contacts_tenant_displayname", "contacts", ["tenant_id", "displayname"])
op.create_index("ix_contacts_email", "contacts", ["email_1"])
op.create_index("ix_contacts_code", "contacts", ["code"])
op.create_index("ix_contacts_search_vec", "contacts", ["search_tsv"], postgresql_using="gin")
# 5. Create contactpersons table
op.create_table(
"contactpersons",
sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
sa.Column("tenant_id", UUID(as_uuid=True), nullable=False, index=True),
sa.Column("contact_id", UUID(as_uuid=True), sa.ForeignKey("contacts.id", ondelete="CASCADE"), nullable=False),
sa.Column("displayname", sa.String(255), nullable=False, server_default=""),
sa.Column("firstname", sa.String(100), nullable=True),
sa.Column("middle_name", sa.String(100), nullable=True),
sa.Column("lastname", sa.String(100), nullable=True),
sa.Column("function", sa.String(255), nullable=True),
sa.Column("phone", sa.String(50), nullable=True),
sa.Column("mobilephone", sa.String(50), nullable=True),
sa.Column("email", sa.String(255), nullable=True),
sa.Column("street", sa.String(255), nullable=True),
sa.Column("number", sa.String(20), nullable=True),
sa.Column("postalcode", sa.String(20), nullable=True),
sa.Column("city", sa.String(100), nullable=True),
sa.Column("state", sa.String(100), nullable=True),
sa.Column("country", sa.String(2), nullable=True),
sa.Column("tags", sa.String(500), nullable=True),
sa.Column("custom", JSON, nullable=True, server_default=sa.text("'{}'::json")),
sa.Column("created_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
sa.Column("updated_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
)
op.create_index("ix_contactpersons_tenant_deleted", "contactpersons", ["tenant_id", "deleted_at"])
op.create_index("ix_contactpersons_contact", "contactpersons", ["contact_id"])
op.create_index("ix_contactpersons_email", "contactpersons", ["email"])
# 6. Add FK columns to contacts that reference contactpersons
op.add_column("contacts", sa.Column("default_person_id", UUID(as_uuid=True), sa.ForeignKey("contactpersons.id", ondelete="SET NULL"), nullable=True))
op.add_column("contacts", sa.Column("admin_contactperson_id", UUID(as_uuid=True), sa.ForeignKey("contactpersons.id", ondelete="SET NULL"), nullable=True))
def downgrade():
op.drop_table("contacts")
op.drop_table("contactpersons")
Reference in New Issue View Git Blame Copy Permalink