Blueprint — Members Suite

Architect Agent 3 of the Pancha · 2026-05-02 · slug members

1. Module summary

The Members Suite presents the institution's people in seven role-driven dashboards — Founders, Substantial Donors, Recurring Donors, Service Recipients, Casual Associates, Volunteers, and Staff Index — without flattening them into seven separate registries. The single canonical members table (already live, with name, gothra, nakshatram, dob, photo, family fields) remains the source of truth. This module adds categorization: a member_categorizations table that lets one person belong to multiple categories simultaneously (a recurring donor who also volunteers, who is also the parent of a Vedapathashala student). Auto-categorization rules pull from Vitta donations, recurring payment events, and Staff Details to keep segments fresh. Communication preferences, mailing-list subscriptions, donor-aggregate denormalisation, and a volunteer skill inventory complete the suite. Staff Index is a read-only join — staff are still owned by the Staff Details module (sibling blueprint).

2. Data model

A categorization table is preferred over extending tag taxonomy because:

1. Tags are flat strings on tags, with no schema for since_date, threshold_met evidence, or recognition_level. Donor categorization needs structured fields.
2. Categories drive workflows (auto-categorize on donation threshold, auto-decategorize on staff exit) — workflow logic should not pivot on string equality across a tag table that is also used for ad-hoc tags.
3. Tags remain useful for ad-hoc segmentation (mysuru-camp-2025, prasadam-volunteer-festivals-only) and the suite still consumes them via the existing Tag model.

