Skip to content
Engineering

Entity Relationship Diagram - Operations Schema

The operations schema encapsulates the sovereign B2B SaaS platform monetization, Product-Led Growth (PLG) mechanics, subscription lifecycle, corporate commercial contracts, tenant quotas, feature entitlement gates, and Brazilian billing reconciliation (Central Bank PIX and Credit Card tokenization).

All entities and services in this context are engineered within dotnet/src/Operations/Gercia.Operations and administered by internal GERCIA staff via the dedicated backoffice portal ops.gercia.com.br (ops.gercia.dev).

1. Domain Aggregates & Business Invariants

Section titled “1. Domain Aggregates & Business Invariants”

1. The Commercial Catalog (Plan & FeatureEntitlement)

Section titled “1. The Commercial Catalog (Plan & FeatureEntitlement)”
  • Shared Catalog Isolation: The commercial plans catalog (plans) and their feature entitlement gates (plan_feature_entitlements) are universal platform definitions. They do not implement IMustHaveTenant and are readable across all tenant sessions while writable exclusively by GERCIA operations administrators.
  • Tier Segregation: Supports discrete vertical and volume tiers:
    • FREEMIUM: Default tier for individual medical, dental, and veterinary practitioners (up to 1 professional, limited patient records, community support).
    • PROFESSIONAL: Solo practitioners and small offices requiring digital signatures, Cloud Storage, and WhatsApp notifications.
    • CLINIC: Multi-professional clinics with reception desks, multi-room calendars, and analytical reporting.
    • ENTERPRISE: Large multi-branch polyclinics, medical groups, and high-volume compounding pharmacy laboratories requiring dedicated edge printing, analytical balance streaming, and custom SLA.
    • PHARMACY_STANDARD & PHARMACY_ENTERPRISE: Dedicated compounding pharmacy plans with Cleanroom batch management and ANVISA RDC 67/2007 audit compliance.
    • VET_STANDARD & VET_CLINIC: Sovereign veterinary vertical plans with CFMV prescriptions, MAPA/SIPEAGRO reporting, and species taxonomy.

2. The 30-Day Reverse Trial & Automated Graceful Downgrade Invariant

Section titled “2. The 30-Day Reverse Trial & Automated Graceful Downgrade Invariant”
  • Zero Credit Card Friction: When a new healthcare clinic, polyclinic, veterinary practice, or compounding pharmacy registers, the platform automatically provisions an active tenant_subscriptions record configured for a 30-day “Reverse Trial” with all ENTERPRISE features unlocked.
  • Non-Destructive Automated Downgrade: At the conclusion of day 30 (trial_ends_at <= clock_timestamp()), if no signed commercial contract or active payment method is attached:
    1. The subscription status transitions automatically from TRIALING to ACTIVE under the FREEMIUM tier.
    2. No tenant data is destroyed, locked, or held hostage. All historical clinical records, dental odontograms, veterinary consultations, compounding formulas, and legal audit trails remain fully readable.
    3. Write operations and high-volume features (such as adding new practitioners beyond 1, high-throughput thermal batch printing, or automated SMS/WhatsApp alerts) are gated gracefully by the entitlement kernel until an upgrade occurs.

3. Corporate Commercial Contracts (CommercialContract)

Section titled “3. Corporate Commercial Contracts (CommercialContract)”
  • B2B Legal Accountability: Large clinic networks, franchise operations, and compounding pharmacies execute bilateral digital service contracts with GERCIA Engenharia de Software Ltda (CNPJ: 67.539.717/0001-87).
  • Digital Audit Integrity: Captures signatory legal civil identity (signer_cpf, signer_full_name), email, corporate tax ID (cnpj), corporate name (corporate_name), client IP address, digital signature timestamp (UTC), contract terms hash (SHA-256), agreed service level agreement (SLA), and custom billing discounts.

4. Billing Lifecycle, Invoices & Brazilian PIX Reconciliation

