proddb-postgres.ts

import { getCloudflareAccountId } from '@repo/alchemy-helpers/cloudflare'
import { postgresql, pulumi } from '@repo/pulumi'
import * as planetscale from '@repo/pulumi-planetscale' // added via `pulumi package add terraform-provider planetscale/planetscale`
import { HyperdriveConfig } from '@pulumi/cloudflare'

import { validateResourceName } from '../lib/schema.ts'

import type { CloudflareAccountId } from '@repo/alchemy-helpers/cloudflare'
import type { HyperdriveConfigArgs } from '@pulumi/cloudflare'

const planetscaleOrganization = 'jhands'
const planetscaleDatabase = 'proddb'
const planetscaleBranch = 'main'
const cloudflareAccountId = getCloudflareAccountId('main')

const proddbDatabases = [
	defineDB({
		database: { name: 'jmail' },
		role: {
			name: 'jmail_service_main',
			inheritedRoles: ['pg_read_all_data', 'pg_write_all_data'],
			successor: 'postgres',
		},
		hyperdrives: [
			{
				name: 'planetscale-jmail-proddb-main',
				caching: { disabled: true },
				originConnectionLimit: 20,
			},
		],
	}),
	defineDB({
		database: { name: 'neverdown' },
		role: {
			name: 'neverdown_service_main',
			inheritedRoles: ['pg_read_all_data', 'pg_write_all_data'],
			successor: 'postgres',
		},
		hyperdrives: [
			{
				name: 'planetscale-neverdown-proddb-main',
				caching: { disabled: true },
				originConnectionLimit: 15,
			},
		],
	}),
	defineDB({
		database: { name: 'planethealth' },
		role: {
			name: 'planethealth_service_main',
			inheritedRoles: ['pg_read_all_data', 'pg_write_all_data'],
			successor: 'postgres',
		},
		hyperdrives: [
			{
				name: 'planetscale-planethealth-proddb-main-cached',
				caching: { maxAge: 60, staleWhileRevalidate: 15 },
				originConnectionLimit: 15,
			},
			{
				name: 'planetscale-planethealth-proddb-main-uncached',
				caching: { disabled: true },
				originConnectionLimit: 15,
			},
		],
	}),
] as const satisfies ProddbServiceConfig[]

export interface ProddbPostgresArgs {}

export class ProddbPostgres extends pulumi.ComponentResource {
	private readonly accountId: CloudflareAccountId
	private readonly planetscaleProvider: planetscale.Provider
	private readonly postgresqlProvider: postgresql.Provider

	public readonly branch: planetscale.PostgresBranch
	public readonly databases: Record<string, postgresql.Database>
	public readonly roles: Record<string, planetscale.PostgresBranchRole>
	public readonly hyperdrives: Record<string, HyperdriveConfig>

