Series: Building Quorentra CRM from Zero — A Modular, ChatGPT-Native AI CRM

Building the organization, user, and membership foundation with UUIDs, timestamps, SQLAlchemy relationships, tenant ownership, role modeling, Alembic migrations, and persistence tests

Quorentra Multi-Tenant Domain: Building from Zero — Part 6

1. Introduction

In Part 5, Quorentra gained persistent storage.

We established:

			
FastAPI
   │
   ▼
SQLAlchemy
   │
   ▼
Psycopg
   │
   ▼
PostgreSQL

		

We also introduced Alembic so database structure can evolve through version-controlled migrations.

But the database currently contains almost no application data.

There are no organizations.

There are no users.

There is no tenant boundary.

That changes now.

In this article, we will build the first real Quorentra domain foundation:

			
Organization
     │
     ▼
Membership
     ▲
     │
    User

		

This may appear simpler than companies, contacts, or opportunities.

Architecturally, however, it is more fundamental.

Every future CRM record will need to answer:

Which organization owns this data?

And every authenticated user action will need to answer:

Is this user a member of that organization, and what are they allowed to do?

The organization-user-membership model provides the answer.

2. Why Organizations Come First

Quorentra is being built as a multi-tenant CRM.

That means multiple customers can use the same application while remaining logically isolated.

For example:

			
Quorentra
   │
   ├── Organization A
   │      ├── Users
   │      ├── Companies
   │      ├── Contacts
   │      └── Opportunities
   │
   └── Organization B
          ├── Users
          ├── Companies
          ├── Contacts
          └── Opportunities

		

Organization A must not see Organization B’s data.

That boundary should not be invented later.

It should be part of the domain architecture before CRM entities appear.

This gives us one of Quorentra’s most important design rules:

Tenant ownership is explicit from the beginning.

3. Why Users and Organizations Are Separate

It might seem easier to define:

			
User
 └── organization_id

and stop there.

But that approach becomes restrictive.

A user may eventually belong to:

one organization;
several organizations;
a consulting organization and a customer organization;
a sandbox organization;
multiple business units represented as organizations.

A better model is:

			
User
  │
  ▼
Membership
  │
  ▼
Organization

		

This is a many-to-many relationship.

Conceptually:

			
User A
   │
   ├── Membership → Organization 1
   └── Membership → Organization 2

while:

			
Organization 1
   │
   ├── Membership → User A
   ├── Membership → User B
   └── Membership → User C

		

The membership itself becomes a meaningful domain object.

4. Why Membership Is More Than a Join Table

A simple many-to-many database relationship could use a join table containing only:

			
user_id
organization_id

But Quorentra needs more.

Membership will eventually carry information such as:

			
role
status
joined_at
invited_by

For the MVP, we will start with:

			
id
organization_id
user_id
role
created_at
updated_at

		

This makes membership an actual domain entity rather than an invisible relational implementation detail.

5. Starting Checkpoint

Part 6 assumes the persistence architecture from Part 5.

The relevant backend structure should resemble:

			
backend/
│
├── alembic/
│   └── versions/
│
├── app/
│   ├── api/
│   ├── core/
│   ├── db/
│   │   ├── __init__.py
│   │   ├── base.py
│   │   ├── dependencies.py
│   │   ├── health.py
│   │   └── session.py
│   │
│   └── main.py
│
├── scripts/
├── tests/
├── alembic.ini
├── requirements.txt
└── requirements-dev.txt

		

PostgreSQL must be running.

The database health check should succeed:

GET /api/v1/health

with:

			
{
  "status": "healthy",
  "service": "quorentra-api",
  "version": "0.0.3",
  "database": "connected"
}

		

Do not continue if the Part 5 database foundation is not stable.

6. The First Domain Modules

Create a module structure inside:

app/modules/

From:

quorentra/backend

run:

			
mkdir app\modules
New-Item app\modules\__init__.py -ItemType File

Now create three domain modules:

			
mkdir app\modules\organizations
mkdir app\modules\users
mkdir app\modules\memberships

Add package files:

			
New-Item app\modules\organizations\__init__.py -ItemType File
New-Item app\modules\users\__init__.py -ItemType File
New-Item app\modules\memberships\__init__.py -ItemType File

The structure becomes:

			
app/
└── modules/
    ├── organizations/
    ├── users/
    └── memberships/

		

This establishes the modular pattern that later features will follow.

7. Why We Are Not Creating `models.py` for Everything

A common small-project structure is:

			
app/
└── models.py

containing every SQLAlchemy model.

That works initially.

But Quorentra will eventually have dozens of domain entities.

A single global model file would become difficult to maintain.

