Data access layer

Overview

OpenCouncil uses Prisma ORM for type-safe database access with PostgreSQL. All database queries are centralized in src/lib/db/ to maintain consistency and prevent duplicate code.

Never write raw Prisma queries directly in components or API routes. Always use the centralized functions from src/lib/db/.

Prisma client setup

The Prisma client is configured as a singleton in src/lib/db/prisma.ts:

src/lib/db/prisma.ts

import { PrismaClient } from '@prisma/client'
import { env } from '@/env.mjs'

const globalForPrisma = globalThis as unknown as {
    prisma: PrismaClient | undefined;
}

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
    log: ['error', 'warn'],
    datasourceUrl: env.DATABASE_URL,
})

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

export default prisma;

The singleton pattern prevents multiple Prisma instances in development due to hot reloading.

Centralized data access

All database operations are organized by entity type in src/lib/db/:

lib/db/
├── prisma.ts              # Prisma client singleton
├── cities.ts              # City queries
├── meetings.ts            # Meeting queries  
├── people.ts              # Person queries
├── parties.ts             # Party queries
├── notifications.ts       # Notification logic
├── reviews.ts             # Review queries
├── speakerSegments.ts     # Speaker segment queries
├── subject.ts             # Subject queries
├── transcript.ts          # Transcript queries
└── types/                 # Shared Prisma types

City queries example

Here’s how city queries are organized:

src/lib/db/cities.ts

"use server";
import { City } from '@prisma/client';
import prisma from "./prisma";
import { isUserAuthorizedToEdit, withUserAuthorizedToEdit } from "../auth";

export type CityWithCounts = City & {
    _count: {
        persons: number;
        parties: number;
        councilMeetings: number;
    };
};

export async function getCity(
    id: string,
    options?: { includeGeometry?: boolean }
): Promise<CityWithCounts | null> {
    const city = await prisma.city.findUnique({
        where: { id },
        include: {
            _count: {
                select: {
                    persons: true,
                    parties: true,
                    councilMeetings: { where: { released: true } }
                }
            }
        }
    });
    
    if (!city) return null;
    if (!options?.includeGeometry) return city;
    
    return await attachGeometryToCity(city);
}

export async function getCities({ 
    includeUnlisted = false, 
    includePending = false 
}: { 
    includeUnlisted?: boolean, 
    includePending?: boolean 
} = {}): Promise<CityWithCounts[]> {
    const currentUser = includeUnlisted ? await getCurrentUser() : null;
    
    let whereClause: any = {};
    if (!includeUnlisted && !includePending) {
        whereClause.status = 'listed';
    }
    
    const cities = await prisma.city.findMany({
        where: whereClause,
        include: { _count: CITY_COUNT_SELECT },
        orderBy: [
            { officialSupport: 'desc' },
            { status: 'desc' },
            { name: 'asc' }
        ]
    });
    
    return cities;
}

export async function editCity(
    id: string, 
    cityData: Partial<Omit<City, 'id' | 'createdAt' | 'updatedAt'>>
): Promise<City> {
    await withUserAuthorizedToEdit({ cityId: id });
    
    return await prisma.city.update({
        where: { id },
        data: cityData,
    });
}

All mutation functions (create, edit, delete) must check authorization using withUserAuthorizedToEdit() before performing the operation.

Type patterns

Using Prisma-generated types

Prisma generates types for all models. Use them directly:

import { City, Person, Party, CouncilMeeting } from '@prisma/client';

function displayCity(city: City) {
    console.log(city.name, city.status);
}

Extended types with relations

When including relations, define custom types:

src/lib/db/people.ts

import { Person, Role, VoicePrint } from '@prisma/client';
import { RoleWithRelations } from './types';

export type PersonWithRelations = Person & {
    roles: RoleWithRelations[];
    voicePrints?: VoicePrint[];
};

export async function getPerson(id: string): Promise<PersonWithRelations | null> {
    return await prisma.person.findUnique({
        where: { id },
        include: {
            roles: roleWithRelationsInclude,
            voicePrints: {
                orderBy: { createdAt: 'desc' },
                take: 1
            }
        }
    });
}

Shared type storage

Store shared Prisma types in src/lib/db/types/:

src/lib/db/types/roles.ts

import { Prisma } from '@prisma/client';

export const roleWithRelationsInclude = Prisma.validator<Prisma.RoleInclude>()({
    party: true,
    city: true,
    administrativeBody: true
});

export type RoleWithRelations = Prisma.RoleGetPayload<{
    include: typeof roleWithRelationsInclude
}>;

