> ## Documentation Index
> Fetch the complete documentation index at: https://docs.controlplane.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Constructive

> Constructive pgpm Postgres modules with a GraphQL API generated from the database and row-level security, on a bundled PostgreSQL 18 with PostGIS and pgvector. Internal-only API. Deploys into an existing GVC; two dictionary secrets must exist first.

## Overview

Constructive is pgpm, a package manager for versioned Postgres modules (schemas, row-level security policies, functions), plus a server that generates a GraphQL API from the database. This template deploys your pgpm modules from a git tag or your own image at boot, and serves them over an internal GraphQL API whose every request runs as a non-superuser role, so Postgres row-level security (RLS) decides which rows each user sees. It deploys only MIT-licensed code.

> [!NOTE]
>
> This template does not create a GVC. It deploys into an existing GVC that you already have, and that GVC should have exactly one location, because the bundled database runs in one location.

### What Gets Created

| Resource | Name | Purpose | Optional |
| - | - | - | - |
| Workload | `RELEASE_NAME-constructive` | GraphQL server (standard, 1 replica by default, up to 4) on port `3000`; each boot sets up the database roles and deploys your modules | — |
| Workload | `RELEASE_NAME-postgres` | PostgreSQL 18 with PostGIS and pgvector (stateful, 1 replica) | — |
| Volume set | `RELEASE_NAME-pg-vs` | PostgreSQL data volume | — |
| Identity | `RELEASE_NAME-constructive-identity` | Workload identity for the GraphQL server | — |
| Policy | `RELEASE_NAME-constructive-policy` | Grants the identity `reveal` on exactly your two prerequisite secrets | — |
| Identity | `RELEASE_NAME-pg-identity` | Workload identity for PostgreSQL | — |
| Policy | `RELEASE_NAME-pg-policy` | Grants the PostgreSQL identity `reveal` on the database credentials secret | — |
| Cron workload | `RELEASE_NAME-postgres-backup` | Scheduled database backups to object storage | `postgres.backup.enabled: true` |

The API is reachable only from inside Control Plane. There is no public endpoint, because the `X-Actor-Id` header that names the acting user is trusted as sent.

## Prerequisites

**1. Create the database credentials secret**

PostgreSQL initializes its superuser and database from this **dictionary** secret, and the boot deploys modules with it:

```bash theme={null}
cpln secret create-dictionary --name DB_CREDENTIALS_SECRET_NAME \
  --entry username=constructive \
  --entry password="$(openssl rand -hex 32)" \
  --entry database=constructive
```

Set `postgres.config.credentialsSecretName` to this name.

**2. Create the server login secret**

The boot creates this login as `NOSUPERUSER NOBYPASSRLS`, and the GraphQL server connects as it. Its username must differ from the database username:

```bash theme={null}
cpln secret create-dictionary --name LOGIN_SECRET_NAME \
  --entry username=constructive_app \
  --entry password="$(openssl rand -hex 32)"
```

Set `server.loginSecretName` to this name. Secret names are org-wide, so give each release its own secrets.

**3. Choose a module source (optional)**

