agilentics / boiler 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
"""SQLAlchemy declarations.

Alembic autogenerate is wired to this Base (see alembic/env.py), so adding a
model here and running `alembic revision --autogenerate -m "..."` is the whole
workflow.

What the skeleton lays down is tenancy and nothing else:

    Organization    a customer - the unit everything a customer composes
                    belongs to
    User            a person (or a machine account) who signs in
    Membership      what one user may do in one organisation

Your own tables go below the marker at the bottom. The one rule worth keeping:
anything a customer owns carries `org_id`, so app.core.context.scope_to_org can
narrow a query to the caller's tenant in one place instead of at every call site.
"""
from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    ForeignKey,
    Integer,
    String,
    UniqueConstraint,
    func,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import deferred, relationship
from sqlalchemy.sql import expression

Base = declarative_base()


# --- tenancy ----------------------------------------------------------------


class Organization(Base):
    """A customer."""

    __tablename__ = "organizations"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(120), nullable=False)

    # The stable handle used in URLs and config, so renaming a customer does not
    # break every reference to them.
    slug = Column(String(60), nullable=False, unique=True)

    created_at = Column(DateTime, nullable=False, server_default=func.now())

    def to_dict(self) -> dict:
        return {"id": self.id, "name": self.name, "slug": self.slug}

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


class User(Base):
    """A person (or a machine account) who signs in.

    Separate from Organization because the two are many-to-many: a consultant
    belongs to several customers, and a customer has several people. Membership
    carries the relationship and the role within it.
    """

    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(254), nullable=False, unique=True)
    name = Column(String(120), nullable=True)

    # A stable, opaque public identifier - what an API payload names instead of
    # the email address. Minted once at creation and never reused, so it survives
    # the user changing address and leaks nothing if it appears in a URL.
    account_id = Column(String(128), nullable=False, unique=True)

    # Null means this account signs in through an external identity provider
    # only. Storing no hash at all is the honest representation of that, and it
    # is what makes adding OIDC later a new sign-in path rather than a migration.
    #
    # deferred() so the hash is not loaded by every query that touches a user.
    password_hash = deferred(Column(String(255), nullable=True))

    # The Google account this user may also sign in with, as Google's opaque
    # subject id (the `sub` claim), or null if they never linked one. Unique, so
    # one Google identity maps to one account. Google verifies the email, which
    # is what makes linking it to an existing address safe.
    google_sub = Column(String(255), nullable=True, unique=True)

    is_active = Column(Boolean, nullable=False, server_default=expression.true(),
                       default=True)
    created_at = Column(DateTime, nullable=False, server_default=func.now())
    last_login_at = Column(DateTime, nullable=True)

    # Brute-force throttling. Kept on the row rather than in process memory
    # because the app scales horizontally - an in-memory counter resets every
    # deploy and is invisible to the other instances.
    failed_logins = Column(Integer, nullable=False, server_default="0", default=0)
    locked_until = Column(DateTime, nullable=True)

    memberships = relationship(
        "Membership", back_populates="user", cascade="all, delete-orphan"
    )

    def to_dict(self) -> dict:
        """Never includes the hash. There is no view that needs it, and the
        surest way to keep it out of a response is to keep it out of here."""
        return {"id": self.id, "email": self.email, "name": self.name,
                "account_id": self.account_id, "is_active": self.is_active}

    def __repr__(self) -> str:
        return f"<User {self.id} {self.email!r}>"


class Membership(Base):
    """What one user may do in one organisation.

    The role here is what a Principal's scopes are derived from, so this table is
    the answer to "may this caller do this" without the answer depending on which
    façade the request arrived through.
    """

    __tablename__ = "memberships"
    __table_args__ = (
        UniqueConstraint("user_id", "org_id", name="uq_membership_user_org"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"),
                     nullable=False, index=True)
    org_id = Column(Integer, ForeignKey("organizations.id", ondelete="CASCADE"),
                    nullable=False, index=True)

    # owner | admin | member | viewer. Mapped to scopes in app/core/auth.py
    # rather than stored as scopes, so tightening what "member" means is one edit
    # instead of a migration over every row.
    role = Column(String(20), nullable=False, default="member")

    created_at = Column(DateTime, nullable=False, server_default=func.now())

    user = relationship("User", back_populates="memberships")
    organization = relationship("Organization")

    def __repr__(self) -> str:
        return f"<Membership user={self.user_id} org={self.org_id} {self.role!r}>"


# --- your models ------------------------------------------------------------
# Carry `org_id` on anything a customer owns, and query it through
# app.core.context.scope_to_org.