BidRadar Development Progress

████████████░░░░░░░░ 12/60

Current phase: Core Domain Implementation
Current milestone: Implement the multi-tenant identity and organization data model
Previous article: Part 11 — Configuring PostgreSQL, SQLAlchemy, and Alembic
Next article: Part 13 — Creating the Initial Core Database Migration
Primary deliverables: Core SQLAlchemy models, relationships, constraints, and model tests
Application functionality added: Persistent organization, user, membership, role, invitation, and audit structures

Introduction

In Part 11, we configured the database foundation for BidRadar.

We created:

• A PostgreSQL connection
• A SQLAlchemy 2.0 engine
• Request-scoped database sessions
• A shared declarative base
• UUID and timestamp mixins
• Alembic migration infrastructure
• Database readiness checks
• Transaction-management conventions

The application can now connect to PostgreSQL, but it does not yet understand the business objects that make BidRadar a multi-tenant SaaS platform.

Before we can import a tender, upload a document, extract a requirement, or generate a proposal, the platform must answer several foundational questions:

• Who is the user?
• Which organization does the user belong to?
• Which organization is currently active?
• What role does the user hold?
• Which actions may the user perform?
• Is the membership active, invited, suspended, or removed?
• Who invited the user?
• When was access granted?
• Which business event changed the system?
• Which organization owns a record?

These are not secondary administrative details.

They determine the ownership and authorization model for the entire product.

Every future opportunity, tender document, requirement, proposal, task, knowledge item, compliance finding, and AI operation will belong to an organization.

Every protected action will be performed by a user acting through an organization membership.

Therefore, the first business models should establish:

Identity, tenancy, access, and traceability.

In this article, we will design and implement the first production-oriented SQLAlchemy models for BidRadar.

We will create:

• Organizations
• Users
• Memberships
• Roles
• Permissions
• Role-permission assignments
• Invitations
• Audit events

We will also establish conventions that every future domain model must follow.

Objectives

After completing this article, we should have:

• A clear multi-tenant ownership model
• A persistent organization model
• A persistent user model
• A many-to-many user-to-organization membership model
• Role and permission models
• Role-permission associations
• Membership status and lifecycle fields
• Organization invitation records
• Audit-event records
• Shared model conventions
• UUID primary keys
• Timestamp fields
• Foreign-key rules
• Unique constraints
• Indexes
• Enum strategies
• JSON metadata conventions
• Secure user-credential fields
• SQLAlchemy relationships
• Model-registration infrastructure
• Initial model tests
• A Google AI Studio implementation prompt
• A detailed validation checklist

Why These Models Come First

A common mistake is to begin with the most visible feature.

For BidRadar, that might mean immediately creating:

Opportunity

or:

Proposal

However, an opportunity without organization ownership creates serious unanswered questions.

For example:

• Which customer owns the opportunity?
• Can users from another customer see it?
• Who may update it?
• Who may archive it?
• Who may generate an AI analysis?
• Who approved the Bid decision?
• Which organization’s knowledge base may support the proposal?

Every future record must be attached to a clear tenant boundary.

The correct dependency order is:

Organization
      ↓
Membership and access
      ↓
Business resources
      ↓
Workflows and approvals
      ↓
AI operations

This means the core organizational model must exist before tender-domain entities.

Core Domain Scope

The first core model set will include the following entities:

Organization
User
Membership
Role
Permission
RolePermission
OrganizationInvitation
AuditEvent

These entities support four essential concerns.

Identity

Who is using BidRadar?

Tenancy

Which customer organization owns the data?

Authorization

What may the user do within that organization?

Traceability

Which actor performed a significant action?

High-Level Entity Relationship Model

The core relationships can be summarized as:

User
  │
  │ many
  ▼
Membership
  ▲
  │ many
  │
Organization

A membership connects one user to one organization.

The membership receives a role:

Membership
    │
    ▼
Role
    │
    ▼
RolePermission
    │
    ▼
Permission

Invitations exist before a membership is accepted:

Organization
    │
    ▼
OrganizationInvitation

Audit events record important actions:

User
   \
    \
     AuditEvent ───── Organization
    /
Membership

The exact optionality of these relationships matters and will be defined later.

Multi-Tenancy Strategy

BidRadar will use a shared-database, shared-schema multi-tenant model.

This means:

• All organizations use the same PostgreSQL database.
• All organizations use the same tables.
• Tenant-owned rows include an organization_id.
• Application queries must always filter by authorized organization context.
• Database constraints reinforce ownership where practical.
• Authorization must never depend only on frontend state.

Example:

opportunities
├── id
├── organization_id
├── title
└── ...

This strategy is appropriate for the first BidRadar architecture because it provides:

• Efficient infrastructure use
• Simpler migrations
• Easier reporting
• Easier development
• Clear application-level tenancy controls

It also creates an important security obligation:

Every tenant-owned query must be scoped to the active organization.

A missing organization filter can become a cross-tenant data exposure.

For that reason, tenancy must be designed as a first-class domain concept.

Why Membership Is a Separate Entity

A simplistic user model might contain:

user.organization_id
user.role

That approach assumes a user can belong to only one organization.

BidRadar should support users who may eventually work across multiple organizations.

Examples include:

• Consultants assisting several legal entities
• Holding companies with separate operating companies
• External proposal reviewers
• Partner organizations
• Administrators supporting multiple customer environments

A separate membership table allows:

One user
      ↓
Multiple organization memberships
      ↓
Different role per organization

For example:

User: alex@example.com
Organization A:
Role = Bid Manager
Organization B:
Role = External Reviewer

This cannot be represented cleanly by storing one role directly on the user.

Core Modeling Principles

Before creating individual models, define the rules that all models must follow.

Principle 1 — Use UUID Primary Keys

Every business entity should use a UUID primary key.

Principle 2 — Use Explicit Table Names

Do not rely on automatically derived table names.

Principle 3 — Use Database Constraints

Application validation is important, but PostgreSQL should also protect critical invariants.

Principle 4 — Use Typed SQLAlchemy 2.0 Mappings

Use:

Mapped
mapped_column
relationship

Avoid legacy untyped declarative mappings.

Principle 5 — Separate Authentication Identity from Membership

The user represents an identity.

The membership represents access to an organization.

Principle 6 — Preserve Historical Context

Do not remove every record when access changes.

Membership status, invitation status, and audit records should preserve history.

Principle 7 — Avoid Sensitive Data in Generic JSON

JSON metadata should not become a dumping ground for secrets, passwords, or unrestricted personal information.

Principle 8 — Design for Soft Lifecycle Changes

Many enterprise records should be suspended, archived, or deactivated rather than immediately deleted.

Principle 9 — Use UTC Timestamps

Store timestamps in a timezone-aware UTC-compatible form.

Principle 10 — Keep Authorization Explicit

A role name alone should not be treated as an immutable application rule.

Permissions should be represented explicitly.

Suggested Core Model Package

Create:

backend/
└── app/
    ├── database/
    │   ├── base.py
    │   ├── mixins.py
    │   └── model_registry.py
    │
    └── models/
        ├── __init__.py
        ├── enums.py
        ├── organization.py
        ├── user.py
        ├── membership.py
        ├── role.py
        ├── permission.py
        ├── invitation.py
        └── audit.py