Then re-export from the index:

src/lib/db/types/index.ts

export * from './roles';
export * from './city';

import type { PersonWithRelations } from '../people';
import type { PartyWithPersons } from '../parties';

export type { PersonWithRelations, PartyWithPersons };

Always check if a type already exists before creating a new one. When a function returns a type you need, follow the import chain to find its definition.

Common query patterns

Basic CRUD operations

export async function createPerson(data: {
    cityId: string;
    name: string;
    roles: Role[];
}): Promise<Person> {
    await withUserAuthorizedToEdit({ cityId: data.cityId });
    
    return await prisma.person.create({
        data: {
            cityId: data.cityId,
            name: data.name,
            roles: {
                create: data.roles.map(role => ({
                    cityId: role.cityId,
                    partyId: role.partyId,
                    name: role.name,
                    startDate: role.startDate,
                    endDate: role.endDate
                }))
            }
        },
        include: { roles: roleWithRelationsInclude }
    });
}

Composite keys

Many models use (cityId, id) composite keys for multi-tenant isolation:

src/lib/db/meetings.ts

export async function getCouncilMeeting(
    cityId: string, 
    id: string
): Promise<CouncilMeetingWithAdminBody | null> {
    const meeting = await prisma.councilMeeting.findUnique({
        where: { 
            cityId_id: { cityId, id }  // Composite key
        },
        include: {
            administrativeBody: true
        }
    });
    
    // Check if user can view unreleased meetings
    if (meeting && !meeting.released && 
        !(await isUserAuthorizedToEdit({ cityId }))) {
        return null;
    }
    
    return meeting;
}

Pagination

export async function getCouncilMeetingsForCity(
    cityId: string,
    { page, pageSize = 12 }: { page?: number; pageSize?: number }
): Promise<CouncilMeetingWithAdminBodyAndSubjects[]> {
    const skip = page ? (page - 1) * pageSize : undefined;
    
    return await prisma.councilMeeting.findMany({
        where: { cityId, released: true },
        orderBy: [
            { dateTime: 'desc' },
            { createdAt: 'desc' }
        ],
        ...(skip !== undefined && { skip }),
        ...(pageSize && { take: pageSize }),
        include: {
            subjects: {
                orderBy: [
                    { hot: 'desc' },
                    { agendaItemIndex: 'asc' }
                ]
            },
            administrativeBody: true
        }
    });
}

Raw SQL with PostGIS

For geographic queries, use raw SQL with type safety:

src/lib/db/cities.ts

import { Prisma } from '@prisma/client';

export async function attachGeometryToCities<T extends Pick<City, 'id'>>(
    cities: T[]
): Promise<Array<T & { geometry?: GeoJSON.Geometry }>> {
    if (cities.length === 0) return cities;

    const cityWithGeometry = await prisma.$queryRaw<
        ({ id: string, geometry: string | null })[]
    >`SELECT 
        c."id" AS id,
        ST_AsGeoJSON(c.geometry)::text AS geometry
    FROM "City" c
    WHERE c.id IN (${Prisma.join(cities.map(city => city.id))})
    `;

    return cities.map(city => {
        const cityGeom = cityWithGeometry.find(c => c.id === city.id);
        const parsed = cityGeom?.geometry ? JSON.parse(cityGeom.geometry) : null;
        return {
            ...city,
            geometry: parsed
        };
    });
}

Use Prisma.join() for safely interpolating arrays in raw queries.

Database schema highlights

Key models in the Prisma schema:

City - Municipalities and regions

model City {
  id        String   @id @default(cuid())
  name      String   // Αθήνα
  name_en   String   // Athens
  name_municipality String // Δήμος Αθηναίων
  name_municipality_en String // Municipality of Athens
  logoImage String?
  timezone  String
  status CityStatus @default(pending)
  authorityType AuthorityType @default(municipality)
  geometry Unsupported("geometry")?
  
  parties        Party[]
  persons        Person[]
  councilMeetings CouncilMeeting[]
  administrators Administers[]
  administrativeBodies AdministrativeBody[]
}

CouncilMeeting - Meeting records

model CouncilMeeting {
  cityId    String
  id        String
  name      String
  dateTime  DateTime
  released  Boolean @default(false)
  audioUrl  String?
  videoUrl  String?
  administrativeBodyId String?
  
  city              City @relation(fields: [cityId], references: [id])
  administrativeBody AdministrativeBody?
  subjects          Subject[]
  speakerSegments   SpeakerSegment[]
  highlights        Highlight[]
  taskStatuses      TaskStatus[]
  
  @@id([cityId, id])
}

