PADS
Grove Portal Database Compatibility

Table of Contents

• 1. Grove Postgres Database Schema
  • 1.1 Entity Relationship Diagram
• 2. SQLC Autogeneration

1. Grove Postgres Database Schema

https://www.postgresql.org/

The Grove Postgres Driver schema file uses a subset of tables from the existing Grove Portal database schema, allowing PATH to source its authorization data from the existing Grove Portal DB.

It converts the data stored in the portal_applications table and its associated tables into the proto.GatewayEndpoint format expected by PATH's Go External Authorization Server.

It also listens for updates to the Grove Portal DB and streams updates to the Go External Authorization Server in real time as changes are made to the connected Postgres database.

For the full Grove Portal DB schema, refer to the database schema defined in the Portal HTTP DB (PHD) repository.

1.1 Entity Relationship Diagram

This ERD shows the subset of tables from the full Grove Portal DB schema that are used by the Grove Postgres Driver in PADS.

erDiagram
    PAY_PLANS {
        VARCHAR(25) plan_type PK
        INT monthly_relay_limit
        INT throughput_limit
    }

    ACCOUNTS {
        VARCHAR(10) id PK
        VARCHAR(25) plan_type FK
    }

    USERS {
        VARCHAR(10) id PK
    }

    USER_AUTH_PROVIDERS {
        SERIAL id PK
        VARCHAR(10) user_id FK
        VARCHAR(255) provider_user_id
        VARCHAR type
    }

    ACCOUNT_USERS {
        SERIAL id PK
        VARCHAR(10) user_id FK
        VARCHAR(10) account_id FK
    }

    PORTAL_APPLICATIONS {
        VARCHAR(24) id PK
        VARCHAR(10) account_id FK
    }

    PORTAL_APPLICATION_SETTINGS {
        SERIAL id PK
        VARCHAR(24) application_id FK
        VARCHAR(64) secret_key
        BOOLEAN secret_key_required
    }

    PORTAL_APPLICATION_CHANGES {
        SERIAL id PK
        VARCHAR(24) portal_app_id
        BOOLEAN is_delete
        TIMESTAMP changed_at
    }

    PAY_PLANS ||--o{ ACCOUNTS : "plan_type"
    ACCOUNTS ||--o{ ACCOUNT_USERS : "id"
    USERS ||--o{ ACCOUNT_USERS : "id"
    USERS ||--o{ USER_AUTH_PROVIDERS : "id"
    ACCOUNTS ||--o{ PORTAL_APPLICATIONS : "id"
    PORTAL_APPLICATIONS ||--o{ PORTAL_APPLICATION_SETTINGS : "id"

2. SQLC Autogeneration

https://docs.sqlc.dev/en/stable

The Postgres Driver uses SQLC to autogenerate the Postgres Go code needed to interact with the Postgres database/

The Make target make gen_sqlc will regenerate the Go code from the SQLC schema file.

This will output code autogenerated from the postgres/grove/sqlc/grove_schema.sql and postgres/grove/sqlc/grove_queries.sql files to the postgres/grove/sqlc directory.

SQLC configuration is defined in the postgres/sqlc/sqlc.yaml file.