Alembic detects FK changes on PostgreSQL when not using public schema

[piziakkodis]

Describe the bug
Alembic always dectects changes on FKs to apply to the database, when not using 'public' and m2m

Expected behavior
not constantly dropping and recreating foreign keys

To Reproduce
Please try to provide a Minimal, Complete, and Verifiable example, with the migration script and/or the SQLAlchemy tables or models involved.
See also Reporting Bugs on the website.

from sqlalchemy import create_engine, text as sqlatext, Column, Integer, String, DateTime, DECIMAL, Table, ForeignKey, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

dbschema = 'iris'
engine = create_engine(DATABASE_URL, connect_args={})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
Base.metadata.schema = dbschema

user_group_association = Table(
    'user_group', Base.metadata,
    Column('user_id', Integer, ForeignKey('iris.users.id', name="fk_user_group_user_id"),primary_key=True),
    Column('group_id', Integer, ForeignKey('iris.groups.id', name="fk_user_group_group_id"),primary_key=True),
    schema=dbschema,
)

service_group_association = Table(
    'service_group',
    Base.metadata,
    Column('service_id', Integer, ForeignKey('iris.services.id', name="fk_service_group_service_id"), primary_key=True),
    Column('group_id', Integer, ForeignKey('iris.groups.id', name="fk_service_group_group_id"), primary_key=True),
    schema=dbschema,
)

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    first_name = Column(String, nullable=True)
    last_name = Column(String, nullable=True)
    groups = relationship('Group', secondary=user_group_association, back_populates='users')

class Group(Base):
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, index=True)
    mapped_to = Column(String, nullable=True) 
    users = relationship('User', secondary=user_group_association, back_populates='groups')
    services = relationship('Service', secondary=service_group_association, back_populates='groups')

Error
alembic revision --autogenerate -m "initial2" will endlessly detect changes and drop/create FK relation.
after alembic upgrade head it will detect the same changes and create the same migration again.

def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(op.f('fk_service_group_service_id'), 'service_group', type_='foreignkey')
    op.drop_constraint(op.f('fk_service_group_group_id'), 'service_group', type_='foreignkey')
    op.create_foreign_key('fk_service_group_group_id', 'service_group', 'groups', ['group_id'], ['id'], source_schema='iris', referent_schema='iris')
    op.create_foreign_key('fk_service_group_service_id', 'service_group', 'services', ['service_id'], ['id'], source_schema='iris', referent_schema='iris')
    op.drop_constraint(op.f('fk_user_group_user_id'), 'user_group', type_='foreignkey')
    op.drop_constraint(op.f('fk_user_group_group_id'), 'user_group', type_='foreignkey')
    op.create_foreign_key('fk_user_group_user_id', 'user_group', 'users', ['user_id'], ['id'], source_schema='iris', referent_schema='iris')
    op.create_foreign_key('fk_user_group_group_id', 'user_group', 'groups', ['group_id'], ['id'], source_schema='iris', referent_schema='iris')
    # ### end Alembic commands ###

Versions.

• OS: windows 11
• Python: 3.12.4
• Alembic: 1.16.5
• SQLAlchemy: 2.0.23
• Database: PostgreSQL 14.19 (Ubuntu 14.19-0ubuntu0.22.04.1)
• DBAPI: psycopg 3.2.9

Additional context
env.py part:

def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        # make use of non-supported SQLAlchemy attribute to ensure
        # the dialect reflects tables in terms of the current tenant name
        connection.dialect.default_schema_name = 'iris'

        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            #include_schemas=True,
            #version_table_schema='iris'
        )

        connection.execute(text("CREATE SCHEMA IF NOT EXISTS iris"))

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Have a nice day!

[zzzeek]

hi -

the source of the issue is in your env.py:

        # the dialect reflects tables in terms of the current tenant name
        connection.dialect.default_schema_name = 'iris'

doing multi-tenancy on PostgreSQL schemas is difficult. For this reason we have a complete recipe for how to do this here: https://alembic.sqlalchemy.org/en/latest/cookbook.html#rudimental-schema-level-multi-tenancy-for-postgresql-mysql-other-databases

the main thing is that this all goes off of the PG schema search path. See https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path for background

[zzzeek]

OK here is the first reflection query emitted, which is getting the names of the schemas:

# yours
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_namespace.nspname 
FROM pg_catalog.pg_namespace
WHERE pg_catalog.pg_namespace.nspname NOT LIKE %(nspname_1)s::VARCHAR ORDER BY pg_catalog.pg_namespace.nspname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00039s] {'nspname_1': 'pg_%'}
DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)

you can see it returned the three schemas. when I run it on mine, I can recognize this is the psycopg driver so I used that, I get similar results, OK:

# mine
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_namespace.nspname 
FROM pg_catalog.pg_namespace 
WHERE pg_catalog.pg_namespace.nspname NOT LIKE %(nspname_1)s::VARCHAR ORDER BY pg_catalog.pg_namespace.nspname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00005s] {'nspname_1': 'pg_%'}
DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)
DEBUG [sqlalchemy.engine.Engine] Row ('test_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('test_schema_2',)

but then yours does this:

# yours
INFO  [sqlalchemy.engine.Engine] COMMIT
Generating C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\versions\f2c8a3d58e15_initial.py ...  done

woah! totally wrong. The "iris" schema is there and your include_name says to use it! Mine proceeds with about 50 more queries after that, the one immediately following gets the names of tables in "iris":

# mine
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR]) AND pg_catalog.pg_class.relpersistence != %(relpersistence_1)s::VARCHAR AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00007s] {'param_1': 'r', 'param_2': 'p', 'relpersistence_1': 't', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
DEBUG [sqlalchemy.engine.Engine] Row ('a',)
DEBUG [sqlalchemy.engine.Engine] Row ('groups',)
DEBUG [sqlalchemy.engine.Engine] Row ('user_group',)
DEBUG [sqlalchemy.engine.Engine] Row ('users',)

those are the names of tables I have in my "iris" schema. Yours didnt even run the query.

So we think that your environment is not configured correctly.

We want to have include_schemas=True and include_name with the function (which I'm going to give you) in the run_migrations_online() method. Use this include_name function as written:

def include_name(name, type_, parent_names):
    id = f"### INCLUDE_NAME: NAME: {name} TYPE: {type_} PARENT_NAMES: {parent_names}"
    if type_ == "schema":
        val = name in ["iris"]
        print(f"{id} RETURNING {val}")
        return val
    else:
        print(f"{id} RETURNING TRUE!!!")
        return True

then, when you run for the revision 2 autogenerate, you should see all those loud print statements. Here's mine right at the spot where we want to see if it's getting picked up:

# mine
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_namespace.nspname 
FROM pg_catalog.pg_namespace 
WHERE pg_catalog.pg_namespace.nspname NOT LIKE %(nspname_1)s::VARCHAR ORDER BY pg_catalog.pg_namespace.nspname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00006s] {'nspname_1': 'pg_%'}
DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)
DEBUG [sqlalchemy.engine.Engine] Row ('test_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('test_schema_2',)
### INCLUDE_NAME: NAME: test_schema_2 TYPE: schema PARENT_NAMES: {} RETURNING False
### INCLUDE_NAME: NAME: None TYPE: schema PARENT_NAMES: {} RETURNING False
### INCLUDE_NAME: NAME: iris TYPE: schema PARENT_NAMES: {} RETURNING True
### INCLUDE_NAME: NAME: test_schema TYPE: schema PARENT_NAMES: {} RETURNING False
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace 
WHERE pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR]) AND pg_catalog.pg_class.relpersistence != %(relpersistence_1)s::VARCHAR AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00006s] {'param_1': 'r', 'param_2': 'p', 'relpersistence_1': 't', 'nspname_1': 'iris'}

Note that a. we see the INCLUDE_NAME print statements - our function is being called! and b. when it gets to iris, it returns True. Then it immediately proceeds into looking at iris.

right now it looks like include_name is there and returning False in all cases. Make sure also your code is not changing that default_schema_name attribute anywhere - note that when SQLAlchemy hits the "default" schema, it returns None, not the name. We are doing approach 2 where we want it to use iris and that's not the default schema.

[piziakkodis]

Thanks,
here are the updated logs.
Schema resetted before by running

drop schema iris cascade;
create schema iris;
commit;

It seems to always returning FALSE. :-(

current env.py with the modification of include_name

...
from services import dbmodels as dbm
target_metadata = dbm.Base.metadata # target_metadata.schema equals iris

def include_name(name, type_, parent_names):
    id = f"### INCLUDE_NAME: NAME: {name} TYPE: {type_} PARENT_NAMES: {parent_names}"
    if type_ == "schema":
        val = name in ["iris"]
        print(f"{id} RETURNING {val}")
        return val
    else:
        print(f"{id} RETURNING TRUE!!!")
        return True


...

def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata, # target_metadata.schema equals iris
            include_schemas=True,
            include_name=include_name,
            version_table_schema = "iris",
        )

        connection.execute(text("CREATE SCHEMA IF NOT EXISTS iris"))

        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

