"""Initial schema. Revision ID: 001 Revises: Create Date: 2026-06-15 00:00:00.000000 """ from typing import Sequence, Union from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = "001" down_revision: Union[str, None] = None branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: # Users op.create_table( "users", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("username", sa.String(64), nullable=False), sa.Column("password_hash", sa.String(255), nullable=False), sa.Column("role", sa.String(32), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("last_login_at", sa.DateTime(timezone=True), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("username"), ) op.create_index("ix_users_username", "users", ["username"], unique=False) op.create_index("ix_users_role", "users", ["role"], unique=False) # Feeds op.create_table( "feeds", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("url", sa.String(2048), nullable=False), sa.Column("title", sa.String(512), nullable=True), sa.Column("description", sa.Text(), nullable=True), sa.Column("category", sa.String(128), nullable=True), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("fetch_interval_minutes", sa.Integer(), nullable=False), sa.Column("priority", sa.Integer(), nullable=False), sa.Column("parser_config", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("proxy_policy", sa.String(32), nullable=False), sa.Column("last_fetch_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_fetch_status", sa.String(32), nullable=True), sa.Column("last_error", sa.Text(), nullable=True), sa.Column("error_type", sa.String(64), nullable=True), sa.Column("success_count", sa.Integer(), nullable=False), sa.Column("fail_count", sa.Integer(), nullable=False), sa.Column("article_count", sa.Integer(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("url"), ) op.create_index("ix_feeds_url", "feeds", ["url"], unique=False) op.create_index("ix_feeds_is_active", "feeds", ["is_active"], unique=False) # Raw articles op.create_table( "raw_articles", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("feed_id", postgresql.UUID(as_uuid=True), nullable=False), sa.Column("external_id", sa.String(255), nullable=True), sa.Column("title", sa.String(1024), nullable=True), sa.Column("link", sa.String(2048), nullable=False), sa.Column("author", sa.String(256), nullable=True), sa.Column("published_at", sa.DateTime(timezone=True), nullable=True), sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False), sa.Column("content", sa.Text(), nullable=True), sa.Column("summary", sa.Text(), nullable=True), sa.Column("raw_html", sa.Text(), nullable=True), sa.Column("content_hash", sa.String(64), nullable=True), sa.Column("language", sa.String(16), nullable=True), sa.Column("status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["feed_id"], ["feeds.id"], ondelete="CASCADE"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_raw_articles_feed_id", "raw_articles", ["feed_id"], unique=False) op.create_index("ix_raw_articles_link", "raw_articles", ["link"], unique=False) op.create_index("ix_raw_articles_external_id", "raw_articles", ["external_id"], unique=False) op.create_index("ix_raw_articles_published_at", "raw_articles", ["published_at"], unique=False) op.create_index("ix_raw_articles_fetched_at", "raw_articles", ["fetched_at"], unique=False) op.create_index("ix_raw_articles_status", "raw_articles", ["status"], unique=False) # Duplicate groups (created first, FK to cleaned_articles added later) op.create_table( "duplicate_groups", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("representative_article_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("member_article_ids", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("similarity_matrix", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("brief_date", sa.String(10), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_duplicate_groups_brief_date", "duplicate_groups", ["brief_date"], unique=False) # Cleaned articles op.create_table( "cleaned_articles", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("raw_article_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("feed_id", postgresql.UUID(as_uuid=True), nullable=False), sa.Column("title", sa.String(1024), nullable=True), sa.Column("link", sa.String(2048), nullable=False), sa.Column("author", sa.String(256), nullable=True), sa.Column("feed_title", sa.String(512), nullable=True), sa.Column("feed_category", sa.String(128), nullable=True), sa.Column("published_at", sa.DateTime(timezone=True), nullable=True), sa.Column("fetched_at", sa.DateTime(timezone=True), nullable=False), sa.Column("content", sa.Text(), nullable=True), sa.Column("content_length", sa.Integer(), nullable=False), sa.Column("original_summary", sa.Text(), nullable=True), sa.Column("ai_summary", sa.Text(), nullable=True), sa.Column("category", sa.String(128), nullable=True), sa.Column("tags", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("heat_score", sa.Float(), nullable=False), sa.Column("importance_score", sa.Float(), nullable=False), sa.Column("duplication_score", sa.Float(), nullable=False), sa.Column("composite_score", sa.Float(), nullable=False), sa.Column("duplicate_group_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("is_representative", sa.Boolean(), nullable=False), sa.Column("reference_links", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("processing_status", sa.String(32), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["raw_article_id"], ["raw_articles.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["feed_id"], ["feeds.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["duplicate_group_id"], ["duplicate_groups.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_cleaned_articles_raw_article_id", "cleaned_articles", ["raw_article_id"], unique=False) op.create_index("ix_cleaned_articles_feed_id", "cleaned_articles", ["feed_id"], unique=False) op.create_index("ix_cleaned_articles_link", "cleaned_articles", ["link"], unique=False) op.create_index("ix_cleaned_articles_title", "cleaned_articles", ["title"], unique=False) op.create_index("ix_cleaned_articles_published_at", "cleaned_articles", ["published_at"], unique=False) op.create_index("ix_cleaned_articles_fetched_at", "cleaned_articles", ["fetched_at"], unique=False) op.create_index("ix_cleaned_articles_category", "cleaned_articles", ["category"], unique=False) op.create_index("ix_cleaned_articles_duplicate_group_id", "cleaned_articles", ["duplicate_group_id"], unique=False) op.create_index("ix_cleaned_articles_is_representative", "cleaned_articles", ["is_representative"], unique=False) op.create_index("ix_cleaned_articles_processing_status", "cleaned_articles", ["processing_status"], unique=False) op.create_index("ix_cleaned_articles_tags", "cleaned_articles", ["tags"], postgresql_using="gin") op.create_index("ix_cleaned_articles_reference_links", "cleaned_articles", ["reference_links"], postgresql_using="gin") # Add FK from duplicate_groups to cleaned_articles (circular dependency resolution) op.create_foreign_key( "fk_duplicate_groups_representative_article_id", "duplicate_groups", "cleaned_articles", ["representative_article_id"], ["id"], ondelete="SET NULL", ) # Article references op.create_table( "article_references", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("source_article_id", postgresql.UUID(as_uuid=True), nullable=False), sa.Column("referenced_article_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("reference_type", sa.String(64), nullable=False), sa.Column("reference_link", sa.String(2048), nullable=True), sa.Column("reference_title", sa.String(1024), nullable=True), sa.Column("similarity", sa.Float(), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["source_article_id"], ["cleaned_articles.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["referenced_article_id"], ["cleaned_articles.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_article_references_source_article_id", "article_references", ["source_article_id"], unique=False) op.create_index("ix_article_references_referenced_article_id", "article_references", ["referenced_article_id"], unique=False) op.create_index("ix_article_references_reference_type", "article_references", ["reference_type"], unique=False) # Skills op.create_table( "skills", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("name", sa.String(128), nullable=False), sa.Column("slug", sa.String(128), nullable=False), sa.Column("description", sa.Text(), nullable=True), sa.Column("type", sa.String(32), nullable=False), sa.Column("version", sa.Integer(), nullable=False), sa.Column("is_default", sa.Boolean(), nullable=False), sa.Column("system_prompt", sa.Text(), nullable=False), sa.Column("output_schema", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("tools", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("input_schema", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("example_inputs", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("created_by", sa.String(64), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("slug"), ) op.create_index("ix_skills_slug", "skills", ["slug"], unique=False) op.create_index("ix_skills_type", "skills", ["type"], unique=False) # AI provider configs op.create_table( "ai_provider_configs", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("name", sa.String(128), nullable=False), sa.Column("provider", sa.String(64), nullable=False), sa.Column("base_url", sa.String(512), nullable=True), sa.Column("api_key_encrypted", sa.Text(), nullable=True), sa.Column("default_model", sa.String(128), nullable=True), sa.Column("timeout", sa.Integer(), nullable=False), sa.Column("max_retries", sa.Integer(), nullable=False), sa.Column("rate_limit_rpm", sa.Integer(), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_ai_provider_configs_provider", "ai_provider_configs", ["provider"], unique=False) # AI task configs op.create_table( "ai_task_configs", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("task_type", sa.String(64), nullable=False), sa.Column("name", sa.String(128), nullable=False), sa.Column("provider_config_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("model", sa.String(128), nullable=False), sa.Column("skill_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("temperature", sa.Float(), nullable=False), sa.Column("max_tokens", sa.Integer(), nullable=True), sa.Column("top_p", sa.Float(), nullable=False), sa.Column("system_prompt_override", sa.Text(), nullable=True), sa.Column("fallback_config_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("enabled", sa.Boolean(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["provider_config_id"], ["ai_provider_configs.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["skill_id"], ["skills.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["fallback_config_id"], ["ai_task_configs.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_ai_task_configs_task_type", "ai_task_configs", ["task_type"], unique=False) # Output tasks op.create_table( "output_tasks", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("name", sa.String(128), nullable=False), sa.Column("task_type", sa.String(64), nullable=False), sa.Column("skill_id", postgresql.UUID(as_uuid=True), nullable=False), sa.Column("schedule", sa.String(128), nullable=True), sa.Column("filter_config", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("output_config", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False), sa.Column("last_run_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_output_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["skill_id"], ["skills.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["last_output_id"], ["outputs.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) # Outputs op.create_table( "outputs", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("output_task_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("content", sa.Text(), nullable=True), sa.Column("content_html", sa.Text(), nullable=True), sa.Column("references", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("metadata", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["output_task_id"], ["output_tasks.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_outputs_output_task_id", "outputs", ["output_task_id"], unique=False) # Chat sessions op.create_table( "chat_sessions", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("title", sa.String(256), nullable=True), sa.Column("skill_id", postgresql.UUID(as_uuid=True), nullable=True), sa.Column("context_window", sa.Integer(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["skill_id"], ["skills.id"], ondelete="SET NULL"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_chat_sessions_user_id", "chat_sessions", ["user_id"], unique=False) # Chat messages op.create_table( "chat_messages", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("session_id", postgresql.UUID(as_uuid=True), nullable=False), sa.Column("role", sa.String(32), nullable=False), sa.Column("content", sa.Text(), nullable=True), sa.Column("tool_calls", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("tool_results", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("references", postgresql.JSONB(astext_type=sa.Text()), nullable=False), sa.Column("token_usage", postgresql.JSONB(astext_type=sa.Text()), nullable=True), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.ForeignKeyConstraint(["session_id"], ["chat_sessions.id"], ondelete="CASCADE"), sa.PrimaryKeyConstraint("id"), ) op.create_index("ix_chat_messages_session_id", "chat_messages", ["session_id"], unique=False) op.create_index("ix_chat_messages_role", "chat_messages", ["role"], unique=False) # Locks op.create_table( "locks", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("lock_name", sa.String(128), nullable=False), sa.Column("owner_id", sa.String(128), nullable=True), sa.Column("acquired_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("lock_name"), ) # App settings op.create_table( "app_settings", sa.Column("id", postgresql.UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), nullable=False), sa.Column("key", sa.String(128), nullable=False), sa.Column("value", sa.Text(), nullable=False), sa.Column("description", sa.Text(), nullable=True), sa.Column("is_sensitive", sa.Boolean(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.text("now()"), nullable=False), sa.PrimaryKeyConstraint("id"), sa.UniqueConstraint("key"), ) op.create_index("ix_app_settings_key", "app_settings", ["key"], unique=False) def downgrade() -> None: op.drop_table("app_settings") op.drop_table("locks") op.drop_table("chat_messages") op.drop_table("chat_sessions") op.drop_table("outputs") op.drop_table("output_tasks") op.drop_table("ai_task_configs") op.drop_table("ai_provider_configs") op.drop_table("skills") op.drop_table("article_references") op.drop_constraint("fk_duplicate_groups_representative_article_id", "duplicate_groups", type_="foreignkey") op.drop_table("cleaned_articles") op.drop_table("duplicate_groups") op.drop_table("raw_articles") op.drop_table("feeds") op.drop_table("users")