Person - Council members

model Person {
  id         String   @id @default(cuid())
  cityId     String
  name       String
  name_en    String
  name_short String
  name_short_en String
  image      String?
  profileUrl String?
  
  city         City @relation(fields: [cityId], references: [id])
  roles        Role[]
  voicePrints  VoicePrint[]
  speakerTags  SpeakerTag[]
}

Role - Administrative roles

model Role {
  id        String   @id @default(cuid())
  cityId    String
  personId  String?
  partyId   String?
  administrativeBodyId String?
  name      String
  name_en   String
  isHead    Boolean @default(false)
  startDate DateTime?
  endDate   DateTime?
  rank      Int?
  
  city              City @relation(fields: [cityId], references: [id])
  person            Person? @relation(fields: [personId], references: [id])
  party             Party? @relation(fields: [partyId], references: [id])
  administrativeBody AdministrativeBody?
}

Prisma commands

npm run prisma:generate

IMPORTANT: When making schema changes, always use --create-only to generate the migration file without applying it. This allows testing the migration against a local database first before applying to production.

Best practices

Always use centralized functions

Never write Prisma queries directly in components or API routes. Use the functions from src/lib/db/.

Check authorization first

All mutation functions must call withUserAuthorizedToEdit() before performing the operation.

Use Server Actions

Mark data mutation functions with "use server" to enable them as Server Actions.

Type safety everywhere

Define custom types for complex queries with relations. Store shared types in src/lib/db/types/.

Use transactions for multi-step operations

When updating multiple related records, use prisma.$transaction() to ensure atomicity.

Check for existing types

Before creating a new type, search for existing types that might already define what you need.

Testing with Testcontainers

OpenCouncil uses Testcontainers for integration tests with real PostgreSQL:

__tests__/integration.test.ts

import { PostgreSqlContainer } from '@testcontainers/postgresql';
import { PrismaClient } from '@prisma/client';

describe('Database integration tests', () => {
    let container: PostgreSqlContainer;
    let prisma: PrismaClient;

    beforeAll(async () => {
        container = await new PostgreSqlContainer('postgres:14')
            .withExposedPorts(5432)
            .start();

        process.env.DATABASE_URL = container.getConnectionUri();
        prisma = new PrismaClient();
        
        // Run migrations
        await prisma.$executeRaw`CREATE EXTENSION IF NOT EXISTS postgis`;
    });

    afterAll(async () => {
        await prisma.$disconnect();
        await container.stop();
    });

    test('creates and retrieves a city', async () => {
        const city = await prisma.city.create({
            data: {
                name: 'Test City',
                name_en: 'Test City',
                name_municipality: 'Municipality of Test',
                name_municipality_en: 'Municipality of Test',
                timezone: 'Europe/Athens'
            }
        });

        const retrieved = await prisma.city.findUnique({
            where: { id: city.id }
        });

        expect(retrieved?.name).toBe('Test City');
    });
});

Next steps

Authentication

Learn authorization patterns

Project structure

Understand the codebase organization

API reference

Explore the API endpoints

Contributing

Learn the contribution workflow

Get Started

Core Features

Deployment

Development

Guides

Overview

Prisma client setup

Centralized data access

City queries example

Type patterns

Using Prisma-generated types

Extended types with relations

Shared type storage

Common query patterns

Basic CRUD operations

Composite keys

Raw SQL with PostGIS

Database schema highlights

Prisma commands

Best practices

Testing with Testcontainers

Next steps

Authentication

Project structure

API reference

Contributing

Build docs developers (and LLMs) love

Get Started

Core Features

Deployment

Development

Guides

​Overview

​Prisma client setup

​Centralized data access

​City queries example

​Type patterns

​Using Prisma-generated types

​Extended types with relations

​Shared type storage

​Common query patterns

​Basic CRUD operations

​Composite keys

​Pagination

​Raw SQL with PostGIS

​Database schema highlights

​Prisma commands

​Best practices

​Testing with Testcontainers

​Next steps

Authentication

Project structure

API reference

Contributing

Build docs developers (and LLMs) love

Overview

Prisma client setup

Centralized data access

City queries example

Type patterns

Using Prisma-generated types

Extended types with relations

Shared type storage

Common query patterns

Basic CRUD operations

Composite keys

Pagination

Raw SQL with PostGIS

Database schema highlights

Prisma commands

Best practices

Testing with Testcontainers

Next steps