Instead, we want:

			
modules/
├── organizations/
│   └── model.py
├── users/
│   └── model.py
├── memberships/
│   └── model.py
├── companies/
│   └── model.py
├── contacts/
│   └── model.py
└── opportunities/
    └── model.py

		

Each business capability owns its model.

That is the beginning of the modular monolith.

8. Shared Model Concerns

Before writing the first domain models, notice that many entities will need the same technical fields.

Most will need:

			
id
created_at
updated_at

We could duplicate these fields everywhere.

A better approach is to create reusable SQLAlchemy mixins.

Create:

app/db/mixins.py

From PowerShell:

New-Item app\db\mixins.py -ItemType File

Add:

			
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),
        server_default=func.now(),
        nullable=False,
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
    )

		

These mixins will be reused throughout Quorentra.

9. Why UUID Primary Keys?

Many traditional applications use integer IDs:

Quorentra will use UUIDs for most core entities.

Example:

9c85c1c4-8aae-4ed6-a446-05ea0f7ec0fd

UUIDs provide several advantages.

They are globally unique.

They are safer to expose in distributed interfaces.

They reduce coupling to database sequence behavior.

They work well when records may eventually be created across multiple services or integration channels.

And they make accidental enumeration harder than sequential integers.

UUIDs do not replace authorization.

But they are a strong fit for this architecture.

10. Why PostgreSQL UUID Type?

PostgreSQL has a native UUID type.

We use:

PGUUID(as_uuid=True)

so Python works with:

uuid.UUID

objects rather than arbitrary strings.

This gives us stronger typing throughout the application.

11. Why Server-Side Timestamps?

The timestamp mixin uses:

server_default=func.now()

This means PostgreSQL supplies the initial timestamp.

That gives the database authoritative creation time.

For updates, we also use:

onupdate=func.now()

Later, if we need stricter database-managed update timestamps, we can evolve the implementation.

For the MVP, this provides a clean baseline.

12. Build the Organization Model

Create:

app/modules/organizations/model.py

Run:

New-Item app\modules\organizations\model.py -ItemType File

Add:

			
from typing import TYPE_CHECKING
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db.base import Base
from app.db.mixins import TimestampMixin, UUIDPrimaryKeyMixin
if TYPE_CHECKING:
    from app.modules.memberships.model import Membership