An alternative structure could organize models by domain modules.

For the initial core identity layer, a centralized app/models package is acceptable.

As BidRadar expands, domain-specific models may move into modules such as:

app/opportunities/models.py
app/proposals/models.py
app/knowledge/models.py

The important requirement is that all models are registered with the shared SQLAlchemy metadata before Alembic autogeneration runs.

Reviewing the Shared Base and Mixins

Part 11 introduced reusable database foundations.

A representative base structure may look like:

from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase
from app.database.naming import NAMING_CONVENTION
class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)

The UUID and timestamp mixins may resemble:

from datetime import datetime
from uuid import UUID, uuid4
from sqlalchemy import DateTime, func
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column
class UUIDPrimaryKeyMixin:
    id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        primary_key=True,
        default=uuid4,
    )
class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
    )

These mixins reduce duplication.

However, they should not be used mechanically.

Some event records may be immutable and need only:

created_at

An audit event, for example, may not need updated_at because it should not be edited after creation.

Database Naming Convention

A consistent naming convention may use:

NAMING_CONVENTION = {
    "ix": "ix_%(table_name)s_%(column_0_name)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": (
        "fk_%(table_name)s_%(column_0_name)s_"
        "%(referred_table_name)s"
    ),
    "pk": "pk_%(table_name)s",
}

This produces readable names such as:

pk_organizations
uq_users_email_normalized
fk_memberships_user_id_users
ix_audit_events_organization_id

Readable constraint names make migration failures easier to diagnose.

Enum Strategy

The core models require several lifecycle fields.

Possible approaches include:

• PostgreSQL native enums
• String columns validated by Python enums
• Lookup tables

Each approach has tradeoffs.

PostgreSQL Native Enum

Advantages:

• Strong database enforcement
• Compact representation
• Clear allowed values

Disadvantages:

• Enum changes require careful migrations
• Renaming values can be awkward
• Rollbacks can be complicated

String Column With Python Enum

Advantages:

• Easier migrations
• More portable
• Easier to evolve

Disadvantages:

• Requires a check constraint or application validation for strong enforcement

For BidRadar, a practical initial strategy is:

• Define Python string enums.
• Persist their string values.
• Use SQLAlchemy enum support with explicit names.
• Document all enum migrations carefully.

Create:

app/models/enums.py

Core Enums

Suggested enums include:

from enum import StrEnum
class OrganizationStatus(StrEnum):
    ACTIVE = "active"
    SUSPENDED = "suspended"
    ARCHIVED = "archived"
class UserStatus(StrEnum):
    ACTIVE = "active"
    SUSPENDED = "suspended"
    DISABLED = "disabled"
class MembershipStatus(StrEnum):
    INVITED = "invited"
    ACTIVE = "active"
    SUSPENDED = "suspended"
    REMOVED = "removed"
class InvitationStatus(StrEnum):
    PENDING = "pending"
    ACCEPTED = "accepted"
    EXPIRED = "expired"
    REVOKED = "revoked"
class AuditActorType(StrEnum):
    USER = "user"
    SYSTEM = "system"
    AI = "ai"
    INTEGRATION = "integration"

Use lowercase persisted values because they are predictable in APIs and database queries.

Organization Model

The organization is the primary tenant.

Every organization-owned domain record will eventually reference it.

Create:

app/models/organization.py

Suggested fields:

id
name
slug
legal_name
status
default_currency
timezone
country_code
website
settings
created_at
updated_at

Organization Field Design

id

UUID primary key.

name

Human-readable organization name.

Example:

Northstar Cloud Consulting

slug

Stable URL-friendly identifier.

Example:

northstar-cloud-consulting

The slug should be unique across active organizations unless the product later introduces custom domain rules.

legal_name

Optional registered legal name.

This may differ from the trading name.

status

Organization lifecycle state.

Possible values:

active
suspended
archived

default_currency

Suggested default:

USD

Store a three-character ISO currency code.

The organization may use the currency for:

• Opportunity values
• AI budgets
• Subscription estimates
• Reporting

timezone

Suggested default:

UTC

This supports deadline presentation and organization-level scheduling.

country_code

Two-character country code where known.

website

Optional public website.

settings

JSONB object for carefully controlled organization preferences.

Examples:

• Date display preference
• Default tender regions
• Feature configuration
• Workflow preferences

Do not store secrets in this field.

Organization Model Example

from typing import TYPE_CHECKING, Any
from sqlalchemy import Enum, String
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import TimestampMixin, UUIDPrimaryKeyMixin
from app.models.enums import OrganizationStatus
if TYPE_CHECKING:
    from app.models.audit import AuditEvent
    from app.models.invitation import OrganizationInvitation
    from app.models.membership import Membership
    from app.models.role import Role