A public git repository holding a pgpm workspace, or your own image built `FROM constructiveio/constructive:08e422d` with the workspace copied in. See [Deploying Modules](#deploying-modules).

> [!WARNING]
>
> Both secrets must exist **before** you install. If either does not, the deployment waits silently: `cpln logs` returns nothing, and the only place the missing secret is named is `status.versions[].message` in `cpln workload get-deployments RELEASE_NAME-constructive --gvc GVC_NAME -o yaml`. Create the secret and the deployment recovers on its own after several minutes, or run `cpln workload force-redeployment RELEASE_NAME-constructive --gvc GVC_NAME` to skip the wait.

Optional database backups need a cloud account and bucket, as described in the [postgres](https://docs.controlplane.com/template-catalog/templates/postgres.md) template.

## Installation

The required values are the names of the two secrets from [Prerequisites](#prerequisites):

```bash theme={null}
cpln helm install RELEASE_NAME oci://ghcr.io/controlplane-com/templates/constructive \
  --version 1.0.0 --gvc GVC_NAME \
  --set postgres.config.credentialsSecretName=DB_CREDENTIALS_SECRET_NAME \
  --set server.loginSecretName=LOGIN_SECRET_NAME
```

To install, follow the instructions for your preferred method:

> [card] [UI](https://docs.controlplane.com/template-catalog/install-manage/ui.md)
>
> Browse, install, and manage templates visually

> [card] [CLI](https://docs.controlplane.com/template-catalog/install-manage/cli.md)
>
> Manage templates from your terminal

> [card] [Terraform](https://docs.controlplane.com/template-catalog/install-manage/terraform.md)
>
> Declare templates in your Terraform configurations

> [card] [Pulumi](https://docs.controlplane.com/template-catalog/install-manage/pulumi.md)
>
> Declare templates in your Pulumi programs

## Configuration

### Server

```yaml theme={null}
image: constructiveio/constructive:08e422d # upstream release build of commit 08e422d (upstream publishes no semver tags)

# GraphQL server replicas. All state lives in PostgreSQL, so replicas are interchangeable;
# 2 keeps the API up through restarts and upgrades. Allowed: 1-4.
replicas: 1

resources: # per GraphQL server replica
  minCpu: 250m
  minMemory: 256Mi
  maxCpu: 1000m
  maxMemory: 1Gi
```

### Server login

```yaml theme={null}
# ─── Server Login ─────────────────────────────────────────────────────────────
server:
  # REQUIRED PREREQUISITE SECRET — CREATE IT BEFORE YOU INSTALL. A `dictionary` secret with
  # `username` and `password`. The chart creates this NOSUPERUSER, NOBYPASSRLS Postgres login on
  # every start and the GraphQL server connects as it, so row-level security always applies.
  # The username must differ from the database superuser. If it is missing the deployment WEDGES silently.
  loginSecretName: my-constructive-login
```

### pgpm modules

```yaml theme={null}
# ─── pgpm Modules ─────────────────────────────────────────────────────────────
modules:
  source: none # none | git (public repo) | image (workspace baked into your own `image`)
  git:
    url: ""  # https URL of a public repo holding a pgpm workspace, e.g. https://git.example.com/acme/db.git
    ref: ""  # tag or full commit SHA to deploy; change it and `helm upgrade` to deploy a new version
  path: "."  # workspace directory: relative to the repo root (git) or absolute inside the image (image)
  packages: [] # pgpm packages to deploy, in order, e.g. [my-app]
```

### Deploying Modules

* **Workspace layout**: a pgpm workspace (`pgpm.json` at `modules.path`, packages under it). `modules.packages` takes the pgpm package names (the `%project` in each `pgpm.plan`), deployed in the order listed.
* **Dependencies**: `pgpm deploy` installs nothing. Commit everything your packages require to the workspace (the `pgpm install` output) or bake it into your image. Native extensions such as `pgcrypto` and `postgis` need nothing extra.
* **Grants**: your modules grant `anonymous` and `authenticated` what they may use. A table with no grant answers `permission denied`.
* **New versions**: set a new `modules.git.ref` (or a new `image` tag) and run `cpln helm upgrade`. A restart with the same settings skips the deploy, so a branch name never picks up new commits.
* **Failed deploy**: the new replica stops with the pgpm error in its logs and never becomes ready, while the old replicas keep serving. Fix the module or revert the upgrade.
* **Image source**: build `FROM constructiveio/constructive:08e422d`, `COPY` your workspace to an absolute path, set `image` to your image, `modules.source: image` and `modules.path` to that path.

### Access

```yaml theme={null}
# ─── Access ───────────────────────────────────────────────────────────────────
internalAccess: # which workloads can call the GraphQL API (there is no public access: callers assert X-Actor-Id)
  type: same-gvc # options: none, same-gvc, same-org, workload-list
  workloads: [] # used with workload-list
  # workloads:
  #   - //gvc/GVC_NAME/workload/WORKLOAD_NAME
```

### Database

```yaml theme={null}
# ─── Database: PostgreSQL with PostGIS and pgvector ───────────────────────────
# Deploys the postgres template with Constructive's postgres-plus image (Postgres 18 +
# PostGIS + pgvector), which Constructive's modules require.
postgres:
  image: constructiveio/postgres-plus@sha256:3708b89b001fbc7ca0f5fb5c7818135a6f1c1d4aae0d63cb7ca38aa0fe85a28e # tag 57d7be6: Postgres 18, PostGIS 3.6.4, pgvector 0.8.2
  config:
    # REQUIRED PREREQUISITE SECRET — CREATE IT BEFORE YOU INSTALL. A `dictionary` secret with
    # `username`, `password` and `database`. PostgreSQL initializes its superuser and database from
    # it once; Constructive deploys modules with it. If it is missing the deployment WEDGES silently.
    credentialsSecretName: my-constructive-db
  resources:
    minCpu: 250m
    minMemory: 384Mi
    maxCpu: 1000m
    maxMemory: 1Gi
  volumeset:
    capacity: 10 # initial capacity in GiB (minimum is 10)

  backup: # pass-through to the postgres template's backup feature
    enabled: false
    image: ghcr.io/controlplane-com/backup-images/postgres-backup:18.1.0
    schedule: "0 2 * * *" # daily at 2am UTC
    resources:
      cpu: 100m
      memory: 128Mi
    provider: aws # Options: aws, gcp, or minio
    aws:
      bucket: my-constructive-backup-bucket
      region: us-east-1
      cloudAccountName: my-constructive-backup-cloudaccount
      policyName: my-constructive-backup-policy
      prefix: postgres/backups
    gcp:
      bucket: my-constructive-backup-bucket
      cloudAccountName: my-constructive-backup-cloudaccount
      prefix: postgres/backups
    minio:
      endpoint: http://my-minio-workload:9000
      bucket: my-constructive-backup-bucket
      # REQUIRED prerequisite dictionary secret holding `accessKey` and `secretKey`,
      # only when backup is enabled with provider: minio.
      credentialsSecretName: my-constructive-minio-credentials
      prefix: postgres/backups
```

## Connecting

| Who | Endpoint | Notes |
| - | - | - |
| Workloads allowed by `internalAccess` | `http://RELEASE_NAME-constructive.GVC_NAME.cpln.local:3000/graphql` | POST GraphQL with the headers below. |
| You, from your machine | `cpln port-forward RELEASE_NAME-constructive 3000:3000 --gvc GVC_NAME`, then `http://localhost:3000/graphql` | POST with the headers below, or browse it in GraphiQL (next section). |
| PostgreSQL (superuser) | `RELEASE_NAME-postgres.GVC_NAME.cpln.local:5432` | Credentials from the database credentials secret (`cpln secret reveal DB_CREDENTIALS_SECRET_NAME -o yaml`). |

Every request sends these headers:

| Header | Value |
| - | - |
| `X-Schemata` | the schemas to expose, comma-separated, e.g. `app_public` |
| `X-Database-Id` | `00000000-0000-4000-8000-000000000c01` (fixed for every install) |
| `X-Actor-Id` | the acting user's UUID; requests run as `authenticated` with `jwt.claims.user_id` set to it. Without it they run as `anonymous` |

```bash theme={null}
curl -s http://RELEASE_NAME-constructive.GVC_NAME.cpln.local:3000/graphql \
  -H 'content-type: application/json' \
  -H 'X-Schemata: app_public' \
  -H 'X-Database-Id: 00000000-0000-4000-8000-000000000c01' \
  -H 'X-Actor-Id: 7d3b8c1e-0000-4000-8000-000000000001' \
  --data '{"query":"query { __typename }"}'
```

### Browsing the API in GraphiQL

The server ships GraphiQL at `/graphiql`, but the page itself needs `X-Database-Id` and `X-Schemata`, which a browser does not send, so a bare tab gets a 404.

1. Run `cpln port-forward RELEASE_NAME-constructive 3000:3000 --gvc GVC_NAME`.
2. Add `X-Database-Id: 00000000-0000-4000-8000-000000000c01` and `X-Schemata: YOUR_SCHEMAS` to every request to `localhost:3000`, with a browser extension that sets request headers or a local proxy that adds them.
3. Open `http://localhost:3000/graphiql` (or the proxy's address), and set `{"X-Actor-Id": "USER_UUID"}` in its Headers pane to query as that user; leave it empty to query as `anonymous`.

The Docs pane lists the types and mutations generated from your modules. If the page stops loading after a while, restart the `cpln port-forward`.

## Operations

### Backing Up

Set `postgres.backup.enabled: true` and fill the provider block under [Database](#database). The cloud account and bucket setup follows the [postgres](https://docs.controlplane.com/template-catalog/templates/postgres.md) template.

### Scaling and Availability

GraphQL server replicas hold no state, so they are interchangeable: set `replicas: 2` (up to 4) to keep the API up through restarts and upgrades. PostgreSQL is a single instance, so a database restart interrupts the API at any replica count.

### Troubleshooting

> [accordion] The deployment never starts and cpln logs is empty
>
> A prerequisite secret does not exist. Read `status.versions[].message` with `cpln workload get-deployments RELEASE_NAME-constructive --gvc GVC_NAME -o yaml`, create the named secret, then run `cpln workload force-redeployment RELEASE_NAME-constructive --gvc GVC_NAME`.

> [accordion] After an upgrade the new replica never becomes ready
>
> The module deploy failed. Read the pgpm error with `cpln logs '{gvc="GVC_NAME", workload="RELEASE_NAME-constructive"}' --limit 50 --since 10m`. The old replicas keep serving; fix the module or revert the upgrade.

> [accordion] permission denied for table
>
> The module grants nothing on that table to `anonymous` or `authenticated`. Add the grants to your module and deploy a new version.

> [accordion] The server stops starting after the database secret was edited
>
> PostgreSQL keeps the credentials it first initialized with, and the boot authenticates with the secret. Restore the original values in the database credentials secret.

## Important Notes

* `X-Actor-Id` is trusted as sent, and `X-Schemata` can name any schema the server login can read. Allow only workloads you trust in `internalAccess`; there is no public endpoint for this reason.
* The server login is re-created on every start. To rotate its password, change `password` with `cpln secret edit LOGIN_SECRET_NAME`, then run `cpln workload force-redeployment RELEASE_NAME-constructive --gvc GVC_NAME`.
* Do not edit the database credentials secret after install.
* Keep `postgres.image` on an image with PostGIS: Constructive's base modules require it, and a backup restores only into such a server.
* Jobs that modules enqueue are not processed: this template runs no job worker.
* The first `cpln helm upgrade` after an install can restart the API.
* Use a single-location GVC. Uninstalling deletes the database volume.

## External References

> [card] [Constructive on GitHub](https://github.com/constructive-io/constructive)
>
> Source and documentation

> [card] [Constructive quickstart](https://github.com/constructive-io/constructive/blob/main/QUICKSTART.md)
>
> Getting started with pgpm and the server

> [card] [GraphQL server reference](https://github.com/constructive-io/constructive/tree/main/graphql/server)
>
> Headers, schemas and server behavior

> [card] [pgpm modules](https://github.com/constructive-io/pgpm-modules)
>
> Reusable Postgres modules