# src/aayojana/models/member_categorization.py
from datetime import date, datetime
from sqlalchemy import (
    Boolean, Date, DateTime, ForeignKey, Index, Integer, JSON,
    Numeric, String, Text, UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from aayojana.models.base import AuditMixin, Base, TenantMixin, uuid4_str


class MemberCategorization(Base, AuditMixin, TenantMixin):
    """One row per (member, category). A person can hold many.

    `category` taxonomy (closed set):
      founder · substantial_donor · recurring_donor ·
      service_recipient · casual_associate · volunteer · staff_index
    """
    __tablename__ = "member_categorizations"
    __table_args__ = (
        UniqueConstraint("member_id", "category", name="uq_member_category"),
        Index("ix_member_cat_category_active", "category", "active"),
        Index("ix_member_cat_tenant_category", "tenant_id", "category"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False
    )
    category: Mapped[str] = mapped_column(String(24), nullable=False)
    since_date: Mapped[date] = mapped_column(Date, nullable=False)
    until_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    active: Mapped[bool] = mapped_column(Boolean, server_default="true")
    # auto-categorisation evidence — e.g. {"trigger":"vitta.donor_aggregate",
    #   "threshold":100000, "actual":135000, "fy":"FY2025-26"}
    threshold_met: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    # bronze | silver | gold | platinum | founder | (free-text per tenant)
    recognition_level: Mapped[str | None] = mapped_column(String(40), nullable=True)
    # automatic | manual | imported
    source: Mapped[str] = mapped_column(String(16), nullable=False, server_default="manual")
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    categorized_by: Mapped[int | None] = mapped_column(
        Integer, ForeignKey("users.id"), nullable=True
    )

    member: Mapped["Member"] = relationship()  # type: ignore[name-defined]


class MemberSubscriptionList(Base, AuditMixin, TenantMixin):
    """A named mailing list (Monthly Newsletter, Annadana Donors, FCRA
    Donors, Festival Devotees). Owned here in Members Suite because
    list membership is a member attribute; the **content** of each
    issue is owned by Newsletter (Agent 5)."""
    __tablename__ = "member_subscription_lists"
    __table_args__ = (
        UniqueConstraint("tenant_id", "code", name="uq_subscription_list_code"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    code: Mapped[str] = mapped_column(String(40), nullable=False)
    name: Mapped[str] = mapped_column(Text, nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    # email | postal | whatsapp | multi
    primary_channel: Mapped[str] = mapped_column(String(16), nullable=False)
    requires_double_optin: Mapped[bool] = mapped_column(Boolean, server_default="true")
    active: Mapped[bool] = mapped_column(Boolean, server_default="true")


class MemberSubscription(Base, AuditMixin, TenantMixin):
    """member ↔ subscription_list with opt-in lineage."""
    __tablename__ = "member_subscriptions"
    __table_args__ = (
        UniqueConstraint("member_id", "list_id", name="uq_member_subscription"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False, index=True
    )
    list_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("member_subscription_lists.id", ondelete="CASCADE"),
        nullable=False, index=True,
    )
    # pending_optin | active | unsubscribed | bounced
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending_optin")
    optin_token: Mapped[str | None] = mapped_column(String(64), nullable=True)
    optin_confirmed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    optout_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    optout_reason: Mapped[str | None] = mapped_column(Text, nullable=True)


class MemberCommunicationPreferences(Base, AuditMixin, TenantMixin):
    """Per-channel opt-in flags + quiet hours.

    1:1 with members. Read by Comms (Agent 5) before every send.
    """
    __tablename__ = "member_communication_preferences"
    __table_args__ = (
        UniqueConstraint("member_id", name="uq_member_comm_prefs"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False
    )
    email_optin: Mapped[bool] = mapped_column(Boolean, server_default="true")
    whatsapp_optin: Mapped[bool] = mapped_column(Boolean, server_default="false")
    sms_optin: Mapped[bool] = mapped_column(Boolean, server_default="false")
    postal_optin: Mapped[bool] = mapped_column(Boolean, server_default="true")
    receipt_only: Mapped[bool] = mapped_column(Boolean, server_default="false")
    # JSON of {fri_evening:false, festival_only:true, languages:["sa","kn","en"]}
    rules: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    preferred_language: Mapped[str | None] = mapped_column(String(8), nullable=True)
    last_updated_by_member_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)


class MemberDonorAggregate(Base, AuditMixin, TenantMixin):
    """Denormalised donor totals refreshed nightly (or on payment event).
    Used by Substantial-Donor auto-categorization without scanning
    the full transactions table per request.
    """
    __tablename__ = "member_donor_aggregates"
    __table_args__ = (
        UniqueConstraint("member_id", "fund_code", "fy_label",
                         name="uq_donor_agg_member_fund_fy"),
        Index("ix_donor_agg_total", "tenant_id", "lifetime_total"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False, index=True
    )
    # general | annadana | building | fcra | endowment | _all_
    fund_code: Mapped[str] = mapped_column(String(20), nullable=False)
    fy_label: Mapped[str] = mapped_column(String(12), nullable=False)  # FY2025-26
    fy_total: Mapped[str] = mapped_column(Numeric(14, 2), nullable=False, server_default="0")
    lifetime_total: Mapped[str] = mapped_column(Numeric(14, 2), nullable=False, server_default="0")
    last_donation_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    donation_count: Mapped[int] = mapped_column(Integer, server_default="0")
    is_recurring: Mapped[bool] = mapped_column(Boolean, server_default="false")
    razorpay_subscription_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
    refreshed_at: Mapped[datetime] = mapped_column(DateTime, nullable=False)


class VolunteerSkill(Base, AuditMixin, TenantMixin):
    """Per-volunteer skill row. Only members with an active
    `member_categorizations.category='volunteer'` get rows here."""
    __tablename__ = "volunteer_skills"
    __table_args__ = (
        UniqueConstraint("member_id", "skill_code", name="uq_volunteer_skill"),
        Index("ix_volunteer_skill_code", "skill_code"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False, index=True
    )
    # Tenant-extensible catalog: driving · cooking · accounting ·
    # photography · sound · samagri_setup · manuscript · sanskrit_typing …
    skill_code: Mapped[str] = mapped_column(String(40), nullable=False)
    proficiency: Mapped[int] = mapped_column(Integer, server_default="3")  # 1..5
    # weekends | weekdays | full_time | events_only | remote
    availability: Mapped[str | None] = mapped_column(String(20), nullable=True)
    certified: Mapped[bool] = mapped_column(Boolean, server_default="false")
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class VolunteerEventParticipation(Base, AuditMixin, TenantMixin):
    """Roster log — populated when an Events Planner roster
    (Agent 4) is finalised. One row per (member, event)."""
    __tablename__ = "volunteer_event_participations"
    __table_args__ = (
        UniqueConstraint("member_id", "event_id", name="uq_volunteer_event"),
    )

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=uuid4_str)
    member_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("members.id", ondelete="CASCADE"), nullable=False, index=True
    )
    event_id: Mapped[str] = mapped_column(String(36), nullable=False, index=True)
    # FK to events table when Agent 4 lands; left soft for now
    role: Mapped[str | None] = mapped_column(String(63), nullable=True)
    hours: Mapped[int | None] = mapped_column(Integer, nullable=True)
    rating: Mapped[int | None] = mapped_column(Integer, nullable=True)  # 1..5
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

NOT redefined: Member.name, Member.dob, Member.photo, Member.gotram, Member.nakshatram_id, Profile.placeOfBirth etc. — those are already on members / profiles. The Members Suite reads them via the existing eager-load relationships.

3. Reuse map

The Members Suite does not introduce a parallel Member entity, parallel address store, parallel donor table, or parallel staff table. Every category dashboard is a projection over the existing canonical data plus the categorization layer.

4. API surface

Existing routes under /api/members (CRUD, search, photo) stay. New routes are additive:

UI pages (HTML, served from existing routers/members.py-adjacent template includes): - /admin/members — current list, gains category-filter chips - /admin/members/{id} — current detail, gains tabs: Donations · Subscriptions · Comm-prefs · Volunteer-skills · Categories - /admin/segments/founders … /admin/segments/staff — one dashboard per category - /admin/segments/_dashboard — overview tile grid - /admin/volunteers/match — skill-matcher tool

5. Service layer

src/aayojana/services/categorization_service.py

async def categorize_member(
    db: AsyncSession, member_id: str, category: str,
    threshold_data: dict | None = None,
    evidence: str | None = None,
    recognition_level: str | None = None,
    source: str = "manual",
    actor: User = ...,
) -> MemberCategorization:
    """Idempotent upsert. If a row already exists for (member, category):
       - active=True → no-op (return existing)
       - active=False → reactivate, set since_date=today, push old row's
         until_date forward; emit 'member.recategorized' audit event.
    Validates `category` against the closed enum. Writes audit_event."""

async def decategorize_member(
    db: AsyncSession, member_id: str, category: str, reason: str, actor: User
) -> MemberCategorization:
    """Sets active=False, until_date=today, notes=reason. Emits
    'member.decategorized' audit event."""

async def compute_substantial_donor_status(
    db: AsyncSession, tenant_id: str, member_id: str,
    since_date: date | None = None,
) -> bool:
    """Reads member_donor_aggregates (already nightly-refreshed).
    Returns True iff lifetime_total ≥ tenant.substantial_donor_threshold
    (default ₹1,00,000; tenant-overridable — see Open Q #3).
    If True and no active categorization exists, calls categorize_member
    automatically with source='automatic'."""

async def refresh_donor_aggregates(
    db: AsyncSession, tenant_id: str,
    only_member_id: str | None = None,
) -> int:
    """Nightly job. SQL aggregates over transactions grouped by
    (member, fund, FY). Updates member_donor_aggregates with merge
    semantics. Returns rows affected. Triggered by a scheduled task
    or by payment_webhooks on every confirmed donation."""

async def auto_categorize_recurring_donor(
    db: AsyncSession, payment_event: PaymentEvent
) -> MemberCategorization | None:
    """Called from payment_webhooks router when event_type starts with
    'subscription.'. Resolves the subscription's customer → member
    → upserts categorization (active or paused based on event)."""

async def list_segment(
    db: AsyncSession, tenant_id: str, category: str,
    limit: int, offset: int, sort: str | None,
) -> tuple[list[Member], int]:
    """Returns (members, total). Joins member_categorizations and,
    for substantial_donors, also joins member_donor_aggregates so
    the UI can show lifetime_total without a second query."""

async def get_donor_history_merged(
    db: AsyncSession, member_id: str
) -> dict:
    """Returns:
      { transactions:[…raw rows…],
        payment_events:[…razorpay subs…],
        aggregates:[…by fund × FY…],
        certificates:[…80G placeholder, populated by Vitta later…] }"""

async def update_communication_preferences(
    db: AsyncSession, member_id: str, prefs: PrefsIn, actor: User
) -> MemberCommunicationPreferences:
    """Upsert. Sets last_updated_by_member_at when actor==self."""

async def subscribe_member(
    db: AsyncSession, member_id: str, list_id: str, actor: User
) -> MemberSubscription:
    """Creates row with status=pending_optin, generates token,
    enqueues Comms 'subscription.optin' email. Skips if list does
    not require double-opt-in (then sets status=active immediately)."""

async def confirm_subscription_optin(
    db: AsyncSession, token: str
) -> MemberSubscription:
    """Public-facing. Sets status=active, optin_confirmed_at=now()."""

async def find_volunteers_for_event(
    db: AsyncSession, tenant_id: str, event_id: str,
    skills_required: list[str], on_date: date | None = None,
    min_proficiency: int = 3,
) -> list[Member]:
    """Subset of members where active categorization=volunteer AND
    every required skill_code exists at proficiency ≥ min_proficiency.
    Excludes members with a conflicting same-day participation row.
    Ranks by historical rating average."""

async def link_staff_index(
    db: AsyncSession, member_id: str
) -> MemberCategorization:
    """Called by staff_service.onboard_staff. Creates a
    category=staff_index categorization so the Staff Index dashboard
    automatically reflects the new hire without manual sync."""

6. UI / Templates

Templates under src/aayojana/templates/admin/members_suite/ (new directory). The existing templates/admin/members.html is extended in-place to add category chips.

Detail-page tab order on /admin/members/{id}: Personal · Addresses · Profile · Donations · Sevas · Subscriptions · Comm-prefs · Categories · Volunteer (only if volunteer category) · Staff (only if staff_index category — opens Staff Details).

7. Migration plan

(Numbering coordinates with Staff Details — Staff uses 0016–0018 of its own track; if migrations are linearised across the repo, allocate Members 0019–0021. The submitted plan assumes Staff and Members run as parallel branches that converge at 0022.)

Each migration includes: 1. Backfill: INSERT INTO member_categorizations (member_id, category='casual_associate', ...) FROM members WHERE NOT EXISTS … so every existing member starts in casual_associate and is promoted by subsequent jobs. 2. A nightly cron stub that calls refresh_donor_aggregates is provisioned as part of 0017.

8. Cross-module dependencies

Reads from - transactions — donation history (Vitta will eventually own these accounting-side; the transactions table is shared) - payment_events — Razorpay subscription state for recurring-donor classification - sevas — service-recipient identification - staff_profiles (Staff blueprint) — staff_index dashboard read-only join - tags — ad-hoc segments overlayed on category dashboards - awardees — founder evidence at import time - dispatch_records — Communication History tab

Writes to - Audit Trail (Agent 2) — every categorize / decategorize / preferences change emits member.category.* events - Comms (Agent 5) — double-opt-in confirmations, anniversary greetings (birthDate + birthYear from members), 80G dispatch reminders (handed to Vitta, dispatched by Comms) - Newsletter (Agent 5) — every send pulls subscriber list via member_subscriptions joined to member_subscription_lists - Events Planner (Agent 4) — find_volunteers_for_event is the public API; Events writes back via volunteer_event_participations - Vitta Fin (Agent 1) — substantial-donor threshold reads org-level config; donor aggregate refresh consumes Vitta-confirmed transactions - Staff Details (sibling) — link_staff_index ensures staff_index category always tracks staff_profiles 1:1

9. Implementation phases

Phase A — categorization scaffold + manual workflow (one sprint) - Migration 0016 - MemberCategorization model + categorize/decategorize CRUD - Category-filter chips on existing /admin/members - Backfill: every existing member → casual_associate - Per-category dashboards with manual promotion buttons

Phase B — auto-categorization + comm-prefs + subscriptions (two sprints) - Migrations 0017 - member_donor_aggregates nightly refresh job - compute_substantial_donor_status + auto-promotion - Recurring-donor auto-categorization wired into payment_webhooks - Communication preferences editor - Subscription lists with double-opt-in flow - Newsletter hand-off API for Agent 5

Phase C — volunteers + staff index + match tool (one sprint) - Migration 0018 - Volunteer skill matrix UI - find_volunteers_for_event API + match-tool template - Staff Index dashboard (read-only join with Staff blueprint) - link_staff_index invoked from staff onboarding (cross-module) - Donor-history merged view

10. Open questions

1. Multi-category membership. Confirmed yes — (member_id, category) unique not (member_id). A donor-volunteer is two active rows. Recommend ratify.
2. Substantial-donor threshold definition. Fixed amount, top-N percentile per fund, or tenant-configurable? Proposed: tenant-configurable per fund (organisations.substantial_donor_thresholds JSON column added in migration 0017), default ₹1,00,000 lifetime to General. Decision needed.
3. Founder vs Substantial Donor rank. If a person qualifies as both, do dashboards show them under both or only the higher? Proposed: both — dashboards are projections, not partitions. Decision needed.
4. Casual Associate as default. Should every member without an explicit category land in casual_associate, or in no category at all? Proposed: explicit casual_associate row created on member insert (via DB trigger or service hook) — keeps dashboard counts consistent. Decision needed.
5. Recurring donor lapse handling. When a Razorpay subscription pauses, should we decategorize after N days (auto) or wait for explicit cancellation? Proposed: decategorize after 90 days of no successful charge; keep until_date so reactivation is detectable. Decision needed.
6. Service recipient lifetime. Once-off seva (one-time kalyanotsavam) — does the member remain service_recipient forever? Proposed: rolling 5-year window (since last seva). Decision needed.
7. Volunteer skill catalog. Tenant-extensible JSON catalog or fixed enum? Proposed: tenant-extensible (default seed: driving, cooking, accounting, photography, sound, samagri_setup, manuscript, sanskrit_typing, chandas_recitation). Decision needed.
8. Staff Index sync direction. Should Staff Details writes always trigger a Members Suite categorization upsert (preferred — automatic), or should the Members Suite poll? Proposed: automatic via link_staff_index call inside onboard_staff. Decision needed.
9. Communication preferences inheritance. If a member opts out of email globally, do they still receive transactional emails (donation receipts, 80G certs)? Proposed: receipt_only flag — opt-out from campaigns but transactional always allowed (legal requirement). Decision needed.
10. DPDP Act / GDPR posture. Double-opt-in mandatory for newsletters but not for transactional Comms — codified via requires_double_optin on each list. Are we comfortable with this default? Decision needed.