class Organization(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "organizations"
    name: Mapped[str] = mapped_column(
        String(200),
        nullable=False,
    )
    slug: Mapped[str] = mapped_column(
        String(120),
        nullable=False,
        unique=True,
        index=True,
    )
    legal_name: Mapped[str | None] = mapped_column(
        String(250),
        nullable=True,
    )
    status: Mapped[OrganizationStatus] = mapped_column(
        Enum(
            OrganizationStatus,
            name="organization_status",
            values_callable=lambda enum: [
                item.value for item in enum
            ],
        ),
        nullable=False,
        default=OrganizationStatus.ACTIVE,
        server_default=OrganizationStatus.ACTIVE.value,
        index=True,
    )
    default_currency: Mapped[str] = mapped_column(
        String(3),
        nullable=False,
        default="USD",
        server_default="USD",
    )
    timezone: Mapped[str] = mapped_column(
        String(64),
        nullable=False,
        default="UTC",
        server_default="UTC",
    )
    country_code: Mapped[str | None] = mapped_column(
        String(2),
        nullable=True,
    )
    website: Mapped[str | None] = mapped_column(
        String(500),
        nullable=True,
    )
    settings: Mapped[dict[str, Any]] = mapped_column(
        JSONB,
        nullable=False,
        default=dict,
        server_default="{}",
    )
    memberships: Mapped[list["Membership"]] = relationship(
        back_populates="organization",
        cascade="all, delete-orphan",
    )
    roles: Mapped[list["Role"]] = relationship(
        back_populates="organization",
        cascade="all, delete-orphan",
    )
    invitations: Mapped[list["OrganizationInvitation"]] = relationship(
        back_populates="organization",
        cascade="all, delete-orphan",
    )
    audit_events: Mapped[list["AuditEvent"]] = relationship(
        back_populates="organization",
    )

Should Organizations Be Hard Deleted?

In production, organization deletion is rarely simple.

An organization may own:

• Tender records
• Proposal history
• Audit logs
• Knowledge documents
• User activity
• Billing records

Therefore, the normal lifecycle should use:

active
suspended
archived

rather than immediate deletion.

Hard deletion may later be implemented as a separate controlled retention workflow.

This is especially important where contractual or regulatory retention requirements apply.

User Model

The user represents a global identity.

It should not contain one organization ID or one organization role.

Create:

app/models/user.py

Suggested fields:

id
email
email_normalized
display_name
first_name
last_name
password_hash
status
email_verified_at
last_login_at
created_at
updated_at

Email Normalization

Email addresses should be normalized before uniqueness checks.

For example:

Alex.Example@Company.com

may be stored for display, while:

alex.example@company.com

is used for lookup and uniqueness.

The application should not make provider-specific assumptions such as removing dots or plus aliases.

A safe normalization strategy generally includes:

• Trimming whitespace
• Lowercasing
• Validating structure

The normalized value should have a unique constraint.

Password Storage

Never store:

password
plain_password
encrypted_password

The database should store only:

password_hash

Hashing will be implemented during authentication.

The model should be ready for:

• Password authentication
• Single sign-on
• Passwordless login
• External identity providers

Therefore, password_hash may be nullable if external authentication is eventually supported.

User Model Example

from datetime import datetime
from typing import TYPE_CHECKING
from sqlalchemy import DateTime, Enum, String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import TimestampMixin, UUIDPrimaryKeyMixin
from app.models.enums import UserStatus
if TYPE_CHECKING:
    from app.models.audit import AuditEvent
    from app.models.membership import Membership
class User(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "users"
    email: Mapped[str] = mapped_column(
        String(320),
        nullable=False,
    )
    email_normalized: Mapped[str] = mapped_column(
        String(320),
        nullable=False,
        unique=True,
        index=True,
    )
    display_name: Mapped[str] = mapped_column(
        String(200),
        nullable=False,
    )
    first_name: Mapped[str | None] = mapped_column(
        String(100),
        nullable=True,
    )
    last_name: Mapped[str | None] = mapped_column(
        String(100),
        nullable=True,
    )
    password_hash: Mapped[str | None] = mapped_column(
        String(255),
        nullable=True,
    )
    status: Mapped[UserStatus] = mapped_column(
        Enum(
            UserStatus,
            name="user_status",
            values_callable=lambda enum: [
                item.value for item in enum
            ],
        ),
        nullable=False,
        default=UserStatus.ACTIVE,
        server_default=UserStatus.ACTIVE.value,
        index=True,
    )
    email_verified_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    last_login_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    memberships: Mapped[list["Membership"]] = relationship(
        back_populates="user",
    )
    audit_events: Mapped[list["AuditEvent"]] = relationship(
        back_populates="actor_user",
        foreign_keys="AuditEvent.actor_user_id",
    )

User Status Versus Membership Status

These states must remain separate.

User Status

Controls the global identity.

Example:

active
suspended
disabled

If a user is globally disabled, the user should not access any organization.

Membership Status

Controls access to one organization.

Example:

invited
active
suspended
removed

A user may be:

Globally active

but:

Suspended in Organization A
Active in Organization B

This separation is essential for a multi-tenant platform.

Role Model

A role groups permissions.

Examples include:

Organization Owner
Administrator
Bid Manager
Proposal Manager
Contributor
Reviewer
Viewer

Create:

app/models/role.py

The role may be:

• Organization-specific
• System-defined
• Customizable

A practical initial model supports both predefined and custom organization roles.

Suggested fields:

id
organization_id
name
slug
description
is_system
is_default
created_at
updated_at

Organization-Specific Roles

Each organization may require different access structures.

For example:

Organization A:
- Bid Director
- Proposal Lead
- Technical Reviewer
Organization B:
- Capture Manager
- Writer
- Executive Approver

Therefore, roles should generally belong to an organization.

System templates can later be copied into new organizations during onboarding.

Role Model Example

from typing import TYPE_CHECKING
from sqlalchemy import Boolean, ForeignKey, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import TimestampMixin, UUIDPrimaryKeyMixin
if TYPE_CHECKING:
    from app.models.membership import Membership
    from app.models.organization import Organization
    from app.models.permission import RolePermission
class Role(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "roles"
    __table_args__ = (
        UniqueConstraint(
            "organization_id",
            "slug",
            name="uq_roles_organization_id_slug",
        ),
    )
    organization_id: Mapped[object] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "organizations.id",
            ondelete="CASCADE",
        ),
        nullable=False,
        index=True,
    )
    name: Mapped[str] = mapped_column(
        String(120),
        nullable=False,
    )
    slug: Mapped[str] = mapped_column(
        String(120),
        nullable=False,
    )
    description: Mapped[str | None] = mapped_column(
        String(500),
        nullable=True,
    )
    is_system: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=False,
        server_default="false",
    )
    is_default: Mapped[bool] = mapped_column(
        Boolean,
        nullable=False,
        default=False,
        server_default="false",
    )
    organization: Mapped["Organization"] = relationship(
        back_populates="roles",
    )
    memberships: Mapped[list["Membership"]] = relationship(
        back_populates="role",
    )
    role_permissions: Mapped[list["RolePermission"]] = relationship(
        back_populates="role",
        cascade="all, delete-orphan",
    )

The organization_id type should be annotated as UUID rather than object in the final implementation.

For example:

from uuid import UUID

then:

organization_id: Mapped[UUID]

Strong typing should be applied consistently.

Permission Model

Permissions represent specific capabilities.

Examples:

opportunities.read
opportunities.create
opportunities.update
opportunities.archive
documents.upload
documents.read
documents.delete
requirements.review
requirements.approve
proposals.create
proposals.update
proposals.approve
proposals.export
knowledge.read
knowledge.manage
members.invite
members.manage
audit.read

Create:

app/models/permission.py

Suggested fields:

id
code
name
description
category
created_at

Permissions are platform-wide definitions.

Unlike roles, they do not normally belong to one organization.

Permission Naming Convention

Use:

resource.action

Examples:

opportunities.read
proposals.approve
members.invite
audit.read

Avoid ambiguous codes such as:

edit
admin
full_access

Explicit permission codes are easier to:

• Test
• Audit
• Document
• Map to API operations

Permission Model Example

from typing import TYPE_CHECKING
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import UUIDPrimaryKeyMixin
if TYPE_CHECKING:
    from app.models.permission import RolePermission
class Permission(
    UUIDPrimaryKeyMixin,
    Base,
):
    __tablename__ = "permissions"
    code: Mapped[str] = mapped_column(
        String(160),
        nullable=False,
        unique=True,
        index=True,
    )
    name: Mapped[str] = mapped_column(
        String(160),
        nullable=False,
    )
    description: Mapped[str | None] = mapped_column(
        String(500),
        nullable=True,
    )
    category: Mapped[str] = mapped_column(
        String(80),
        nullable=False,
        index=True,
    )
    role_permissions: Mapped[list["RolePermission"]] = relationship(
        back_populates="permission",
        cascade="all, delete-orphan",
    )

A permission may be treated as configuration data rather than user-created data.

Later migrations or seed scripts can create the approved permission catalogue.

Role-Permission Association

A role may have many permissions.

A permission may belong to many roles.

This requires an association table.

Create a mapped association entity rather than a minimal plain table because it may later need:

• Assignment timestamp
• Assignment actor
• Scope metadata

Suggested initial fields:

role_id
permission_id
created_at

RolePermission Example

from datetime import datetime
from typing import TYPE_CHECKING
from uuid import UUID
from sqlalchemy import DateTime, ForeignKey, PrimaryKeyConstraint, func
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
if TYPE_CHECKING:
    from app.models.permission import Permission
    from app.models.role import Role