class Organization(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "organizations"
    name: Mapped[str] = mapped_column(
        String(200),
        nullable=False,
    )
    slug: Mapped[str] = mapped_column(
        String(100),
        unique=True,
        nullable=False,
        index=True,
    )
    memberships: Mapped[list["Membership"]] = relationship(
        back_populates="organization",
        cascade="all, delete-orphan",
    )

		

This is Quorentra’s first real domain model.

13. Organization Fields

The model currently contains:

			
id
name
slug
created_at
updated_at

		

For example:

			
name = Acme Consulting
slug = acme-consulting

The slug gives us a human-readable stable organization identifier.

Later it could appear in:

			
URLs
tenant selection
administration
integrations

The slug is unique globally in the MVP.

14. Why Keep Organization Small?

We could immediately add:

			
billing_email
country
timezone
currency
logo
industry
settings
subscription_plan

		

But none of those are required to establish the tenant boundary.

A key development principle remains:

Add fields when a working capability requires them.

The organization model should begin with the minimum domain identity.

15. Build the User Model

Create:

app/modules/users/model.py

Run:

New-Item app\modules\users\model.py -ItemType File

Add:

			
from typing import TYPE_CHECKING
from sqlalchemy import Boolean, String
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db.base import Base
from app.db.mixins import TimestampMixin, UUIDPrimaryKeyMixin
if TYPE_CHECKING:
    from app.modules.memberships.model import Membership
class User(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "users"
    email: Mapped[str] = mapped_column(
        String(320),
        unique=True,
        nullable=False,
        index=True,
    )
    display_name: Mapped[str | None] = mapped_column(
        String(200),
        nullable=True,
    )
    is_active: Mapped[bool] = mapped_column(
        Boolean,
        default=True,
        nullable=False,
    )
    memberships: Mapped[list["Membership"]] = relationship(
        back_populates="user",
        cascade="all, delete-orphan",
    )

		

Notice what is missing:

password_hash

That is intentional.

Authentication arrives in a later article.

At this stage, we are building identity persistence, not login.

16. Why Email Is Globally Unique

The initial user model defines:

unique=True

for email.

That means one user identity can participate in multiple organizations.

Conceptually:

			
ben@example.com
      │
      ├── Organization A
      ├── Organization B
      └── Organization C

		

rather than creating three separate user identities.

This works naturally with the membership model.

17. Normalize Email Later

Email addresses should eventually be normalized before persistence.

For example:

Ben@example.com

and:

ben@example.com

should not accidentally become separate user identities.

That normalization belongs in the application/service layer.

We will address it when registration is implemented.

The database-level uniqueness constraint remains an important final safeguard.

18. Build the Membership Model

Create:

app/modules/memberships/model.py

Run:

New-Item app\modules\memberships\model.py -ItemType File

Add:

			
from typing import TYPE_CHECKING
from uuid import UUID
from sqlalchemy import ForeignKey, String, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db.base import Base
from app.db.mixins import TimestampMixin, UUIDPrimaryKeyMixin
if TYPE_CHECKING:
    from app.modules.organizations.model import Organization
    from app.modules.users.model import User
class Membership(
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    Base,
):
    __tablename__ = "memberships"
    __table_args__ = (
        UniqueConstraint(
            "organization_id",
            "user_id",
            name="uq_membership_organization_user",
        ),
    )
    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: Mapped[str] = mapped_column(
        String(50),
        nullable=False,
        default="member",
    )
    organization: Mapped["Organization"] = relationship(
        back_populates="memberships",
    )
    user: Mapped["User"] = relationship(
        back_populates="memberships",
    )

		

Membership now connects users to organizations.

19. The Domain Relationship

We now have:

			
Organization
     │
     │ one-to-many
     ▼
Membership
     ▲
     │ many-to-one
     │
    User

		

More precisely:

			
Organization 1
     │
     ├── Membership A ── User A
     ├── Membership B ── User B
     └── Membership C ── User C

		

while:

			
User A
  │
  ├── Membership A ── Organization 1
  └── Membership D ── Organization 2

This provides the multi-tenant identity foundation we need.

20. Why the Unique Membership Constraint Matters

Without:

			
UniqueConstraint(
    "organization_id",
    "user_id",
)

the database could contain:

			
User A → Organization 1
User A → Organization 1
User A → Organization 1

three times.

That would make authorization and role logic ambiguous.

The database therefore guarantees:

A user can have at most one membership record per organization.

This is a domain invariant worth enforcing at the database level.

21. Initial Role Model

For the MVP, membership roles are:

			
owner
admin
member

The model currently stores role as a string.

This keeps the persistence layer simple.

Later, application validation will ensure only supported role values are accepted.

We could create a PostgreSQL enum immediately, but enums can make migrations more cumbersome when roles evolve.

For the initial MVP, a validated string is more flexible.

22. Why Role Belongs to Membership

Role does not belong on:

User

because the same user may have different privileges in different organizations.

For example:

			
User A
   │
   ├── Organization 1 → owner
   └── Organization 2 → member

Therefore:

role

belongs to:

Membership

This is another reason membership must be a real domain entity.

23. Make Alembic Aware of the Models

Alembic currently knows about:

Base.metadata

but models must be imported so their table definitions register with that metadata.

Create:

app/db/models.py

Run:

New-Item app\db\models.py -ItemType File

Add:

			
from app.modules.memberships.model import Membership
from app.modules.organizations.model import Organization
from app.modules.users.model import User
__all__ = [
    "Membership",
    "Organization",
    "User",
]

		

This module provides a central import point for migration discovery.

24. Import Models in Alembic

Open:

alembic/env.py

Add:

import app.db.models

before:

target_metadata = Base.metadata

For example:

			
from app.core.config import get_settings
from app.db.base import Base
import app.db.models
settings = get_settings()
config.set_main_option(
    "sqlalchemy.url",
    settings.database_url,
)
target_metadata = Base.metadata

		

The import is intentionally present even if your editor reports it as unused.

Its purpose is registration.

25. Verify Model Metadata

Before creating a migration, verify that SQLAlchemy sees the tables.

From:

quorentra/backend

run:

			
python -c "import app.db.models; from app.db.base import Base; print(sorted(Base.metadata.tables.keys()))"

Expected:

['memberships', 'organizations', 'users']

If you do not see all three tables, stop.

Do not generate the migration yet.

Fix model imports first.

26. Generate the Migration

Run:

			
python -m alembic revision --autogenerate -m "add organizations users memberships"

Alembic should report operations such as:

			
Detected added table 'organizations'
Detected added table 'users'
Detected added table 'memberships'

A new revision appears under:

alembic/versions/

Do not immediately apply it.

First inspect it.

27. Always Inspect Auto-Generated Migrations

Alembic autogeneration is a productivity tool.

It is not an excuse to stop reviewing schema changes.

Open the generated migration.

You should see operations conceptually similar to:

			
op.create_table(
    "organizations",
    ...
)
op.create_table(
    "users",
    ...
)
op.create_table(
    "memberships",
    ...
)

		

and indexes for fields such as:

			
organizations.slug
users.email
memberships.organization_id
memberships.user_id

Also verify the unique constraint:

uq_membership_organization_user

The migration should match what you intended.

28. Check Foreign Keys

The membership migration should include foreign keys from:

memberships.organization_id

to:

organizations.id

and:

memberships.user_id

to:

users.id

Both should use:

ON DELETE CASCADE

as defined in the model.

This means deleting an organization removes its membership rows.

Deleting a user also removes that user’s membership rows.

The database will therefore not leave orphaned memberships.

29. Apply the Migration

Once the migration looks correct, run:

python -m alembic upgrade head

Expected output should indicate the new migration has been applied.

Verify:

python -m alembic current

The latest revision should now be active.

30. Inspect PostgreSQL

Open pgAdmin and navigate to:

			
quorentra
  ↓
Schemas
  ↓
public
  ↓
Tables

		

You should now see:

			
alembic_version
memberships
organizations
users

Quorentra has its first domain schema.

31. Inspect the Organization Table

The organizations table should contain fields similar to:

			
id
name
slug
created_at
updated_at

		

and constraints including:

			
PRIMARY KEY id
UNIQUE slug

The slug should also have an index.

32. Inspect the User Table

The users table should contain:

			
id
email
display_name
is_active
created_at
updated_at

		

with:

			
PRIMARY KEY id
UNIQUE email

and an email index.

33. Inspect the Membership Table

The memberships table should contain:

			
id
organization_id
user_id
role
created_at
updated_at

		

with:

			
PRIMARY KEY id
FOREIGN KEY organization_id
FOREIGN KEY user_id
UNIQUE organization_id + user_id

This is the tenant identity boundary.

34. Add Model-Level Role Constants

Create:

app/modules/memberships/constants.py

Run:

New-Item app\modules\memberships\constants.py -ItemType File

Add:

			
ROLE_OWNER = "owner"
ROLE_ADMIN = "admin"
ROLE_MEMBER = "member"
SUPPORTED_ROLES = {
    ROLE_OWNER,
    ROLE_ADMIN,
    ROLE_MEMBER,
}

		

Now update the membership model default:

from app.modules.memberships.constants import ROLE_MEMBER

and:

			
role: Mapped[str] = mapped_column(
    String(50),
    nullable=False,
    default=ROLE_MEMBER,
)

		

This prevents magic strings from spreading throughout the codebase.

35. Why Not Create Full RBAC Yet?

The role names exist.

But we are not yet implementing permissions.

That comes later.

At this stage we only need the membership to record an initial organizational role.

Eventually RBAC may map roles to permissions such as:

			
company.read
company.write
contact.read
opportunity.write
user.manage
organization.manage

		

But none of those CRM capabilities exist yet.

Therefore full permission modeling would be premature.

36. Add Persistence Tests

We should verify that the models actually work together.

Create:

tests/integration/

Run:

			
mkdir tests\integration
New-Item tests\integration\__init__.py -ItemType File
New-Item tests\integration\test_tenant_models.py -ItemType File

This is our first clearly separated integration-test package.

37. Build the First Persistence Test

Open:

tests/integration/test_tenant_models.py

Add:

			
from uuid import uuid4
from app.db.session import SessionLocal
from app.modules.memberships.constants import ROLE_OWNER
from app.modules.memberships.model import Membership
from app.modules.organizations.model import Organization
from app.modules.users.model import User
def test_create_organization_user_membership() -> None:
    unique_value = uuid4().hex
    organization = Organization(
        name="Quorentra Test Organization",
        slug=f"quorentra-test-{unique_value}",
    )
    user = User(
        email=f"test-{unique_value}@example.com",
        display_name="Test User",
    )
    membership = Membership(
        organization=organization,
        user=user,
        role=ROLE_OWNER,
    )
    with SessionLocal() as db:
        db.add(membership)
        db.commit()
        db.refresh(organization)
        db.refresh(user)
        db.refresh(membership)
        assert organization.id is not None
        assert user.id is not None
        assert membership.id is not None
        assert membership.organization_id == organization.id
        assert membership.user_id == user.id
        assert membership.role == ROLE_OWNER

		

This test creates all three entities in one transaction.

38. Why Use Unique Test Values?

Both:

organization.slug

and:

user.email

are unique.

If we hard-code:

test@example.com

the first test run may succeed while the second fails because the record remains in the database.

Using:

uuid4().hex

makes each test record unique.

Later we will create stronger database test isolation.

For now this keeps the first integration test straightforward.

39. Run the Integration Test

Run:

python -m pytest tests/integration/test_tenant_models.py

Expected:

1 passed

If it succeeds, we have verified:

			
SQLAlchemy Models
      │
      ▼
Relationships
      │
      ▼
Session
      │
      ▼
PostgreSQL

		

for the first Quorentra domain entities.

40. Inspect the Test Data

Open pgAdmin and inspect:

			
organizations
users
memberships

You should see the test records.

For example:

			
Organization:
Quorentra Test Organization

and:

			
User:
Test User

with a membership linking them.

The records demonstrate that the many-to-many architecture works.

41. Add Relationship Assertions

We can make the test stronger.

After committing, add:

			
assert membership.organization.name == "Quorentra Test Organization"
assert membership.user.display_name == "Test User"

And:

			
assert len(organization.memberships) == 1
assert len(user.memberships) == 1

The full test now verifies both foreign keys and ORM relationships.

42. Add a Unique Membership Test

Create another test:

			
import pytest
from sqlalchemy.exc import IntegrityError

Then:

			
def test_duplicate_membership_is_rejected() -> None:
    unique_value = uuid4().hex
    organization = Organization(
        name="Duplicate Membership Test",
        slug=f"duplicate-membership-{unique_value}",
    )
    user = User(
        email=f"duplicate-{unique_value}@example.com",
    )
    first_membership = Membership(
        organization=organization,
        user=user,
        role=ROLE_OWNER,
    )
    with SessionLocal() as db:
        db.add(first_membership)
        db.commit()
        duplicate_membership = Membership(
            organization_id=organization.id,
            user_id=user.id,
            role=ROLE_OWNER,
        )
        db.add(duplicate_membership)
        with pytest.raises(IntegrityError):
            db.commit()
        db.rollback()

		

This proves the database protects the membership invariant.

43. Why Test Database Constraints?

Application validation is important.

But database constraints provide the last line of defense.

Consider concurrent requests.

Two processes could both check:

Does membership already exist?

and both temporarily receive:

No

without a unique database constraint.

The database constraint guarantees correctness even under concurrency.

This pattern will become important for many CRM rules.

44. Add a Duplicate Organization Slug Test

We should also verify the organization slug uniqueness constraint.

Conceptually:

			
def test_duplicate_organization_slug_is_rejected() -> None:
    ...

Create two organizations with the same slug and confirm PostgreSQL raises:

IntegrityError

The same pattern can later be used for duplicate user email addresses.

45. Database Constraints Versus Business Validation

This introduces an important design principle.

We want both:

			
Application Validation
        +
Database Constraints

Application validation gives users useful error messages.

Database constraints guarantee data integrity.

For example:

			
Application:
"An organization with this slug already exists."

while PostgreSQL guarantees:

UNIQUE slug

We should not rely exclusively on either layer.

46. Build a Small Repository Layer

Part 6 is also a good point to establish the first repository pattern.

Create:

app/modules/organizations/repository.py

Run:

New-Item app\modules\organizations\repository.py -ItemType File

Add:

			
from uuid import UUID
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.modules.organizations.model import Organization
class OrganizationRepository:
    def __init__(self, db: Session) -> None:
        self.db = db
    def get_by_id(
        self,
        organization_id: UUID,
    ) -> Organization | None:
        statement = select(Organization).where(
            Organization.id == organization_id
        )
        return self.db.scalar(statement)
    def get_by_slug(
        self,
        slug: str,
    ) -> Organization | None:
        statement = select(Organization).where(
            Organization.slug == slug
        )
        return self.db.scalar(statement)
    def add(
        self,
        organization: Organization,
    ) -> Organization:
        self.db.add(organization)
        return organization

		

This is deliberately small.

47. Why Repositories Start Simple

Repositories should not become elaborate generic abstractions immediately.

We do not need:

			
GenericRepository[T]
AbstractRepositoryFactory
RepositoryRegistry

before three CRUD methods exist.

We start with a concrete repository that reflects real use cases.

Later patterns can be extracted if repetition becomes meaningful.

This follows our overall philosophy:

Earn abstractions through repeated requirements.

48. Create the User Repository

Create:

app/modules/users/repository.py

Add:

			
from uuid import UUID
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.modules.users.model import User
class UserRepository:
    def __init__(self, db: Session) -> None:
        self.db = db
    def get_by_id(
        self,
        user_id: UUID,
    ) -> User | None:
        statement = select(User).where(
            User.id == user_id
        )
        return self.db.scalar(statement)
    def get_by_email(
        self,
        email: str,
    ) -> User | None:
        statement = select(User).where(
            User.email == email
        )
        return self.db.scalar(statement)
    def add(
        self,
        user: User,
    ) -> User:
        self.db.add(user)
        return user

		

Again, simple and explicit.

49. Create the Membership Repository

Create:

app/modules/memberships/repository.py

Add:

			
from uuid import UUID
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.modules.memberships.model import Membership
class MembershipRepository:
    def __init__(self, db: Session) -> None:
        self.db = db
    def get_by_user_and_organization(
        self,
        user_id: UUID,
        organization_id: UUID,
    ) -> Membership | None:
        statement = select(Membership).where(
            Membership.user_id == user_id,
            Membership.organization_id == organization_id,
        )
        return self.db.scalar(statement)
    def add(
        self,
        membership: Membership,
    ) -> Membership:
        self.db.add(membership)
        return membership

		

This method will later become central to authorization and tenant context.

50. The First Tenant Lookup

We can now perform:

			
User ID
   +
Organization ID
        │
        ▼
MembershipRepository
        │
        ▼
Membership

		

This allows Quorentra to answer:

Does this user belong to this organization?

Later the same membership can answer:

What role does the user have in that organization?

That is the foundation of tenant-aware authorization.

51. Do Not Build API Endpoints Yet

We now have domain models and repositories.

Why not immediately add:

			
POST /organizations
POST /users
POST /memberships

Because those endpoints are not independent CRUD resources from a product perspective.

Our intended user journey is:

			
Register
   ↓
Create User
   ↓
Create Organization
   ↓
Create Owner Membership

		

That belongs to an onboarding use case.

If we expose raw CRUD APIs too early, we may end up designing the product around database tables rather than business workflows.

Part 6 therefore establishes persistence only.

The application-service and API flow will come next.

52. The First Business Use Case

The next application-level operation will effectively be:

register_new_organization_owner()

which performs:

			
Create User
    +
Create Organization
    +
Create Membership(role=owner)

		

inside one transaction.

That is much more meaningful than three unrelated HTTP requests.

This is a good example of why application services matter.

53. Transaction Boundary Preview

The future registration transaction should conceptually be:

			
BEGIN
Create User
Create Organization
Create Owner Membership
COMMIT

		

If any step fails:

ROLLBACK

We do not want:

			
User created
Organization failed
Membership missing

leaving half-finished onboarding state.

This is exactly the kind of problem the application-service layer will solve.

54. Add an Organization Service Skeleton

We can establish the module pattern without implementing onboarding yet.

Create:

app/modules/organizations/service.py

Add:

			
from sqlalchemy.orm import Session
from app.modules.organizations.repository import (
    OrganizationRepository,
)
class OrganizationService:
    def __init__(self, db: Session) -> None:
        self.db = db
        self.organizations = OrganizationRepository(db)

		

This appears minimal because it is.

The purpose is to establish the dependency direction:

			
Service
   │
   ▼
Repository
   │
   ▼
SQLAlchemy

		

Actual use cases arrive in the next article.

55. Update the Backend Structure

The relevant module tree should now resemble:

			
app/
├── db/
│   ├── base.py
│   ├── mixins.py
│   ├── models.py
│   └── ...
│
└── modules/
    ├── memberships/
    │   ├── __init__.py
    │   ├── constants.py
    │   ├── model.py
    │   └── repository.py
    │
    ├── organizations/
    │   ├── __init__.py
    │   ├── model.py
    │   ├── repository.py
    │   └── service.py
    │
    └── users/
        ├── __init__.py
        ├── model.py
        └── repository.py

		

This becomes the template for future modules.

56. Run the Full Test Suite

Run:

python -m pytest

Expected tests should include:

			
health API
tenant model persistence
duplicate membership protection

All should pass.

If not, do not continue.

The tenant foundation must be stable before authentication or CRM data is added.

57. Verify Alembic State

Run:

python -m alembic current

Then:

python -m alembic history

You should see both:

initialize database foundation

and:

add organizations users memberships

The database migration history is now beginning to reflect the actual domain architecture.

58. Verify the Schema Through SQL

Using pgAdmin Query Tool, you can verify the tables:

			
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

You should see:

			
alembic_version
memberships
organizations
users

You can also inspect relationships:

			
SELECT
    m.id,
    o.name AS organization_name,
    u.email,
    m.role
FROM memberships AS m
JOIN organizations AS o
    ON o.id = m.organization_id
JOIN users AS u
    ON u.id = m.user_id;

		

This should display any records created by your integration tests.

59. Why Tenant IDs Will Later Appear on CRM Records

It might seem that a company could determine its organization indirectly.

For example:

Company → Created By User → Membership → Organization

That would be a mistake.

Future CRM tables should carry explicit organization ownership.

For example:

			
Company
├── id
├── organization_id
├── name
└── ...

		

Likewise:

			
Contact
├── organization_id
└── ...

and:

			
Opportunity
├── organization_id
└── ...

This makes tenant filtering explicit and efficient.

60. Tenant Isolation Is Not Implemented Yet

We now have the data model required for tenant isolation.

But we do not yet have:

			
authenticated user
current organization
request tenant context
authorization middleware

Therefore we should be precise:

Part 6 establishes tenant ownership architecture. It does not yet enforce tenant isolation at the HTTP request layer.

That enforcement will come after authentication and onboarding are implemented.

61. Security Boundary Preview

Eventually every protected request will resemble:

			
Request
   │
   ▼
Authenticated User
   │
   ▼
Current Organization
   │
   ▼
Membership Lookup
   │
   ▼
Role / Permissions
   │
   ▼
Tenant-Scoped Service

		

For example:

GET /companies

must never become:

SELECT * FROM companies;

It must effectively become:

			
Companies
WHERE organization_id = current_organization

The tenant context created from membership is what makes that possible.

62. ChatGPT Will Use the Same Tenant Boundary

This architecture also matters later when we connect ChatGPT.

A ChatGPT request such as:

Show me all my open opportunities.

must not bypass organization context.

The future path will be:

			
ChatGPT
   │
   ▼
MCP Tool
   │
   ▼
Authenticated User
   │
   ▼
Organization Membership
   │
   ▼
Tenant Context
   │
   ▼
OpportunityService

		

That means the identity model we are building now is directly relevant to the eventual ChatGPT-native architecture.

63. Why MCP Must Not Supply Arbitrary Organization IDs

When MCP arrives, we should avoid dangerous tool designs like:

list_companies(organization_id="anything")

where the AI can freely select tenant identifiers.

Instead, authenticated tenant context should be resolved by Quorentra.

Conceptually:

			
MCP request
   │
   ▼
Resolved User
   │
   ▼
Resolved Tenant
   │
   ▼
Authorized Service

		

The AI should request a capability.

Quorentra should determine which data scope is permitted.

This is another reason the membership architecture must exist early.

64. Update the Application Version

Open:

app/core/constants.py

Change:

APP_VERSION = "0.0.3"

to:

APP_VERSION = "0.0.4"

Start Quorentra:

python -m uvicorn app.main:app --reload

Open:

http://127.0.0.1:8000/api/v1/health

Expected:

			
{
  "status": "healthy",
  "service": "quorentra-api",
  "version": "0.0.4",
  "database": "connected"
}

		

The application and database foundation remain healthy after the schema expansion.

65. Quorentra 0.0.4

We can now describe the system state as:

			
Repository              ✓
FastAPI                 ✓
PostgreSQL              ✓
SQLAlchemy              ✓
Alembic                 ✓
Organizations           ✓
Users                   ✓
Memberships             ✓
UUID identity           ✓
Timestamps              ✓
Role storage            ✓
ORM relationships       ✓
Repository pattern      ✓
Persistence tests       ✓
Registration            -
Authentication          -
Tenant request context  -
RBAC enforcement        -
React                   -
Companies               -
Contacts                -
Opportunities           -
MCP                     -
ChatGPT                 -
AI                      -

		

This is an important architectural checkpoint.

Quorentra now understands:

who can belong to whom.

66. Acceptance Criteria

Part 6 is complete when:

			
✓ app/modules exists
✓ Organization model exists
✓ User model exists
✓ Membership model exists
✓ UUID primary-key mixin exists
✓ Timestamp mixin exists
✓ organization slug is unique
✓ user email is unique
✓ memberships connect users and organizations
✓ duplicate membership is prevented
✓ membership contains role
✓ owner/admin/member constants exist
✓ SQLAlchemy relationships work
✓ repositories exist
✓ models register with Base.metadata
✓ Alembic detects all three tables
✓ generated migration is reviewed
✓ migration applies successfully
✓ PostgreSQL contains all three tables
✓ persistence integration tests pass
✓ Quorentra reports version 0.0.4
✓ health endpoint still passes

		

Most importantly:

Organizations, users, and memberships must exist as a stable domain foundation before we build onboarding or authentication.

67. What We Deliberately Did Not Build

Part 6 did not implement:

passwords;
registration API;
login;
JWT tokens;
current-user dependency;
current-organization dependency;
invitation workflows;
complete RBAC;
organization settings;
companies;
contacts.

Those concerns build on the model created here.

The current architecture is:

			
PostgreSQL
   │
   ├── organizations
   ├── users
   └── memberships

		

The next architecture will introduce behavior around those entities.

68. Why Registration Comes Before Login

A login system is meaningless if users cannot first enter the system.

Therefore the next practical workflow is not:

Login

It is:

			
Register
   │
   ├── Create User
   ├── Create Organization
   └── Create Owner Membership

		

Only once that flow works do we add:

			
Password verification
JWT tokens
Authenticated requests

This continues our vertical implementation approach.

69. The Next Complete Transaction

Part 7 should make this possible:

POST /api/v1/auth/register

with input such as:

			
{
  "email": "owner@example.com",
  "password": "secure-password",
  "display_name": "CRM Owner",
  "organization_name": "Acme Consulting"
}

		

Quorentra will then perform:

			
Validate Input
      ↓
Normalize Email
      ↓
Generate Organization Slug
      ↓
Hash Password
      ↓
Create User
      ↓
Create Organization
      ↓
Create Owner Membership
      ↓
Commit Transaction

		

The entire operation must either succeed or fail atomically.

That will become our first true application-service use case.

70. The Next Version

At the end of the next stage, we should be able to say:

Quorentra 0.0.5

supports:

			
User Registration
      +
Organization Creation
      +
Owner Membership

		

That will be the first end-user business workflow implemented in the backend.

71. Development Progress

Our path now looks like:

			
Part 3
Repository
   ✓
   │
Part 4
FastAPI
   ✓
   │
Part 5
PostgreSQL
   ✓
   │
Part 6
Tenant Domain
   ✓
   │
Part 7
Registration + Onboarding
   ↓
   │
Part 8
Authentication
   ↓
   │
Part 9
Tenant Context
   ↓
   │
Part 10
RBAC
   ↓
   │
CRM Modules

		

This sequence is deliberate.

We are building Quorentra from its trust boundary outward.

72. Next Article

In Part 7, we will build:

User Registration and Organization Onboarding

We will introduce:

password hashing;
registration request schemas;
registration response schemas;
email normalization;
organization slug generation;
duplicate-email detection;
duplicate-slug handling;
user repository enhancements;
organization repository enhancements;
membership creation;
transaction boundaries;
application service orchestration;
POST /api/v1/auth/register;
registration integration tests;
failure rollback testing.

The architecture will evolve from:

			
Organization
User
Membership

to:

			
Registration Request
        │
        ▼
Onboarding Service
        │
    ┌───┼─────────┐
    ▼   ▼         ▼
  User Organization Membership
    │   │         │
    └───┴────┬────┘
             ▼
         PostgreSQL

		

This will be Quorentra’s first complete business transaction.

For the first time, someone will be able to enter the system and establish a tenant.

That is the next major step toward the Minimum Viable CRM.

Quorentra CRM Multi-Tenant Domain: Building from Zero — Part 6

1. Introduction

2. Why Organizations Come First

3. Why Users and Organizations Are Separate

4. Why Membership Is More Than a Join Table

5. Starting Checkpoint

6. The First Domain Modules

7. Why We Are Not Creating models.py for Everything

8. Shared Model Concerns

9. Why UUID Primary Keys?

10. Why PostgreSQL UUID Type?

11. Why Server-Side Timestamps?

12. Build the Organization Model

13. Organization Fields

14. Why Keep Organization Small?

15. Build the User Model

16. Why Email Is Globally Unique

17. Normalize Email Later

18. Build the Membership Model

19. The Domain Relationship

20. Why the Unique Membership Constraint Matters

21. Initial Role Model

22. Why Role Belongs to Membership

23. Make Alembic Aware of the Models

24. Import Models in Alembic

25. Verify Model Metadata

26. Generate the Migration

27. Always Inspect Auto-Generated Migrations

28. Check Foreign Keys

29. Apply the Migration

30. Inspect PostgreSQL

31. Inspect the Organization Table

32. Inspect the User Table

33. Inspect the Membership Table

34. Add Model-Level Role Constants

35. Why Not Create Full RBAC Yet?

36. Add Persistence Tests

37. Build the First Persistence Test

38. Why Use Unique Test Values?

39. Run the Integration Test

40. Inspect the Test Data

41. Add Relationship Assertions

42. Add a Unique Membership Test

43. Why Test Database Constraints?

44. Add a Duplicate Organization Slug Test

45. Database Constraints Versus Business Validation

46. Build a Small Repository Layer

47. Why Repositories Start Simple

48. Create the User Repository

49. Create the Membership Repository

50. The First Tenant Lookup

51. Do Not Build API Endpoints Yet

52. The First Business Use Case

53. Transaction Boundary Preview

54. Add an Organization Service Skeleton

55. Update the Backend Structure

56. Run the Full Test Suite

57. Verify Alembic State

58. Verify the Schema Through SQL

59. Why Tenant IDs Will Later Appear on CRM Records

60. Tenant Isolation Is Not Implemented Yet

61. Security Boundary Preview

62. ChatGPT Will Use the Same Tenant Boundary

63. Why MCP Must Not Supply Arbitrary Organization IDs

64. Update the Application Version

65. Quorentra 0.0.4

66. Acceptance Criteria

67. What We Deliberately Did Not Build

68. Why Registration Comes Before Login

69. The Next Complete Transaction

70. The Next Version

71. Development Progress

72. Next Article

Share this:

Like this:

Quorentra

Contact

Menu

Discover more from Quorentra

7. Why We Are Not Creating `models.py` for Everything