STEP 1 - create initial migration

(.bevenv) PS C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend> alembic revision --autogenerate -m "initial" 
INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.Engine] Row ('PostgreSQL 14.19 (Ubuntu 14.19-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit',)
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('standard_conforming_strings',)
DEBUG [sqlalchemy.engine.Engine] Row ('on',)
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] CREATE SCHEMA IF NOT EXISTS iris
INFO  [sqlalchemy.engine.Engine] [generated in 0.00075s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00063s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [cached since 0.04343s ago] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
INFO  [sqlalchemy.engine.Engine]
CREATE TABLE iris.alembic_version (
        version_num VARCHAR(32) NOT NULL,
        CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00093s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_namespace.nspname 
FROM pg_catalog.pg_namespace
WHERE pg_catalog.pg_namespace.nspname NOT LIKE %(nspname_1)s::VARCHAR ORDER BY pg_catalog.pg_namespace.nspname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00063s] {'nspname_1': 'pg_%'}
DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)
### INCLUDE_NAME: NAME: None TYPE: schema PARENT_NAMES: {} RETURNING False
### INCLUDE_NAME: NAME: public TYPE: schema PARENT_NAMES: {} RETURNING False
INFO  [sqlalchemy.engine.Engine] COMMIT
Generating C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\versions\2a7b8a783975_initial.py ...  done

STEP 2 - upgrade initial migration