class RolePermission(Base):
    __tablename__ = "role_permissions"
    __table_args__ = (
        PrimaryKeyConstraint(
            "role_id",
            "permission_id",
            name="pk_role_permissions",
        ),
    )
    role_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "roles.id",
            ondelete="CASCADE",
        ),
        nullable=False,
    )
    permission_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "permissions.id",
            ondelete="CASCADE",
        ),
        nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )
    role: Mapped["Role"] = relationship(
        back_populates="role_permissions",
    )
    permission: Mapped["Permission"] = relationship(
        back_populates="role_permissions",
    )

The composite primary key prevents duplicate role-permission assignments.

Membership Model

The membership is one of the most important models in BidRadar.

It connects:

• A user
• An organization
• A role
• A membership lifecycle

Create:

app/models/membership.py

Suggested fields:

id
organization_id
user_id
role_id
status
job_title
invited_by_user_id
joined_at
suspended_at
removed_at
created_at
updated_at

Membership Invariants

The database should enforce:

• One membership per user per organization
• A valid organization
• A valid user
• A valid role
• The assigned role should belong to the same organization

The first three can be enforced directly with ordinary constraints.

The final rule is more subtle.

A foreign key from membership.role_id to roles.id proves that the role exists, but not that:

membership.organization_id

matches:

role.organization_id

This cross-column ownership rule must be handled carefully.

Possible solutions include:

1. Service-layer validation
2. Composite foreign key
3. Database trigger
4. Role assignment association scoped to organization

For the initial implementation, service-layer validation plus tests may be acceptable.

For stronger database enforcement, roles can expose a composite unique constraint on:

id, organization_id

and memberships can use a composite foreign key referencing both fields.

That design is more complex but significantly strengthens tenant integrity.

Because tenant boundaries are security-sensitive, BidRadar should strongly consider the composite foreign-key approach.

Membership Model Example

from datetime import datetime
from typing import TYPE_CHECKING
from uuid import UUID
from sqlalchemy import (
    DateTime,
    Enum,
    ForeignKey,
    String,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import TimestampMixin, UUIDPrimaryKeyMixin
from app.models.enums import MembershipStatus
if TYPE_CHECKING:
    from app.models.organization import Organization
    from app.models.role import Role
    from app.models.user import User
class Membership(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "memberships"
    __table_args__ = (
        UniqueConstraint(
            "organization_id",
            "user_id",
            name="uq_memberships_organization_id_user_id",
        ),
    )
    organization_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "organizations.id",
            ondelete="CASCADE",
        ),
        nullable=False,
        index=True,
    )
    user_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "users.id",
            ondelete="CASCADE",
        ),
        nullable=False,
        index=True,
    )
    role_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "roles.id",
            ondelete="RESTRICT",
        ),
        nullable=False,
        index=True,
    )
    status: Mapped[MembershipStatus] = mapped_column(
        Enum(
            MembershipStatus,
            name="membership_status",
            values_callable=lambda enum: [
                item.value for item in enum
            ],
        ),
        nullable=False,
        default=MembershipStatus.INVITED,
        server_default=MembershipStatus.INVITED.value,
        index=True,
    )
    job_title: Mapped[str | None] = mapped_column(
        String(160),
        nullable=True,
    )
    invited_by_user_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "users.id",
            ondelete="SET NULL",
        ),
        nullable=True,
    )
    joined_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    suspended_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    removed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    organization: Mapped["Organization"] = relationship(
        back_populates="memberships",
    )
    user: Mapped["User"] = relationship(
        back_populates="memberships",
        foreign_keys=[user_id],
    )
    role: Mapped["Role"] = relationship(
        back_populates="memberships",
    )
    invited_by_user: Mapped["User | None"] = relationship(
        foreign_keys=[invited_by_user_id],
    )

Membership Deletion Behavior

The foreign keys above use:

CASCADE

for organization and user deletion.

This is technically consistent for test environments, but production deletion policy requires further review.

If users or organizations should remain historically traceable, alternatives include:

RESTRICT
SET NULL
soft deletion
anonymization

The correct rule depends on:

• Retention requirements
• Audit policy
• Privacy obligations
• Account-deletion behavior

Do not accept cascade rules without reviewing their historical impact.

Organization Invitation Model

An invitation exists before a membership becomes active.

Create:

app/models/invitation.py

Suggested fields:

id
organization_id
email
email_normalized
role_id
status
token_hash
invited_by_user_id
expires_at
accepted_at
revoked_at
created_at
updated_at

Invitation Token Security

Never store the raw invitation token.

The application should:

1. Generate a cryptographically secure token.
2. Send the raw token to the invitee.
3. Store only a hash.
4. Hash the presented token during acceptance.
5. Compare the hashes securely.

The database should contain:

token_hash

not:

token

This limits damage if the invitation table is exposed.

Invitation Model Example

from datetime import datetime
from typing import TYPE_CHECKING
from uuid import UUID
from sqlalchemy import DateTime, Enum, ForeignKey, String
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import TimestampMixin, UUIDPrimaryKeyMixin
from app.models.enums import InvitationStatus
if TYPE_CHECKING:
    from app.models.organization import Organization
    from app.models.role import Role
    from app.models.user import User
class OrganizationInvitation(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "organization_invitations"
    organization_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "organizations.id",
            ondelete="CASCADE",
        ),
        nullable=False,
        index=True,
    )
    email: Mapped[str] = mapped_column(
        String(320),
        nullable=False,
    )
    email_normalized: Mapped[str] = mapped_column(
        String(320),
        nullable=False,
        index=True,
    )
    role_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "roles.id",
            ondelete="RESTRICT",
        ),
        nullable=False,
    )
    status: Mapped[InvitationStatus] = mapped_column(
        Enum(
            InvitationStatus,
            name="invitation_status",
            values_callable=lambda enum: [
                item.value for item in enum
            ],
        ),
        nullable=False,
        default=InvitationStatus.PENDING,
        server_default=InvitationStatus.PENDING.value,
        index=True,
    )
    token_hash: Mapped[str] = mapped_column(
        String(255),
        nullable=False,
        unique=True,
    )
    invited_by_user_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "users.id",
            ondelete="SET NULL",
        ),
        nullable=True,
    )
    expires_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        index=True,
    )
    accepted_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    revoked_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    organization: Mapped["Organization"] = relationship(
        back_populates="invitations",
    )
    role: Mapped["Role"] = relationship()
    invited_by_user: Mapped["User | None"] = relationship()

Preventing Duplicate Pending Invitations

The product should prevent multiple active invitations for the same email and organization.

A normal unique constraint across:

organization_id
email_normalized

would also block historical invitations after they expire or are revoked.

PostgreSQL supports a partial unique index.

Conceptually:

CREATE UNIQUE INDEX ...
ON organization_invitations (
    organization_id,
    email_normalized
)
WHERE status = 'pending';

This allows:

• One pending invitation
• Historical accepted invitations
• Historical expired invitations
• Historical revoked invitations

Partial indexes are powerful but must be represented carefully in Alembic.

Audit Event Model

BidRadar needs durable records of important business actions.

Examples include:

• User invited
• Membership activated
• Role changed
• Opportunity created
• Tender document uploaded
• Requirement approved
• Bid decision recorded
• Proposal approved
• Export generated
• AI analysis completed
• Security setting changed