	constructor(name: string, args: ProddbPostgresArgs, opts?: pulumi.ComponentResourceOptions) {
		super('custom:cf-infra:ProddbPostgres', name, args, opts)

		this.accountId = cloudflareAccountId

		const planetscaleConfig = new pulumi.Config('planetscale')
		const serviceToken = planetscaleConfig.requireSecret('serviceToken')

		this.planetscaleProvider = new planetscale.Provider(
			'proddb-planetscale',
			{
				serviceToken,
				serviceTokenId: 'xtg1w69nuca4',
			},
			{ parent: this }
		)

		this.branch = new planetscale.PostgresBranch(
			'proddb_main_planetscale_postgres_branch',
			{
				organization: planetscaleOrganization,
				database: planetscaleDatabase,
				name: planetscaleBranch,
			},
			{
				parent: this,
				provider: this.planetscaleProvider,
				import: JSON.stringify({
					organization: planetscaleOrganization,
					database: planetscaleDatabase,
					id: planetscaleBranch,
				}),
			}
		)

		// Bootstrap role Pulumi uses for CREATE DATABASE and other admin tasks.
		const adminRole = this.newRole(
			{
				// PlanetScale only returns the plain-text password when a role is created.
				// Imported roles do not expose a reusable password, so v2 needs its own
				// bootstrap role for the PostgreSQL provider.
				name: 'pulumi_admin_main',
				inheritedRoles: ['postgres'],
				successor: 'postgres',
			} as const satisfies NewPostgresBranchRoleArgs,
			{ dependsOn: [this.branch] }
		)

		// PlanetScale's login user is suffixed with the branch id. The
		// PostgreSQL provider wants the underlying DB role name separately,
		// so strip the branch suffix back off before issuing DDL.
		const adminDatabaseUsername = pulumi
			.all([adminRole.username, this.branch.id])
			.apply(([username, branchId]) => {
				const branchSuffix = `.${branchId}`
				if (!username.endsWith(branchSuffix)) {
					throw new Error(
						`expected PlanetScale username "${username}" to end with branch suffix "${branchSuffix}"`
					)
				}

				return username.slice(0, -branchSuffix.length)
			})

		// `accessHostUrl` is the hostname itself, not a full DSN, so keep the
		// configured port.
		this.postgresqlProvider = new postgresql.Provider(
			'proddb-planetscale-postgres',
			{
				host: adminRole.accessHostUrl,
				port: 5432,
				database: 'postgres',
				username: adminRole.username,
				databaseUsername: adminDatabaseUsername,
				password: adminRole.password,
				superuser: false,
				sslmode: 'verify-full',
				connectTimeout: 15,
				maxConnections: 4,
			},
			{
				parent: this,
				dependsOn: [adminRole],
			}
		)

		this.roles = Object.fromEntries(
			proddbDatabases.map((service) => [
				service.role.name,
				this.newRole(service.role, { dependsOn: [this.branch] }),
			])
		) as Record<string, planetscale.PostgresBranchRole>

		this.databases = Object.fromEntries(
			proddbDatabases.map((service) => [
				service.database.name,
				this.newDatabase(service.database, { dependsOn: [this.branch, adminRole] }),
			])
		) as Record<string, postgresql.Database>

		this.hyperdrives = Object.fromEntries(
			proddbDatabases.flatMap((service) =>
				service.hyperdrives.map((hyperdrive) => [
					hyperdrive.name,
					this.newHyperdrive(
						{
							...hyperdrive,
							database: service.database.name,
							role: service.role.name,
						},
						{
							dependsOn: [this.databases[service.database.name], this.roles[service.role.name]],
						}
					),
				])
			)
		) as Record<string, HyperdriveConfig>

		this.registerOutputs({
			branch: this.branch,
			databases: this.databases,
			roles: this.roles,
			hyperdrives: this.hyperdrives,
		})
	}

	private newDatabase(args: NewPostgresDatabaseArgs, opts?: pulumi.CustomResourceOptions) {
		const { import: importId, ...databaseArgs } = args

		validateResourceName({ database: databaseArgs.name })

		return new postgresql.Database(
			`${databaseArgs.name}_postgres_database`,
			{
				// Whether clients can connect to the database.
				allowConnections: true, // default

				// Maximum concurrent connections allowed to this database.
				connectionLimit: -1, // default

				// Character encoding used by the database.
				encoding: 'UTF8', // default

				// Keep database ownership on the built-in postgres role.
				owner: 'postgres',

				// Controls how text is sorted in queries and indexes.
				lcCollate: 'C', // default

				// Controls locale rules for character handling,
				// like upper/lower case behavior.
				lcCtype: 'C', // default

				// Create from Postgres's clean base template so
				// new databases start from a predictable baseline.
				template: 'template0', // default
				...databaseArgs,
			},
			{
				parent: this,
				provider: this.postgresqlProvider,
				...opts,
				...(importId ? { import: importId } : {}),
			}
		)
	}