(.bevenv) PS C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend> alembic upgrade head                        
INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.Engine] Row ('PostgreSQL 14.19 (Ubuntu 14.19-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit',)
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('standard_conforming_strings',)
DEBUG [sqlalchemy.engine.Engine] Row ('on',)
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] CREATE SCHEMA IF NOT EXISTS iris
INFO  [sqlalchemy.engine.Engine] [generated in 0.00054s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00037s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
DEBUG [sqlalchemy.engine.Engine] Row ('alembic_version',)
INFO  [sqlalchemy.engine.Engine] SELECT iris.alembic_version.version_num
FROM iris.alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00045s] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version_num',)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [cached since 0.1005s ago] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
DEBUG [sqlalchemy.engine.Engine] Row ('alembic_version',)
INFO  [sqlalchemy.engine.Engine]
CREATE TABLE iris.chat_history (
        id SERIAL NOT NULL,
        user_id VARCHAR,
        conversation_id VARCHAR NOT NULL,
        service_name VARCHAR NOT NULL,
        search_index VARCHAR NOT NULL,
        user_prompt VARCHAR NOT NULL,
        bot_response VARCHAR NOT NULL,
        error VARCHAR,
        citations VARCHAR,
        messages_length INTEGER NOT NULL,
        response_time DECIMAL(10, 2) NOT NULL,
        model VARCHAR,
        completion_tokens INTEGER,
        prompt_tokens INTEGER,
        total_tokens INTEGER,
        user_vote INTEGER,
        timestamp TIMESTAMP WITHOUT TIME ZONE,
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00039s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_chat_history_conversation_id ON iris.chat_history (conversation_id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00067s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_chat_history_id ON iris.chat_history (id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00052s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_chat_history_search_index ON iris.chat_history (search_index)
INFO  [sqlalchemy.engine.Engine] [no key 0.00042s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_chat_history_service_name ON iris.chat_history (service_name)
INFO  [sqlalchemy.engine.Engine] [no key 0.00053s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_chat_history_user_id ON iris.chat_history (user_id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00051s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE iris.groups (
        id SERIAL NOT NULL,
        name VARCHAR,
        mapped_to VARCHAR,
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00061s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_groups_id ON iris.groups (id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00047s] {}
INFO  [sqlalchemy.engine.Engine] CREATE UNIQUE INDEX ix_iris_groups_name ON iris.groups (name)
INFO  [sqlalchemy.engine.Engine] [no key 0.00030s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE iris.services (
        id SERIAL NOT NULL,
        name VARCHAR,
        type VARCHAR NOT NULL,
        api_key VARCHAR,
        api_version VARCHAR,
        azure_endpoint VARCHAR,
        model VARCHAR,
        search_api_key VARCHAR,
        search_endpoint VARCHAR,
        active BOOLEAN NOT NULL,
        system_prompt VARCHAR,
        search_index VARCHAR,
        search_results_limit INTEGER,
        search_results_score_treshold DECIMAL(10, 2),
        additional_search_params VARCHAR,
        function_names VARCHAR,
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00067s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_services_id ON iris.services (id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00035s] {}
INFO  [sqlalchemy.engine.Engine] CREATE UNIQUE INDEX ix_iris_services_name ON iris.services (name)
INFO  [sqlalchemy.engine.Engine] [no key 0.00025s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE iris.users (
        id SERIAL NOT NULL,
        username VARCHAR,
        email VARCHAR,
        hashed_password VARCHAR,
        created_at TIMESTAMP WITHOUT TIME ZONE,
        first_name VARCHAR,
        last_name VARCHAR,
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00065s] {}
INFO  [sqlalchemy.engine.Engine] CREATE UNIQUE INDEX ix_iris_users_email ON iris.users (email)
INFO  [sqlalchemy.engine.Engine] [no key 0.00051s] {}
INFO  [sqlalchemy.engine.Engine] CREATE INDEX ix_iris_users_id ON iris.users (id)
INFO  [sqlalchemy.engine.Engine] [no key 0.00028s] {}
INFO  [sqlalchemy.engine.Engine] CREATE UNIQUE INDEX ix_iris_users_username ON iris.users (username)
INFO  [sqlalchemy.engine.Engine] [no key 0.00033s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE iris.service_group (
        service_id INTEGER NOT NULL,
        group_id INTEGER NOT NULL,
        PRIMARY KEY (service_id, group_id),
        CONSTRAINT fk_service_group_group_id FOREIGN KEY(group_id) REFERENCES iris.groups (id),
        CONSTRAINT fk_service_group_service_id FOREIGN KEY(service_id) REFERENCES iris.services (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00041s] {}
INFO  [sqlalchemy.engine.Engine] 
CREATE TABLE iris.user_group (
        user_id INTEGER NOT NULL,
        group_id INTEGER NOT NULL,
        PRIMARY KEY (user_id, group_id),
        CONSTRAINT fk_user_group_group_id FOREIGN KEY(group_id) REFERENCES iris.groups (id),
        CONSTRAINT fk_user_group_user_id FOREIGN KEY(user_id) REFERENCES iris.users (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00056s] {}
INFO  [sqlalchemy.engine.Engine] INSERT INTO iris.alembic_version (version_num) VALUES ('2a7b8a783975') RETURNING iris.alembic_version.version_num
INFO  [sqlalchemy.engine.Engine] [generated in 0.00046s] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version_num',)
DEBUG [sqlalchemy.engine.Engine] Row ('2a7b8a783975',)
INFO  [sqlalchemy.engine.Engine] COMMIT

STEP 3 - create initial2 migration

(.bevenv) PS C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend> alembic revision --autogenerate -m "initial2"
INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.Engine] Row ('PostgreSQL 14.19 (Ubuntu 14.19-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit',)
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('standard_conforming_strings',)
DEBUG [sqlalchemy.engine.Engine] Row ('on',)
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] CREATE SCHEMA IF NOT EXISTS iris
INFO  [sqlalchemy.engine.Engine] [generated in 0.00065s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00054s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
DEBUG [sqlalchemy.engine.Engine] Row ('alembic_version',)
INFO  [sqlalchemy.engine.Engine] SELECT iris.alembic_version.version_num
FROM iris.alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00053s] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version_num',)
DEBUG [sqlalchemy.engine.Engine] Row ('2a7b8a783975',)
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_namespace.nspname
FROM pg_catalog.pg_namespace
WHERE pg_catalog.pg_namespace.nspname NOT LIKE %(nspname_1)s::VARCHAR ORDER BY pg_catalog.pg_namespace.nspname
INFO  [sqlalchemy.engine.Engine] [generated in 0.00036s] {'nspname_1': 'pg_%'}
DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)
### INCLUDE_NAME: NAME: None TYPE: schema PARENT_NAMES: {} RETURNING False
### INCLUDE_NAME: NAME: public TYPE: schema PARENT_NAMES: {} RETURNING False
INFO  [sqlalchemy.engine.Engine] COMMIT
Generating C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\versions\e5852774f1c6_initial2.py ...  done

STEP 4 - upgrade to initial2 migration

(.bevenv) PS C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend> alembic upgrade head                         
INFO  [sqlalchemy.engine.Engine] select pg_catalog.version()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.Engine] Row ('PostgreSQL 14.19 (Ubuntu 14.19-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.2) 11.4.0, 64-bit',)
INFO  [sqlalchemy.engine.Engine] select current_schema()
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
INFO  [sqlalchemy.engine.Engine] show standard_conforming_strings
INFO  [sqlalchemy.engine.Engine] [raw sql] {}
DEBUG [sqlalchemy.engine.Engine] Col ('standard_conforming_strings',)
DEBUG [sqlalchemy.engine.Engine] Row ('on',)
INFO  [sqlalchemy.engine.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.Engine] CREATE SCHEMA IF NOT EXISTS iris
INFO  [sqlalchemy.engine.Engine] [generated in 0.00058s] {}
INFO  [sqlalchemy.engine.Engine] SELECT pg_catalog.pg_class.relname 
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s::VARCHAR AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s::VARCHAR, %(param_2)s::VARCHAR, %(param_3)s::VARCHAR, %(param_4)s::VARCHAR, %(param_5)s::VARCHAR]) AND pg_catalog.pg_namespace.nspname = %(nspname_1)s::VARCHAR
INFO  [sqlalchemy.engine.Engine] [generated in 0.00066s] {'table_name': 'alembic_version', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'iris'}
DEBUG [sqlalchemy.engine.Engine] Col ('relname',)
DEBUG [sqlalchemy.engine.Engine] Row ('alembic_version',)
INFO  [sqlalchemy.engine.Engine] SELECT iris.alembic_version.version_num
FROM iris.alembic_version
INFO  [sqlalchemy.engine.Engine] [generated in 0.00015s] {}
DEBUG [sqlalchemy.engine.Engine] Col ('version_num',)
DEBUG [sqlalchemy.engine.Engine] Row ('2a7b8a783975',)
INFO  [sqlalchemy.engine.Engine]
CREATE TABLE iris.chat_history (
        id SERIAL NOT NULL,
        user_id VARCHAR,
        conversation_id VARCHAR NOT NULL,
        service_name VARCHAR NOT NULL,
        search_index VARCHAR NOT NULL,
        user_prompt VARCHAR NOT NULL,
        bot_response VARCHAR NOT NULL,
        error VARCHAR,
        citations VARCHAR,
        messages_length INTEGER NOT NULL,
        response_time DECIMAL(10, 2) NOT NULL,
        model VARCHAR,
        completion_tokens INTEGER,
        prompt_tokens INTEGER,
        total_tokens INTEGER,
        user_vote INTEGER,
        timestamp TIMESTAMP WITHOUT TIME ZONE,
        PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.Engine] [no key 0.00092s] {}
INFO  [sqlalchemy.engine.Engine] ROLLBACK
Traceback (most recent call last):
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\psycopg\cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.DuplicateTable: relation "chat_history" already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Scripts\alembic.exe\__main__.py", line 7, in <module>
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\config.py", line 1022, in main
    CommandLine(prog=prog).main(argv=argv)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\config.py", line 1012, in main
    self.run_cmd(cfg, options)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\config.py", line 946, in run_cmd
    fn(
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\command.py", line 483, in upgrade
    script.run_env()
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\script\base.py", line 549, in run_env
    util.load_python_file(self.dir, "env.py")
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\util\pyfiles.py", line 116, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\util\pyfiles.py", line 136, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 995, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\env.py", line 94, in <module>
    run_migrations_online()
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\env.py", line 88, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\runtime\environment.py", line 946, in run_migrations
    self.get_context().run_migrations(**kw)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\runtime\migration.py", line 627, in run_migrations
    step.migration_fn(**kw)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\migrations\versions\e5852774f1c6_initial2.py", line 24, in upgrade
    op.create_table('chat_history',
  File "<string>", line 8, in create_table
  File "<string>", line 3, in create_table
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\operations\ops.py", line 1332, in create_table
    return operations.invoke(op)
           ^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\operations\base.py", line 441, in invoke
    return fn(self, operation)
           ^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\operations\toimpl.py", line 135, in create_table
    operations.impl.create_table(table, **kw)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\ddl\impl.py", line 423, in create_table
    self._exec(schema.CreateTable(table, **kw))
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\alembic\ddl\impl.py", line 246, in _exec
    return conn.execute(construct, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\sql\ddl.py", line 181, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1528, in _execute_ddl
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\sqlalchemy\engine\default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend\.bevenv\Lib\site-packages\psycopg\cursor.py", line 97, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.DuplicateTable) relation "chat_history" already exists
[SQL:
CREATE TABLE iris.chat_history (
        id SERIAL NOT NULL,
        user_id VARCHAR,
        conversation_id VARCHAR NOT NULL,
        service_name VARCHAR NOT NULL,
        search_index VARCHAR NOT NULL,
        user_prompt VARCHAR NOT NULL,
        bot_response VARCHAR NOT NULL,
        error VARCHAR,
        citations VARCHAR,
        messages_length INTEGER NOT NULL,
        response_time DECIMAL(10, 2) NOT NULL,
        model VARCHAR,
        completion_tokens INTEGER,
        prompt_tokens INTEGER,
        total_tokens INTEGER,
        user_vote INTEGER,
        timestamp TIMESTAMP WITHOUT TIME ZONE,
        PRIMARY KEY (id)
)

]
(Background on this error at: https://sqlalche.me/e/20/f405)
(.bevenv) PS C:\dev\ai\trunk\aichat_fullstack_react_fastapi\backend> 

2a7b8a783975_initial.py

"""initial

Revision ID: 2a7b8a783975
Revises: 
Create Date: 2025-09-25 10:24:42.593882

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '2a7b8a783975'
down_revision: Union[str, Sequence[str], None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('chat_history',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=True),
    sa.Column('conversation_id', sa.String(), nullable=False),
    sa.Column('service_name', sa.String(), nullable=False),
    sa.Column('search_index', sa.String(), nullable=False),
    sa.Column('user_prompt', sa.String(), nullable=False),
    sa.Column('bot_response', sa.String(), nullable=False),
    sa.Column('error', sa.String(), nullable=True),
    sa.Column('citations', sa.String(), nullable=True),
    sa.Column('messages_length', sa.Integer(), nullable=False),
    sa.Column('response_time', sa.DECIMAL(precision=10, scale=2), nullable=False),
    sa.Column('model', sa.String(), nullable=True),
    sa.Column('completion_tokens', sa.Integer(), nullable=True),
    sa.Column('prompt_tokens', sa.Integer(), nullable=True),
    sa.Column('total_tokens', sa.Integer(), nullable=True),
    sa.Column('user_vote', sa.Integer(), nullable=True),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_chat_history_conversation_id'), 'chat_history', ['conversation_id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_id'), 'chat_history', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_search_index'), 'chat_history', ['search_index'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_service_name'), 'chat_history', ['service_name'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_user_id'), 'chat_history', ['user_id'], unique=False, schema='iris')
    op.create_table('groups',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('mapped_to', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_groups_id'), 'groups', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_groups_name'), 'groups', ['name'], unique=True, schema='iris')
    op.create_table('services',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('type', sa.String(), nullable=False),
    sa.Column('api_key', sa.String(), nullable=True),
    sa.Column('api_version', sa.String(), nullable=True),
    sa.Column('azure_endpoint', sa.String(), nullable=True),
    sa.Column('model', sa.String(), nullable=True),
    sa.Column('search_api_key', sa.String(), nullable=True),
    sa.Column('search_endpoint', sa.String(), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=False),
    sa.Column('system_prompt', sa.String(), nullable=True),
    sa.Column('search_index', sa.String(), nullable=True),
    sa.Column('search_results_limit', sa.Integer(), nullable=True),
    sa.Column('search_results_score_treshold', sa.DECIMAL(precision=10, scale=2), nullable=True),
    sa.Column('additional_search_params', sa.String(), nullable=True),
    sa.Column('function_names', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_services_id'), 'services', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_services_name'), 'services', ['name'], unique=True, schema='iris')
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('hashed_password', sa.String(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('first_name', sa.String(), nullable=True),
    sa.Column('last_name', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_users_email'), 'users', ['email'], unique=True, schema='iris')
    op.create_index(op.f('ix_iris_users_id'), 'users', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_users_username'), 'users', ['username'], unique=True, schema='iris')
    op.create_table('service_group',
    sa.Column('service_id', sa.Integer(), nullable=False),
    sa.Column('group_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['iris.groups.id'], name='fk_service_group_group_id'),
    sa.ForeignKeyConstraint(['service_id'], ['iris.services.id'], name='fk_service_group_service_id'),
    sa.PrimaryKeyConstraint('service_id', 'group_id'),
    schema='iris'
    )
    op.create_table('user_group',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('group_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['iris.groups.id'], name='fk_user_group_group_id'),
    sa.ForeignKeyConstraint(['user_id'], ['iris.users.id'], name='fk_user_group_user_id'),
    sa.PrimaryKeyConstraint('user_id', 'group_id'),
    schema='iris'
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('user_group', schema='iris')
    op.drop_table('service_group', schema='iris')
    op.drop_index(op.f('ix_iris_users_username'), table_name='users', schema='iris')
    op.drop_index(op.f('ix_iris_users_id'), table_name='users', schema='iris')
    op.drop_index(op.f('ix_iris_users_email'), table_name='users', schema='iris')
    op.drop_table('users', schema='iris')
    op.drop_index(op.f('ix_iris_services_name'), table_name='services', schema='iris')
    op.drop_index(op.f('ix_iris_services_id'), table_name='services', schema='iris')
    op.drop_table('services', schema='iris')
    op.drop_index(op.f('ix_iris_groups_name'), table_name='groups', schema='iris')
    op.drop_index(op.f('ix_iris_groups_id'), table_name='groups', schema='iris')
    op.drop_table('groups', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_user_id'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_service_name'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_search_index'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_id'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_conversation_id'), table_name='chat_history', schema='iris')
    op.drop_table('chat_history', schema='iris')
    # ### end Alembic commands ###

e5852774f1c6_initial2.py

"""initial2

Revision ID: e5852774f1c6
Revises: 2a7b8a783975
Create Date: 2025-09-25 10:28:07.994779

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = 'e5852774f1c6'
down_revision: Union[str, Sequence[str], None] = '2a7b8a783975'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('chat_history',
    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=True),
    sa.Column('conversation_id', sa.String(), nullable=False),
    sa.Column('service_name', sa.String(), nullable=False),
    sa.Column('search_index', sa.String(), nullable=False),
    sa.Column('user_prompt', sa.String(), nullable=False),
    sa.Column('bot_response', sa.String(), nullable=False),
    sa.Column('error', sa.String(), nullable=True),
    sa.Column('citations', sa.String(), nullable=True),
    sa.Column('messages_length', sa.Integer(), nullable=False),
    sa.Column('response_time', sa.DECIMAL(precision=10, scale=2), nullable=False),
    sa.Column('model', sa.String(), nullable=True),
    sa.Column('completion_tokens', sa.Integer(), nullable=True),
    sa.Column('prompt_tokens', sa.Integer(), nullable=True),
    sa.Column('total_tokens', sa.Integer(), nullable=True),
    sa.Column('user_vote', sa.Integer(), nullable=True),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_chat_history_conversation_id'), 'chat_history', ['conversation_id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_id'), 'chat_history', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_search_index'), 'chat_history', ['search_index'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_service_name'), 'chat_history', ['service_name'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_chat_history_user_id'), 'chat_history', ['user_id'], unique=False, schema='iris')
    op.create_table('groups',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('mapped_to', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_groups_id'), 'groups', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_groups_name'), 'groups', ['name'], unique=True, schema='iris')
    op.create_table('services',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('type', sa.String(), nullable=False),
    sa.Column('api_key', sa.String(), nullable=True),
    sa.Column('api_version', sa.String(), nullable=True),
    sa.Column('azure_endpoint', sa.String(), nullable=True),
    sa.Column('model', sa.String(), nullable=True),
    sa.Column('search_api_key', sa.String(), nullable=True),
    sa.Column('search_endpoint', sa.String(), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=False),
    sa.Column('system_prompt', sa.String(), nullable=True),
    sa.Column('search_index', sa.String(), nullable=True),
    sa.Column('search_results_limit', sa.Integer(), nullable=True),
    sa.Column('search_results_score_treshold', sa.DECIMAL(precision=10, scale=2), nullable=True),
    sa.Column('additional_search_params', sa.String(), nullable=True),
    sa.Column('function_names', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_services_id'), 'services', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_services_name'), 'services', ['name'], unique=True, schema='iris')
    op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('username', sa.String(), nullable=True),
    sa.Column('email', sa.String(), nullable=True),
    sa.Column('hashed_password', sa.String(), nullable=True),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('first_name', sa.String(), nullable=True),
    sa.Column('last_name', sa.String(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    schema='iris'
    )
    op.create_index(op.f('ix_iris_users_email'), 'users', ['email'], unique=True, schema='iris')
    op.create_index(op.f('ix_iris_users_id'), 'users', ['id'], unique=False, schema='iris')
    op.create_index(op.f('ix_iris_users_username'), 'users', ['username'], unique=True, schema='iris')
    op.create_table('service_group',
    sa.Column('service_id', sa.Integer(), nullable=False),
    sa.Column('group_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['iris.groups.id'], name='fk_service_group_group_id'),
    sa.ForeignKeyConstraint(['service_id'], ['iris.services.id'], name='fk_service_group_service_id'),
    sa.PrimaryKeyConstraint('service_id', 'group_id'),
    schema='iris'
    )
    op.create_table('user_group',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('group_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['group_id'], ['iris.groups.id'], name='fk_user_group_group_id'),
    sa.ForeignKeyConstraint(['user_id'], ['iris.users.id'], name='fk_user_group_user_id'),
    sa.PrimaryKeyConstraint('user_id', 'group_id'),
    schema='iris'
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('user_group', schema='iris')
    op.drop_table('service_group', schema='iris')
    op.drop_index(op.f('ix_iris_users_username'), table_name='users', schema='iris')
    op.drop_index(op.f('ix_iris_users_id'), table_name='users', schema='iris')
    op.drop_index(op.f('ix_iris_users_email'), table_name='users', schema='iris')
    op.drop_table('users', schema='iris')
    op.drop_index(op.f('ix_iris_services_name'), table_name='services', schema='iris')
    op.drop_index(op.f('ix_iris_services_id'), table_name='services', schema='iris')
    op.drop_table('services', schema='iris')
    op.drop_index(op.f('ix_iris_groups_name'), table_name='groups', schema='iris')
    op.drop_index(op.f('ix_iris_groups_id'), table_name='groups', schema='iris')
    op.drop_table('groups', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_user_id'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_service_name'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_search_index'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_id'), table_name='chat_history', schema='iris')
    op.drop_index(op.f('ix_iris_chat_history_conversation_id'), table_name='chat_history', schema='iris')
    op.drop_table('chat_history', schema='iris')
    # ### end Alembic commands ###

[CaselIT]

DEBUG [sqlalchemy.engine.Engine] Col ('nspname',)
DEBUG [sqlalchemy.engine.Engine] Row ('information_schema',)
DEBUG [sqlalchemy.engine.Engine] Row ('iris',)
DEBUG [sqlalchemy.engine.Engine] Row ('public',)
### INCLUDE_NAME: NAME: None TYPE: schema PARENT_NAMES: {} RETURNING False
### INCLUDE_NAME: NAME: public TYPE: schema PARENT_NAMES: {} RETURNING False
INFO  [sqlalchemy.engine.Engine] COMMIT

You still are modifing the default schema in the driver to iris. Please do a seach for everything instance of it, to ensure that's not modified

[piziakkodis]

Thanks Federico, it's not a driver or settings modification. More like a bad coincidence.
Or maybe a coincidence with user name, schema name and the aproach with redirecting the schema.

root cause of the problem:

Using the postgresql.conf default search path like :
`
search_path

"$user", public`

My user is named iris too.
So, the side effect of this is, that we have suddenly a search_path pointing to the iris application schema.

I've confirmed this with another database and another user.
The fault happens, as soon as user name == schema name.

Is there a possible alembic side fix?

Of course i could:
a) rename the user
b) change default search_path to not include $user
c) change the schema name to not equal the user name

Actually b will pose problems with temporary tables and the like, which will be created in $user schema space.
So only a or c ...

[zzzeek]

well it means that iris is in fact your default schema, so if that's staying, you'd do approach 1 again. otherwise yes you have to change the search path, which you can do client side. would be at least within the alembic scope and preferably in the whole SQLAlchemy application scope. creating temp tables from sqlalchemy isnt a common use case but you could always set a schema on those too, if you are going with the approach that models all have schema="iris" in them.

[piziakkodis]

Thanks,

i've changed username to avoid $user == schema name collission.

So in the end, a mixture of unlucky user/schema name and an uncommon sqlalchemy/alembic usage (not using public).

I'll mark it fixed above.
Geee ... what a experience ;)

[CaselIT]

Yes, pg really expect you to know how it behaves when using custom schemas and users