Create:

app/models/audit.py

Suggested fields:

id
organization_id
actor_type
actor_user_id
actor_membership_id
action
resource_type
resource_id
request_id
ip_address
user_agent
metadata
created_at

Audit Events Should Be Append-Only

Audit events should generally not be updated.

They represent historical facts.

Therefore, the model may use:

• UUID primary key
• created_at
• No updated_at

Application services should create new audit events rather than edit existing events.

Database-level controls may later strengthen immutability.

Actor Types

Not every action is performed directly by a user.

Possible actor types include:

user
system
ai
integration

Examples:

user:
Proposal manager approves a section.
system:
Scheduled invitation-expiration job updates an invitation.
ai:
Requirement-extraction pipeline produces findings.
integration:
External procurement connector imports an opportunity.

The actor type provides context when actor_user_id is absent.

Audit Metadata

JSONB metadata may contain structured information such as:

{
  "previous_role_id": "uuid",
  "new_role_id": "uuid",
  "reason": "Promotion to proposal manager"
}

Do not place complete confidential documents or unrestricted request bodies in audit metadata.

Audit logs should be useful without becoming a secondary data leak.

Audit Event Example

from datetime import datetime
from typing import TYPE_CHECKING, Any
from uuid import UUID
from sqlalchemy import DateTime, Enum, ForeignKey, String, func
from sqlalchemy.dialects.postgresql import INET, JSONB
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database.base import Base
from app.database.mixins import UUIDPrimaryKeyMixin
from app.models.enums import AuditActorType
if TYPE_CHECKING:
    from app.models.membership import Membership
    from app.models.organization import Organization
    from app.models.user import User
class AuditEvent(
    UUIDPrimaryKeyMixin,
    Base,
):
    __tablename__ = "audit_events"
    organization_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "organizations.id",
            ondelete="SET NULL",
        ),
        nullable=True,
        index=True,
    )
    actor_type: Mapped[AuditActorType] = mapped_column(
        Enum(
            AuditActorType,
            name="audit_actor_type",
            values_callable=lambda enum: [
                item.value for item in enum
            ],
        ),
        nullable=False,
        index=True,
    )
    actor_user_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "users.id",
            ondelete="SET NULL",
        ),
        nullable=True,
        index=True,
    )
    actor_membership_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey(
            "memberships.id",
            ondelete="SET NULL",
        ),
        nullable=True,
        index=True,
    )
    action: Mapped[str] = mapped_column(
        String(180),
        nullable=False,
        index=True,
    )
    resource_type: Mapped[str] = mapped_column(
        String(120),
        nullable=False,
        index=True,
    )
    resource_id: Mapped[UUID | None] = mapped_column(
        PGUUID(as_uuid=True),
        nullable=True,
        index=True,
    )
    request_id: Mapped[str | None] = mapped_column(
        String(120),
        nullable=True,
        index=True,
    )
    ip_address: Mapped[str | None] = mapped_column(
        INET,
        nullable=True,
    )
    user_agent: Mapped[str | None] = mapped_column(
        String(500),
        nullable=True,
    )
    event_metadata: Mapped[dict[str, Any]] = mapped_column(
        "metadata",
        JSONB,
        nullable=False,
        default=dict,
        server_default="{}",
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
        index=True,
    )
    organization: Mapped["Organization | None"] = relationship(
        back_populates="audit_events",
    )
    actor_user: Mapped["User | None"] = relationship(
        back_populates="audit_events",
        foreign_keys=[actor_user_id],
    )
    actor_membership: Mapped["Membership | None"] = relationship()

The Python property is called:

event_metadata

while the database column remains:

metadata

This avoids conflicts with SQLAlchemy’s own metadata attribute.

Audit Action Naming

Use consistent dot-separated action names.

Examples:

organization.created
organization.settings_updated
membership.invited
membership.activated
membership.role_changed
membership.suspended
membership.removed
role.created
role.permissions_updated
opportunity.created
opportunity.bid_decision_recorded
proposal.section_approved
proposal.export_generated

This convention supports:

• Filtering
• Reporting
• Alerting
• Policy analysis

Avoid vague audit actions such as:

updated
changed
action_completed

Optional System-Level Organization

Some audit events may occur outside an organization context.

Examples:

• Failed login
• Password reset request
• Global user suspension
• Platform maintenance operation

Therefore:

audit_events.organization_id

may be nullable.

Tenant-specific audit screens must still filter by organization and never expose global events accidentally.

Relationship Loading Strategy

SQLAlchemy relationships can use loading strategies such as:

select
selectin
joined
raise

Using eager loading everywhere can cause unnecessarily large queries.

Using lazy loading carelessly can create N+1 query problems.

A sensible initial strategy is:

• Keep default relationships conservative.
• Use selectinload explicitly in repositories for collections.
• Use joinedload only for small required references.
• Avoid serializing ORM relationships automatically.
• Return Pydantic response models rather than ORM objects directly.

The data-access layer should decide which relationships are needed for each use case.

Cascades Require Care

SQLAlchemy cascade settings and database ON DELETE rules are related but not identical.

For example:

cascade="all, delete-orphan"

controls ORM behavior.

Meanwhile:

ON DELETE CASCADE

controls PostgreSQL behavior.

Incorrect combinations can cause:

• Unexpected deletions
• Orphaned records
• Duplicate delete operations
• Historical data loss

Every relationship must be reviewed individually.

Do not apply delete-orphan to audit records or shared identities.

Index Strategy

Indexes should be driven by expected queries.

Initial indexes should support common access patterns.

Organizations

slug
status

Users

email_normalized
status

Memberships

organization_id
user_id
role_id
status
organization_id + status

Roles

organization_id
organization_id + slug

Permissions

code
category

Invitations

organization_id
email_normalized
status
expires_at

Audit Events

organization_id
created_at
actor_user_id
resource_type + resource_id
action
request_id

Avoid indexing every column.

Indexes improve reads but increase:

• Storage
• Write cost
• Migration time
• Maintenance overhead

Composite Indexes

Some queries filter by multiple columns.

Example audit query:

organization_id
created_at descending

A useful composite index may be:

organization_id, created_at

For memberships:

organization_id, status

For invitations:

organization_id, status, expires_at

Column order matters.

Design indexes around real query patterns, not aesthetics.

Constraint Strategy

The core models should include database constraints for critical rules.

Examples:

Organization

slug unique
currency length = 3
country code length = 2 when present

User

email_normalized unique

Membership

organization_id + user_id unique

Role

organization_id + slug unique

Permission

code unique

Role Permission

role_id + permission_id primary key

Invitation

token_hash unique
one pending invitation per organization and email

Check constraints can be added where they provide meaningful protection.

Case Sensitivity

PostgreSQL string comparisons are case-sensitive by default.

For fields such as:

• Emails
• Slugs
• Permission codes

the application should store normalized forms.

Possible alternatives include:

• CITEXT
• Functional indexes using lower()
• Dedicated normalized columns

A dedicated normalized email field is explicit and portable.

For slugs and permission codes, store lowercase values and validate them before persistence.

Model Registration for Alembic

Alembic cannot autogenerate migrations for models it has not imported.