	private newRole(args: NewPostgresBranchRoleArgs, opts?: pulumi.CustomResourceOptions) {
		const { import: importId, ...roleArgs } = args

		validateResourceName({ role: roleArgs.name })

		return new planetscale.PostgresBranchRole(
			`${roleArgs.name}_planetscale_postgres_branch_role`,
			{
				organization: planetscaleOrganization,
				database: planetscaleDatabase,
				branch: planetscaleBranch,
				...roleArgs,
			},
			{
				parent: this,
				provider: this.planetscaleProvider,
				...opts,
				...(importId ? { import: importId } : {}),
			}
		)
	}

	private newHyperdrive(args: NewHyperdriveArgs, opts?: pulumi.CustomResourceOptions) {
		const { import: importId, ...hyperdriveArgs } = args

		const database = this.databases[hyperdriveArgs.database]
		const role = this.roles[hyperdriveArgs.role]

		if (!database) {
			throw new Error(`Unknown proddb database: ${hyperdriveArgs.database}`)
		}

		if (!role) {
			throw new Error(`Unknown proddb role: ${hyperdriveArgs.role}`)
		}

		return new HyperdriveConfig(
			`${hyperdriveArgs.name}_hyperdrive`,
			{
				accountId: this.accountId,
				name: hyperdriveArgs.name,
				origin: {
					database: database.name,
					host: role.accessHostUrl,
					password: role.password,
					port: 6432,
					scheme: 'postgres',
					user: role.username,
				},
				caching: hyperdriveArgs.caching,
				originConnectionLimit: hyperdriveArgs.originConnectionLimit,
			},
			{
				parent: this,
				dependsOn: [database, role],
				...opts,
				...(importId ? { import: importId } : {}),
			}
		)
	}
}

type NewPostgresDatabaseArgs<TName extends string = string> = {
	name: TName
	import?: string
	owner?: string
	allowConnections?: boolean
	alterObjectOwnership?: boolean
	connectionLimit?: number
	encoding?: string
	isTemplate?: boolean
	lcCollate?: string
	lcCtype?: string
	tablespaceName?: string
	template?: string
}

type NewPostgresBranchRoleArgs<TName extends string = string> = {
	name: TName
	import?: string
	inheritedRoles?: PostgresBuiltinRoleName[]
	successor: 'postgres'
	ttl?: number
}

type PostgresBuiltinRoleName =
	| 'postgres'
	| 'pg_read_all_data'
	| 'pg_write_all_data'
	| 'pg_read_all_settings'
	| 'pg_read_all_stats'
	| 'pg_stat_scan_tables'
	| 'pg_monitor'
	| 'pg_signal_backend'
	| 'pg_checkpoint'
	| 'pg_maintain'
	| 'pg_use_reserved_connections'
	| 'pg_create_subscription'

type RoleNameFor<TDatabase extends string> = `${TDatabase}_service_main`

type HyperdriveNameFor<TDatabase extends string> =
	`planetscale-${TDatabase}-proddb-main${'' | '-cached' | '-uncached'}`

type ProddbServiceConfig<TDatabase extends string = string> = {
	database: NewPostgresDatabaseArgs<TDatabase>
	role: NewPostgresBranchRoleArgs<RoleNameFor<TDatabase>>
	hyperdrives: Array<
		Omit<NewHyperdriveArgs<TDatabase, RoleNameFor<TDatabase>>, 'database' | 'role'>
	>
}

type NewHyperdriveArgs<TDatabase extends string = string, TRole extends string = string> = {
	name: HyperdriveNameFor<TDatabase>
	import?: string
	caching: HyperdriveConfigArgs['caching']
	originConnectionLimit: HyperdriveConfigArgs['originConnectionLimit']
	database: TDatabase
	role: TRole
}

function defineDB<const TDatabase extends string>(service: ProddbServiceConfig<TDatabase>) {
	return service
}