Section titled “4. Billing Lifecycle, Invoices & Brazilian PIX Reconciliation”
  • Billing Cycles: Subscriptions operate on MONTHLY or ANNUAL cycles with pre-billing invoice generation 5 days prior to period expiration.
  • Instant Central Bank PIX Reconciliation:
    • For each pending invoice, the billing engine generates a dynamic Brazilian Central Bank PIX payload (emv_payload and base64 QR code) with a cryptographic txid.
    • The payment webhook endpoint on api.gercia.com.br receives the instant bank notification, verifies the HMAC-SHA256 signature, asserts the end_to_end_id, marks the invoices status as PAID, and automatically extends the subscription period.
  • Tokenized Credit Cards: For recurring card payments, the platform integrates with PCI-DSS compliant Brazilian payment processors (storing only card brand, last 4 digits, expiration month/year, and gateway token). No raw card numbers ever touch GERCIA persistence.

2. Operations Relational Model

Section titled “2. Operations Relational Model”
erDiagram
    PLANS ||--o{ PLAN_FEATURE_ENTITLEMENTS : "defines gates"
    PLANS ||--o{ TENANT_SUBSCRIPTIONS : "subscribed by"
    TENANT_SUBSCRIPTIONS ||--o{ COMMERCIAL_CONTRACTS : "governed by"
    TENANT_SUBSCRIPTIONS ||--o{ INVOICES : "bills"
    INVOICES ||--o{ INVOICE_ITEMS : "details"
    INVOICES ||--o{ PAYMENT_TRANSACTIONS : "settles"
    TENANT_SUBSCRIPTIONS ||--o{ TENANT_QUOTA_USAGES : "tracks meter"

    PLANS {
        uuid id PK "UUIDv7 Temporal Primary Key"
        varchar code UK "Canonical code (e.g. FREEMIUM, PRO, CLINIC, ENTERPRISE)"
        varchar name "Marketing plan name"
        varchar description "Plan summary and tier description"
        varchar vertical "Target vertical (HEALTHCARE, DENTAL, VETERINARY, PHARMACY)"
        varchar billing_interval "Billing cycle (MONTHLY, ANNUAL)"
        integer price_in_cents "Base price in Brazilian Real cents (BRL)"
        integer trial_duration_days "Trial period duration (default 30 days)"
        boolean is_active "Plan availability flag"
        integer version "Optimistic concurrency token"
    }

    PLAN_FEATURE_ENTITLEMENTS {
        uuid id PK "UUIDv7 Temporal Primary Key"
        uuid plan_id FK "FK to parent Plan definition"
        varchar feature_key "Feature identifier (e.g. MAX_PRACTITIONERS, STORAGE_GB)"
        varchar value_type "Data type (BOOLEAN, NUMERIC, UNLIMITED)"
        varchar string_value "String representation of entitlement value"
        bigint numeric_value "Numeric limit (-1 for unlimited)"
        boolean is_enabled "Feature toggle switch"
    }

    TENANT_SUBSCRIPTIONS {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id UK "Tenant boundary ID (Unique 1:1 active subscription)"
        timestamptz created_at "Creation timestamp (UTC)"
        uuid created_by "Signer Master User ID"
        timestamptz last_modified_at "Last modification timestamp (UTC)"
        uuid last_modified_by "Mutating User ID"
        boolean is_deleted "Soft-delete flag"
        uuid plan_id FK "FK to active Plan"
        varchar status "Status (TRIALING, ACTIVE, PAST_DUE, SUSPENDED, CANCELED)"
        varchar billing_interval "Current billing interval (MONTHLY, ANNUAL)"
        timestamptz trial_starts_at "Reverse Trial initiation timestamp"
        timestamptz trial_ends_at "Reverse Trial expiration timestamp (30 days)"
        timestamptz current_period_starts_at "Current billing period start date"
        timestamptz current_period_ends_at "Current billing period end date"
        timestamptz canceled_at "Cancellation request timestamp"
        boolean cancel_at_period_end "Downgrade to Freemium at period end flag"
    }

    COMMERCIAL_CONTRACTS {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id FK "Tenant boundary ID (IMustHaveTenant)"
        timestamptz created_at "Contract creation timestamp (UTC)"
        uuid created_by "Creator Master User ID"
        timestamptz last_modified_at "Modification timestamp (UTC)"
        uuid last_modified_by "Mutating User ID"
        boolean is_deleted "Soft-delete flag"
        uuid subscription_id FK "FK to parent Tenant Subscription"
        varchar contract_number UK "Sequential legal contract identifier (e.g. CTR-2026-0042)"
        varchar corporate_name "Legal corporate entity name (Razao Social)"
        varchar cnpj "Federal Corporate Tax ID (14 digits)"
        varchar signer_cpf "Signatory CPF (11 sanitized digits)"
        varchar signer_full_name "Signatory civil name"
        varchar signer_email "Signatory email address"
        varchar signer_ip_address "IP address recorded at contract execution"
        varchar terms_version "Contract legal terms version (e.g. v2026.1)"
        varchar terms_hash "SHA-256 cryptographic digest of signed terms"
        timestamptz signed_at "Digital execution timestamp (UTC)"
        varchar status "Status (DRAFT, PENDING_SIGNATURE, ACTIVE, TERMINATED)"
        varchar sla_tier "Agreed SLA (STANDARD_99_5, MISSION_CRITICAL_99_9)"
        integer discount_percentage "Agreed recurring discount percentage"
    }

    INVOICES {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id FK "Tenant boundary ID (IMustHaveTenant)"
        timestamptz created_at "Invoice issuance timestamp (UTC)"
        uuid created_by "System or Administrator User ID"
        timestamptz last_modified_at "Modification timestamp (UTC)"
        uuid last_modified_by "Mutating User ID"
        boolean is_deleted "Soft-delete flag"
        uuid subscription_id FK "FK to parent Tenant Subscription"
        varchar invoice_number UK "Sequential invoice code (e.g. INV-2026-0819)"
        varchar status "Status (DRAFT, OPEN, PAID, UNCOLLECTIBLE, VOID)"
        integer subtotal_in_cents "Subtotal amount before discounts (BRL cents)"
        integer discount_in_cents "Applied promotional or contractual discount"
        integer total_in_cents "Final payable amount in BRL cents"
        timestamptz due_date "Payment due date (UTC)"
        timestamptz paid_at "Confirmation timestamp of payment receipt"
        timestamptz period_starts_at "Billed service cycle start date"
        timestamptz period_ends_at "Billed service cycle end date"
    }

    INVOICE_ITEMS {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id FK "Tenant boundary ID (IMustHaveTenant)"
        uuid invoice_id FK "FK to parent Invoice"
        varchar description "Line item description (e.g. Base Plan, Add-on SMS)"
        integer unit_price_in_cents "Unit price in BRL cents"
        integer quantity "Item quantity"
        integer total_price_in_cents "Total line amount in BRL cents"
    }

    PAYMENT_TRANSACTIONS {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id FK "Tenant boundary ID (IMustHaveTenant)"
        timestamptz created_at "Transaction initiation timestamp (UTC)"
        uuid created_by "Initiating User ID or System"
        timestamptz last_modified_at "Modification timestamp (UTC)"
        uuid last_modified_by "Mutating User ID"
        boolean is_deleted "Soft-delete flag"
        uuid invoice_id FK "FK to settled Invoice"
        varchar transaction_type "Payment gateway type (PIX, CREDIT_CARD, BOLETO)"
        varchar status "Status (PENDING, AUTHORIZED, PAID, FAILED, REFUNDED)"
        integer amount_in_cents "Settled amount in BRL cents"
        varchar gateway_name "Payment processor name (e.g. ASAAS, IUGU, STRIPE)"
        varchar gateway_transaction_id "Processor unique transaction ID"
        varchar pix_txid "Central Bank PIX transaction ID"
        varchar pix_end_to_end_id "Central Bank PIX End-to-End unique identifier"
        text pix_emv_payload "Central Bank PIX dynamic Copia e Cola payload"
        varchar card_last_four "Credit card last four digits"
        varchar card_brand "Card scheme (VISA, MASTERCARD, ELO)"
        timestamptz settled_at "Bank settlement timestamp (UTC)"
    }

    TENANT_QUOTA_USAGES {
        uuid id PK "UUIDv7 Temporal Primary Key (Ordinal 0)"
        uuid tenant_id FK "Tenant boundary ID (IMustHaveTenant)"
        varchar feature_key "Targeted feature entitlement key"
        timestamptz period_starts_at "Current metering cycle start date"
        timestamptz period_ends_at "Current metering cycle end date"
        bigint current_usage "Current consumed count (e.g. 1420 formulas)"
        timestamptz last_calculated_at "Latest usage recalculation timestamp"
    }

3. Physical Table Schema Specifications

Section titled “3. Physical Table Schema Specifications”

operations.plans

Section titled “operations.plans”
Column Physical Type Constraints Description
id uuid PK, Default uuidv7 Primary key generated via Guid.CreateVersion7().
code varchar(50) NOT NULL, UNIQUE Human-readable system code (e.g., FREEMIUM, CLINIC_MONTHLY).
name varchar(100) NOT NULL Display name of the plan tier.
description varchar(500) NULLABLE Summary of plan capabilities.
vertical varchar(30) NOT NULL Industry vertical (HEALTHCARE, DENTAL, VETERINARY, PHARMACY).
billing_interval varchar(20) NOT NULL Billing interval (MONTHLY, ANNUAL).
price_in_cents integer NOT NULL, DEFAULT 0 Price in Brazilian Real cents (0 for Freemium).
trial_duration_days integer NOT NULL, DEFAULT 30 Duration of initial reverse trial.
is_active boolean NOT NULL, DEFAULT true Availability in public and commercial catalogs.
created_at timestamptz NOT NULL, DEFAULT clock_timestamp() Creation timestamp.
version integer NOT NULL, DEFAULT 1 Optimistic concurrency token.

operations.tenant_subscriptions

Section titled “operations.tenant_subscriptions”
Column Physical Type Constraints Description
id uuid PK, Default uuidv7 Primary key generated via Guid.CreateVersion7().
tenant_id uuid NOT NULL, UNIQUE, FK -> core.tenants(id) Tenant boundary. Exactly one active subscription per tenant.
plan_id uuid NOT NULL, FK -> operations.plans(id) Active commercial plan reference.
status varchar(30) NOT NULL Lifecycle state (TRIALING, ACTIVE, PAST_DUE, SUSPENDED, CANCELED).
billing_interval varchar(20) NOT NULL Active interval (MONTHLY, ANNUAL).
trial_starts_at timestamptz NULLABLE Timestamp when the 30-day Reverse Trial commenced.
trial_ends_at timestamptz NULLABLE Expiration date of Reverse Trial. Triggers automated downgrade.
current_period_starts_at timestamptz NOT NULL Current subscription cycle start date.
current_period_ends_at timestamptz NOT NULL Current subscription cycle end date.
cancel_at_period_end boolean NOT NULL, DEFAULT false If true, downgrades to Freemium upon period completion.
created_at timestamptz NOT NULL Ordinal 3 metadata creation timestamp.
last_modified_at timestamptz NULLABLE Ordinal 5 metadata mutation timestamp.
is_deleted boolean NOT NULL, DEFAULT false Ordinal 7 soft delete flag.

operations.invoices

Section titled “operations.invoices”
Column Physical Type Constraints Description
id uuid PK, Default uuidv7 Primary key generated via Guid.CreateVersion7().
tenant_id uuid NOT NULL, FK -> core.tenants(id) Tenant boundary key enforcing RLS.
subscription_id uuid NOT NULL, FK -> operations.tenant_subscriptions(id) Associated subscription record.
invoice_number varchar(50) NOT NULL, UNIQUE Formatted invoice identifier (e.g., INV-2026-0012).
status varchar(20) NOT NULL State (DRAFT, OPEN, PAID, UNCOLLECTIBLE, VOID).
subtotal_in_cents integer NOT NULL Total before promotional discounts in BRL cents.
discount_in_cents integer NOT NULL, DEFAULT 0 Promotional or contractual discount in BRL cents.
total_in_cents integer NOT NULL Final payable amount in BRL cents.
due_date timestamptz NOT NULL Expiration date for payment before entering PAST_DUE.
paid_at timestamptz NULLABLE Timestamp when payment was confirmed.

operations.payment_transactions

Section titled “operations.payment_transactions”
Column Physical Type Constraints Description
id uuid PK, Default uuidv7 Primary key generated via Guid.CreateVersion7().
tenant_id uuid NOT NULL, FK -> core.tenants(id) Tenant boundary key enforcing RLS.
invoice_id uuid NOT NULL, FK -> operations.invoices(id) Settled invoice reference.
transaction_type varchar(30) NOT NULL Gateway rail (PIX, CREDIT_CARD, BOLETO).
status varchar(30) NOT NULL Transaction state (PENDING, AUTHORIZED, PAID, FAILED).
amount_in_cents integer NOT NULL Settled amount in Brazilian Real cents.
gateway_name varchar(50) NOT NULL Payment processor identifier.
gateway_transaction_id varchar(100) NOT NULL Payment processor reference code.
pix_txid varchar(100) NULLABLE Brazilian Central Bank PIX dynamic transaction identifier.
pix_end_to_end_id varchar(100) NULLABLE Brazilian Central Bank PIX unique End-to-End audit ID.
pix_emv_payload text NULLABLE Dynamic PIX Copia e Cola payload for client rendering.
card_last_four varchar(4) NULLABLE Last 4 digits of tokenized credit card.
card_brand varchar(20) NULLABLE Card scheme (VISA, MASTERCARD, ELO).
settled_at timestamptz NULLABLE Bank confirmation timestamp (UTC).

4. Multi-Tenancy & Row-Level Security (RLS) Policies

Section titled “4. Multi-Tenancy & Row-Level Security (RLS) Policies”

In compliance with ADR-004, the operations schema enforces strict tenant isolation:

  1. Tenant-Scoped Tables:

    • tenant_subscriptions
    • commercial_contracts
    • invoices
    • invoice_items
    • payment_transactions
    • tenant_quota_usages

    Every query targeting these tables must enforce:

    ALTER TABLE operations.tenant_subscriptions ENABLE ROW LEVEL SECURITY;
    ALTER TABLE operations.tenant_subscriptions FORCE ROW LEVEL SECURITY;
    

    CREATE POLICY tenant_isolation_policy ON operations.tenant_subscriptions
        FOR ALL
        USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

  2. Catalog Exemption Tables:

    • plans and plan_feature_entitlements are global read-only catalog tables. Tenant sessions possess SELECT grants without RLS filtering to inspect pricing and available feature packages.
    • Write mutations (INSERT, UPDATE, DELETE) are restricted to the gercia_server connection role during backoffice operations originating from ops.gercia.com.br.

5. Operations Backoffice Integration (ops.gercia.com.br)

Section titled “5. Operations Backoffice Integration (ops.gercia.com.br)”

The platform management backoffice runs at ops.gercia.com.br (local loopback: ops.gercia.dev) and interfaces directly with the operations schema via Gercia.Operations:

flowchart LR
    subgraph OPS ["GERCIA Operations Backoffice (ops.gercia.com.br)"]
        direction TB
        STAFF["GERCIA Staff Member<br>(Google Workspace SSO @gercia.com.br)"]
        UI["Angular Backoffice Portal<br>Dashboard, Invoicing, Plan Manager"]
    end

    subgraph API ["Central API Gateway (api.gercia.com.br)"]
        AUTH["Google Workspace OIDC<br>Role: GerciaOpsAdmin"]
        OPS_MOD["Gercia.Operations Domain Engine"]
    end

    subgraph DB ["Google Cloud SQL (southamerica-east1)"]
        SCHEMA_OPS[("PostgreSQL Schema: operations<br>plans, contracts, invoices, transactions")]
    end

    STAFF --> UI
    UI -- "HTTPS / TLS 1.3 (Bearer Token)" --> AUTH
    AUTH --> OPS_MOD
    OPS_MOD -- "EF Core DbContext (operations schema)" --> SCHEMA_OPS
  • Staff Authentication: Internal operators log in exclusively using Google Workspace Single Sign-On restricted to @gercia.com.br accounts (hd=gercia.com.br) protected by Cloudflare Zero Trust policies.
  • Autonomous Downgrade Dispatcher: A background recurring job running on Gercia.Server sweeps operations.tenant_subscriptions daily. Any subscription where status = 'TRIALING' and trial_ends_at <= clock_timestamp() is automatically transitioned to status = 'ACTIVE' with plan_id re-pointed to the FREEMIUM plan, emitting a SubscriptionDowngradedToFreemiumEvent to notify the tenant administrator via email.