Create:

app/database/model_registry.py

from app.models.audit import AuditEvent
from app.models.invitation import OrganizationInvitation
from app.models.membership import Membership
from app.models.organization import Organization
from app.models.permission import Permission, RolePermission
from app.models.role import Role
from app.models.user import User
__all__ = [
    "AuditEvent",
    "Membership",
    "Organization",
    "OrganizationInvitation",
    "Permission",
    "Role",
    "RolePermission",
    "User",
]

Then ensure Alembic imports this registry before reading metadata.

For example, in:

alembic/env.py

use:

from app.database.base import Base
from app.database import model_registry  # noqa: F401
target_metadata = Base.metadata

The import is intentional because it registers model tables in the metadata.

Avoiding Circular Imports

Model relationships can create circular imports.

Use:

from typing import TYPE_CHECKING

for type-only imports.

Example:

if TYPE_CHECKING:
    from app.models.organization import Organization

Then use string-based relationship targets:

organization: Mapped["Organization"] = relationship(...)

This preserves type checking while reducing runtime import cycles.

Model __repr__ Methods

Readable representations can help debugging.

Example:

def __repr__(self) -> str:
    return (
        f"<Organization id={self.id} "
        f"slug={self.slug!r}>"
    )

Do not include:

• Password hashes
• Invitation token hashes
• Sensitive metadata
• Confidential customer data

Representations may appear in logs and debugging output.

Pydantic Schemas Are Separate

SQLAlchemy models define database persistence.

Pydantic schemas define API contracts.

Do not use one class for both responsibilities.

For example:

Organization SQLAlchemy model

is not the same as:

OrganizationCreate
OrganizationUpdate
OrganizationResponse

The API schemas will be introduced when we build the organization and membership endpoints.

This separation prevents accidental exposure of fields such as:

password_hash
token_hash
internal settings

Domain Rules That Do Not Belong in Models

SQLAlchemy models may contain simple invariants, but complex business rules should remain in services.

Examples:

• An organization must always have at least one owner.
• A user cannot remove their own final owner membership.
• A suspended organization cannot create invitations.
• An invitation role must belong to the same organization.
• A user cannot accept an expired invitation.
• A default role cannot be removed while active members use it.
• A membership role change requires permission.
• An organization owner role cannot lose critical permissions.

These rules require context, authorization, and transaction handling.

They do not belong in route handlers or simple ORM property setters.

Tenant-Aware Model Rules

Every future tenant-owned table should normally include:

organization_id

Examples:

opportunities
documents
requirements
knowledge_documents
proposals
tasks
exports
ai_operations

Even when the organization could theoretically be derived through another relationship, storing organization_id may be useful for:

• Direct tenant filtering
• Indexing
• Row-level security
• Auditability
• Query performance

However, duplicated ownership fields create consistency requirements.

The database should enforce matching organization relationships where possible.

Future PostgreSQL Row-Level Security

Application-level filtering is necessary, but PostgreSQL Row-Level Security may later provide an additional boundary.

Conceptually:

Current organization context
        ↓
PostgreSQL policy
        ↓
Only matching rows visible

RLS is not implemented in this article because it requires:

• Connection-context design
• Transaction-local settings
• Worker support
• Migration policies
• Operational testing

Still, the model design should not prevent its future use.

Consistent organization_id fields support that direction.

Soft Deletion Strategy

Not every table needs a deleted_at column.

Adding soft deletion everywhere creates complexity:

• Every query needs extra filtering.
• Unique constraints become more complicated.
• Restoring data requires business rules.
• Relationships may point to deleted records.

For the core models:

• Organization uses lifecycle status.
• User uses lifecycle status.
• Membership uses lifecycle status and timestamps.
• Invitation uses lifecycle status.
• Audit events are retained.
• Roles may later use an archive flag if needed.

Soft deletion should be introduced per domain, not as an automatic universal mixin.

Seeded Roles and Permissions

New organizations need an initial role catalogue.

Suggested starter roles:

Organization Owner
Administrator
Bid Manager
Proposal Manager
Contributor
Reviewer
Viewer

Suggested initial permission categories:

organization
members
roles
opportunities
documents
requirements
knowledge
proposals
tasks
approvals
reports
audit
ai_usage

The exact permission matrix should be documented before seeding.

Do not bury authorization policy inside ad hoc Python conditionals.

A future onboarding service can:

1. Create the organization.
2. Create standard roles.
3. Assign permissions.
4. Create the owner membership.
5. Record audit events.

All steps should occur inside one transaction.

Role Immutability Considerations

System-defined roles may be protected from:

• Renaming
• Deletion
• Removal of required permissions

However, organizations may need customization.

A balanced approach is:

• Mark seeded roles with is_system.
• Allow display-name changes only if product policy permits.
• Prevent deletion of essential roles.
• Allow creation of custom roles.
• Record permission changes in audit events.

The role table should not imply that system roles are globally shared if each organization receives its own role records.

Database Model Tests

Model tests should verify structural invariants.

Create:

backend/tests/models/
├── __init__.py
├── test_organization_model.py
├── test_user_model.py
├── test_membership_model.py
├── test_role_permission_model.py
├── test_invitation_model.py
└── test_audit_model.py

The tests should use an isolated test database rather than the development database.

Test Database Strategy

Recommended options include:

• Dedicated PostgreSQL test database
• Temporary PostgreSQL container
• Transaction rollback per test
• Testcontainers

SQLite should not be treated as a complete substitute because BidRadar uses PostgreSQL-specific features such as:

• UUID
• JSONB
• INET
• Partial indexes
• Native enums
• pgvector later

Using SQLite can hide PostgreSQL-specific problems.

Transaction-Rollback Fixture

A common test pattern is:

1. Open a database connection.
2. Begin a transaction.
3. Bind a session to the connection.
4. Run the test.
5. Roll back the transaction.
6. Close the connection.

This provides test isolation without recreating the full database after every test.

The exact fixture will be implemented once the test database configuration is finalized.

Organization Model Tests

Test:

• Organization creation
• UUID generation
• Default status
• Default currency
• Default timezone
• Unique slug
• Empty settings object
• Updated timestamp behavior

Example conceptual test:

def test_organization_defaults(session):
    organization = Organization(
        name="Northstar Cloud Consulting",
        slug="northstar-cloud-consulting",
    )
    session.add(organization)
    session.flush()
    assert organization.id is not None
    assert organization.status == OrganizationStatus.ACTIVE
    assert organization.default_currency == "USD"
    assert organization.timezone == "UTC"
    assert organization.settings == {}

User Model Tests

Test:

• User creation
• Unique normalized email
• Nullable password hash
• Default status
• Membership relationships
• Email verification timestamp

Never print password hashes during tests.

Membership Tests

Test:

• User can belong to multiple organizations
• One user cannot have two memberships in one organization
• Membership defaults to invited
• Membership role is required
• Lifecycle timestamps are nullable initially
• Relationship loading works
• Role must belong to the correct organization through service validation

Role and Permission Tests

Test:

• Role slug is unique within one organization
• Same role slug can exist in another organization
• Permission code is globally unique
• Duplicate role-permission assignment fails
• Role permission relationships load correctly

Invitation Tests

Test:

• Token hash is unique
• Raw token is never stored
• Invitation defaults to pending
• Expiration is required
• Duplicate pending invitation is blocked
• Historical expired invitation can coexist where the partial index permits it

Audit Event Tests

Test:

• Event can be linked to an organization
• Event can be system-generated without a user
• Actor membership may be stored
• Metadata defaults to an empty object
• Request ID can be recorded
• Audit event does not have an update lifecycle
• Sensitive fields are not automatically captured

Model Validation Versus Database Validation

A model instance can exist in Python before database constraints are checked.

For example:

organization = Organization(
    name="Example",
    slug="Example With Spaces",
)

SQLAlchemy will not automatically convert this into a valid slug.

Validation should occur before persistence through:

• Pydantic input schemas
• Domain services
• Dedicated normalization functions

The database then enforces final invariants such as uniqueness and nullability.

Use layered validation:

API validation
      ↓
Service/domain validation
      ↓
Database constraints

Avoiding Automatic Business Side Effects

Do not hide complex actions inside ORM events such as:

after_insert
before_update

For example, creating an organization should not silently create roles, memberships, and audit events through scattered model hooks.

Instead, use an explicit service transaction:

OrganizationService.create_organization(...)

This makes behavior:

• Testable
• Traceable
• Easier to reason about
• Easier to retry

ORM events may still be appropriate for narrow technical concerns, but not for core business workflows.

Initial File Structure After Part 12

The backend should now resemble:

backend/
├── app/
│   ├── database/
│   │   ├── base.py
│   │   ├── health.py
│   │   ├── mixins.py
│   │   ├── model_registry.py
│   │   ├── naming.py
│   │   └── session.py
│   │
│   ├── models/
│   │   ├── __init__.py
│   │   ├── audit.py
│   │   ├── enums.py
│   │   ├── invitation.py
│   │   ├── membership.py
│   │   ├── organization.py
│   │   ├── permission.py
│   │   ├── role.py
│   │   └── user.py
│   │
│   └── main.py
│
├── alembic/
├── tests/
│   └── models/
├── requirements/
├── pyproject.toml
└── README.md

What We Are Not Building Yet

This article does not implement:

• Organization API endpoints
• User registration
• Login
• Password hashing
• JWT tokens
• Invitation acceptance
• Permission dependencies
• Role-management screens
• Organization onboarding
• Tender opportunities
• Procurement documents
• Proposal models
• Audit-event services
• Seed scripts
• Database migration generation

The models define persistence structures.

The next article will create and review the migration that materializes them in PostgreSQL.

Security Review

Before generating the migration, review the core models carefully.

Passwords

Only password hashes may be stored.

Invitation Tokens

Only token hashes may be stored.

Tenant Ownership

Organization relationships must be explicit.

Role Assignment

A role must not be assigned across organizations.

Audit Logs

Sensitive payloads must not be copied blindly.

Deletion

Cascade rules must not destroy required history.

Email

Normalized email uniqueness must be enforced.

Settings JSON

Secrets must not be stored in generic organization settings.

User Identity

A user record must not expose membership access automatically.

Authorization

Roles and permissions must be checked through the active membership.

Google AI Studio Planning Prompt

Use this prompt before asking Google AI Studio to generate the model files.

You are planning Part 12 of the BidRadar development series.
BidRadar is a secure, multi-tenant AI Tender Intelligence and Proposal Automation SaaS platform for IT service providers.
Before planning, read:
- docs/project-vision.md
- docs/product-requirements.md
- docs/architecture.md
- docs/database-schema.md
- docs/api-specification.md
- docs/design-system.md
- docs/master-build-prompt.md
Inspect the existing backend, SQLAlchemy Base, mixins, naming conventions, database session, Alembic configuration, and test setup.
Do not generate code yet.
Plan the first core SQLAlchemy models:
- Organization
- User
- Membership
- Role
- Permission
- RolePermission
- OrganizationInvitation
- AuditEvent
For each model, define:
1. Purpose.
2. Table name.
3. Columns.
4. SQL types.
5. Nullability.
6. Defaults.
7. Server defaults.
8. Foreign keys.
9. Delete behavior.
10. Relationships.
11. Unique constraints.
12. Check constraints.
13. Indexes.
14. Lifecycle fields.
15. Sensitive-data rules.
16. Tenant-isolation implications.
17. Audit implications.
18. Required tests.
Also evaluate:
- UUID primary-key strategy
- Timestamp strategy
- Enum persistence strategy
- Email normalization
- Invitation-token hashing
- Role ownership
- Cross-tenant role-assignment risks
- Partial unique indexes
- Audit-event immutability
- Cascade risks
- Alembic model registration
- Circular-import risks
- PostgreSQL-specific features
- Future row-level security compatibility
Do not create:
- API routes
- Pydantic API schemas
- Repositories
- Services
- Authentication
- JWT handling
- Seed data
- Business workflows
- Migrations
Identify unresolved design decisions before implementation.

Google AI Studio Implementation Prompt

After reviewing the model plan, use:

You are implementing Part 12 of the BidRadar development series.
BidRadar is a secure, multi-tenant AI Tender Intelligence and Proposal Automation SaaS platform for IT service providers.
Before generating code, read:
- docs/project-vision.md
- docs/product-requirements.md
- docs/architecture.md
- docs/database-schema.md
- docs/api-specification.md
- docs/design-system.md
- docs/master-build-prompt.md
Inspect the existing repository before modifying files.
Your task is to implement the first core SQLAlchemy 2.0 database models.
Create:
- Organization
- User
- Membership
- Role
- Permission
- RolePermission
- OrganizationInvitation
- AuditEvent
- Required Python enums
- Model registry
- Model-level tests
Use:
- PostgreSQL
- SQLAlchemy 2.0 typed mappings
- Mapped
- mapped_column
- relationship
- UUID primary keys
- Timezone-aware timestamps
- Existing Base and naming conventions
- Explicit table names
- Explicit foreign-key behavior
- Explicit relationship back_populates where appropriate
Requirements:
1. Organization is the primary tenant.
2. User is a global identity.
3. Membership connects a user to an organization.
4. One user may belong to multiple organizations.
5. One user may have only one membership per organization.
6. Membership has one role.
7. Roles belong to organizations.
8. Permissions are globally defined capability records.
9. Roles receive permissions through RolePermission.
10. Invitations store only a token hash.
11. Invitation email must have a normalized form.
12. Audit events are append-only records.
13. Audit events support user, system, AI, and integration actors.
14. JSON metadata must use JSONB.
15. Do not name a Python ORM attribute `metadata`.
16. Do not store raw passwords.
17. Do not store raw invitation tokens.
18. Do not expose ORM models as API responses.
19. Do not add automatic business workflows through ORM events.
20. Register all models for Alembic metadata discovery.
Review carefully:
- Role and membership organization consistency
- Foreign-key delete behavior
- Cascade settings
- Partial unique index for pending invitations
- Unique normalized email
- Unique role slug per organization
- Unique membership per organization and user
- Unique permission code
- Composite primary key for role permissions
- Audit indexes
- Circular imports
- Type annotations
- Enum names
- Server defaults
Do not implement:
- Alembic migration files
- Authentication endpoints
- Password hashing functions
- Invitation acceptance
- Organization services
- Membership services
- Repositories
- API routes
- Pydantic request and response schemas
- Seed data
- Tender-domain models
Generate:
1. Final file tree.
2. Complete file contents.
3. Model explanation.
4. Relationship explanation.
5. Constraint explanation.
6. Index explanation.
7. Security considerations.
8. Tests.
9. Commands to run tests.
10. Assumptions.
11. Remaining open questions.
12. Suggested Git commits.
Keep the implementation incremental, typed, and aligned with the existing architecture.

Google AI Studio Review Prompt

After generating the models, use a separate review prompt.

Review the BidRadar Part 12 core SQLAlchemy models.
Do not generate migrations yet.
Do not add API endpoints.
Do not implement authentication.
Evaluate:
1. Alignment with docs/database-schema.md.
2. Alignment with the multi-tenant architecture.
3. SQLAlchemy 2.0 typed mapping correctness.
4. UUID primary-key consistency.
5. Timestamp consistency.
6. Enum persistence correctness.
7. Organization ownership.
8. Membership uniqueness.
9. Cross-tenant role-assignment risk.
10. Permission-model correctness.
11. RolePermission composite-key correctness.
12. Invitation-token security.
13. Pending-invitation uniqueness.
14. Email normalization.
15. Audit-event immutability.
16. Audit metadata safety.
17. Foreign-key delete behavior.
18. ORM cascade behavior.
19. Relationship ambiguity.
20. Circular-import risk.
21. Alembic model registration.
22. Missing constraints.
23. Missing indexes.
24. Excessive indexes.
25. Incorrect nullable fields.
26. Unsafe server defaults.
27. Sensitive fields in repr methods.
28. PostgreSQL compatibility.
29. Test coverage.
30. Business logic incorrectly placed in models.
31. Code generated beyond the requested scope.
Classify every finding as:
- Critical
- High
- Medium
- Low
For every finding:
- Identify the file and relevant model.
- Explain the problem.
- Explain the tenant, security, data-integrity, or operational risk.
- Recommend a specific correction.
Do not rewrite the files until the findings have been reviewed.

Manual Review Checklist

Organization

• Does the organization use a UUID?
• Is the slug unique?
• Is the status explicit?
• Is the default currency valid?
• Is the timezone stored?
• Is settings data JSONB?
• Are secrets prohibited from settings?
• Are deletion rules appropriate?

User

• Is normalized email unique?
• Is the original email retained?
• Is the password hash nullable only by design?
• Is the global user status separate from membership status?
• Are login and verification timestamps nullable?
• Are sensitive fields excluded from representations?

Membership

• Is one user limited to one membership per organization?
• Is a role required?
• Does the role belong to the same organization?
• Are status timestamps modeled?
• Are invitation relationships explicit?
• Are deletion rules historically safe?

Roles and Permissions

• Are roles organization-specific?
• Is the role slug unique per organization?
• Are permission codes globally unique?
• Does RolePermission prevent duplicates?
• Are system-role semantics documented?
• Are permissions explicit capabilities?

Invitations

• Is only the token hash stored?
• Is expiration required?
• Is the email normalized?
• Is the role organization-consistent?
• Can only one pending invitation exist?
• Are accepted, expired, and revoked states preserved?

Audit Events

• Are events append-only?
• Is actor type explicit?
• Can system and AI events exist without users?
• Is organization context stored where applicable?
• Are request IDs supported?
• Is metadata JSONB?
• Are sensitive payloads excluded?
• Are audit indexes appropriate?

Validation Checklist

Before continuing to Part 13, verify that:

• The app/models package exists.
• Every model uses the shared declarative Base.
• Every primary business entity uses UUIDs.
• Timestamps are timezone-aware.
• Organization status is defined.
• User status is defined.
• Membership status is defined.
• Invitation status is defined.
• Audit actor type is defined.
• Organization model exists.
• User model exists.
• Membership model exists.
• Role model exists.
• Permission model exists.
• RolePermission model exists.
• OrganizationInvitation model exists.
• AuditEvent model exists.
• Table names are explicit.
• Foreign keys are explicit.
• Delete behavior is explicit.
• Relationships use type annotations.
• Relationship ambiguity is resolved with foreign_keys where necessary.
• Organization slug is unique.
• User normalized email is unique.
• Membership is unique by organization and user.
• Role slug is unique within an organization.
• Permission code is unique.
• RolePermission has a composite primary key.
• Invitation token hash is unique.
• Pending invitation uniqueness is designed.
• Raw passwords are not stored.
• Raw invitation tokens are not stored.
• Audit metadata does not use the Python attribute name metadata.
• Audit records do not use an ordinary update lifecycle.
• Model registration is configured for Alembic.
• Circular imports are controlled.
• Model tests exist.
• Tests use PostgreSQL-compatible infrastructure.
• No API schemas have been added.
• No routers have been added.
• No services have been added.
• No authentication logic has been added.
• No migration has been generated yet.
• No tender-domain models have been added.

Definition of Done

Part 12 is complete when:

• BidRadar has a clear organization tenant model.
• Users exist independently of organizations.
• Memberships connect users to organizations.
• Role assignments are organization-aware.
• Permissions are represented explicitly.
• Duplicate memberships are prevented.
• Invitation records support secure acceptance workflows.
• Raw invitation tokens are never stored.
• Audit events can capture user, system, AI, and integration activity.
• All models are registered with SQLAlchemy metadata.
• Core constraints and indexes are defined.
• Model tests verify critical invariants.
• No API or authentication workflows have been implemented prematurely.
• The model set is ready for Alembic migration generation.

Recommended Git Commits

A single commit may be used:

feat(database): add core organization and access models

A more granular sequence may be:

feat(models): add organization and user models

feat(models): add membership and role models

feat(models): add permissions and role assignments

feat(models): add organization invitation model

feat(audit): add append-only audit event model

test(models): add core model constraint tests

What We Built

In this article, we created the first real business entities in the BidRadar database model.

We established:

• Organizations as tenants
• Users as global identities
• Memberships as organization-specific access records
• Roles as organization-owned permission groups
• Permissions as explicit platform capabilities
• Role-permission assignments
• Secure organization invitations
• Append-only audit events
• Lifecycle statuses
• Foreign-key behavior
• Uniqueness rules
• Indexing strategy
• Tenant-aware relationships
• Alembic model registration
• Model-level testing requirements

This model set creates the ownership and authorization foundation for the entire BidRadar platform.

Every future opportunity, document, requirement, knowledge record, proposal, task, approval, export, and AI operation will build on these tenant and identity relationships.

Next Article

Part 13 — Creating the Initial Core Database Migration

In Part 13, we will convert the SQLAlchemy models into a reviewed Alembic migration.

We will:

• Import the complete model registry
• Generate the migration
• Review every generated table
• Review PostgreSQL enum creation
• Add partial unique indexes
• Validate foreign keys
• Validate cascade behavior
• Validate unique constraints
• Validate indexes
• Upgrade a clean database
• Inspect the resulting schema
• Downgrade safely
• Upgrade again
• Add migration tests
• Document the migration workflow

By the end of Part 13, the first BidRadar business schema will exist inside PostgreSQL and will be reproducible from version-controlled migration files.