Architecture

Database Schema

PostgreSQL table structure, indexes, and relationships

This guide documents the complete database schema for ZineCore2, including all tables, indexes, foreign keys, and constraints.

Overview

ZineCore2 uses PostgreSQL 16+ with:

Integer primary keys (BigAutoField)
ArrayField for repeatable elements
Foreign keys for relationships
Indexes on external IDs and foreign keys
Timestamps on all profile tables

Table Categories

Profile Tables (4)

Core metadata models implementing the four ZineCore2 profiles:

Table	Purpose	External ID Field
catalog_zine	ZineCore2 bibliographic records	zine_id
agents_agent	AgentCore2 authority records	agent_id
holdings_holding	HoldingCore2 holdings records	holding_id
repositories_repository	RepoCore2 institutional records	repo_id

Vocabulary Tables (8+)

Controlled vocabularies used by profiles:

Table	Purpose	Used By
catalog_subject	Subject terms	Zine
catalog_genre	Genre terms	Zine
agents_agentkind	Agent types	Agent
agents_agentrole	Agent roles	Agent
repositories_repositorykind	Repository types	Repository
holdings_accessstatus	Access status codes	Holding
vocabularies_rightsstatus	Rights statements	Zine
vocabularies_format	Production formats	Zine

Geographic Tables (3)

GeoNames-based geographic data:

Table	Purpose
geography_geoplace	Places (cities, regions, countries)
geography_country	ISO 3166 country codes
geography_language	ISO 639 language codes

Profile Table Schemas

catalog_zine

ZineCore2 bibliographic records.

CREATE TABLE catalog_zine (
  -- Identifiers
  id BIGSERIAL PRIMARY KEY,
  zine_id VARCHAR(255) UNIQUE NOT NULL,

  -- Required fields
  title VARCHAR(500) NOT NULL,
  creator TEXT[] NOT NULL,
  subject TEXT[] NOT NULL,
  genre TEXT[] NOT NULL,
  date TEXT[] NOT NULL,
  language TEXT[] NOT NULL,
  rights TEXT[] NOT NULL,

  -- Optional fields
  series_title TEXT[],
  issue_designation VARCHAR(100),
  edition_statement TEXT[],
  alternative_title TEXT[],
  contributor TEXT[],
  abstract TEXT,
  table_of_contents TEXT,
  public_notes TEXT[],
  publisher TEXT[],
  physical_dimensions VARCHAR(100),
  number_of_pages VARCHAR(50),
  format TEXT[],
  binding_features TEXT[],
  place_of_publication TEXT[],
  coverage TEXT[],
  source TEXT[],
  relation TEXT[],
  identifier TEXT[],

  -- Timestamps
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_zine_id ON catalog_zine(zine_id);
CREATE INDEX idx_zine_title ON catalog_zine(title);
CREATE INDEX idx_zine_creator ON catalog_zine USING GIN(creator);
CREATE INDEX idx_zine_subject ON catalog_zine USING GIN(subject);
CREATE INDEX idx_zine_genre ON catalog_zine USING GIN(genre);
CREATE INDEX idx_zine_created_at ON catalog_zine(created_at DESC);

Key features:

TEXT[] for all repeatable elements (creator, subject, etc.)
GIN indexes for fast array searches
Unique constraint on zine_id

Storage notes:

creator, contributor, publisher store agent IDs (not full objects)
subject, genre, rights, format store vocabulary codes (not full terms)
Array fields default to [] (empty array) when not provided

agents_agent

AgentCore2 authority records.

CREATE TABLE agents_agent (
  -- Identifiers
  id BIGSERIAL PRIMARY KEY,
  agent_id VARCHAR(255) UNIQUE NOT NULL,

  -- Required fields
  kind VARCHAR(50) NOT NULL,
  display_name VARCHAR(500) NOT NULL,
  public BOOLEAN NOT NULL DEFAULT TRUE,

  -- Optional fields
  legal_name VARCHAR(500),
  alternative_names TEXT[],
  sort_name VARCHAR(500),
  pronouns TEXT[],
  biography TEXT,
  scope_note TEXT[],
  roles TEXT[],
  orcid VARCHAR(255),
  wikidata VARCHAR(50),
  other_identifiers TEXT[],
  active_dates TEXT[],
  location TEXT[],
  website VARCHAR(255),
  email VARCHAR(254),
  social_media TEXT[],

  -- Timestamps
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_agent_id ON agents_agent(agent_id);
CREATE INDEX idx_agent_display_name ON agents_agent(display_name);
CREATE INDEX idx_agent_kind ON agents_agent(kind);
CREATE INDEX idx_agent_public ON agents_agent(public);

Privacy features:

public field controls visibility
legal_name, email can be kept private
Serializers filter based on public field

holdings_holding

HoldingCore2 holdings records.

CREATE TABLE holdings_holding (
  -- Identifiers
  id BIGSERIAL PRIMARY KEY,
  holding_id VARCHAR(255) UNIQUE NOT NULL,

  -- Foreign keys (REQUIRED)
  zine_id BIGINT NOT NULL REFERENCES catalog_zine(id) ON DELETE CASCADE,
  repository_id BIGINT NOT NULL REFERENCES repositories_repository(id) ON DELETE CASCADE,

  -- Optional fields
  call_number VARCHAR(255),
  location VARCHAR(500),
  access_status VARCHAR(100),
  condition TEXT,
  copy_count INTEGER NOT NULL DEFAULT 1,
  barcode VARCHAR(255),
  digital_available BOOLEAN NOT NULL DEFAULT FALSE,
  digital_url VARCHAR(255),
  distro_status VARCHAR(100),
  notes TEXT[],

  -- Timestamps
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_holding_id ON holdings_holding(holding_id);
CREATE INDEX idx_holding_zine ON holdings_holding(zine_id);
CREATE INDEX idx_holding_repository ON holdings_holding(repository_id);
CREATE INDEX idx_holding_zine_repo ON holdings_holding(zine_id, repository_id);
CREATE INDEX idx_holding_access_status ON holdings_holding(access_status);

Foreign key relationships:

zine_id → catalog_zine.id (required)
repository_id → repositories_repository.id (required)
ON DELETE CASCADE — deleting a zine or repository deletes its holdings

Composite index:

(zine_id, repository_id) — fast queries like "all holdings of this zine at this repo"

repositories_repository

RepoCore2 institutional records.

CREATE TABLE repositories_repository (
  -- Identifiers
  id BIGSERIAL PRIMARY KEY,
  repo_id VARCHAR(255) UNIQUE NOT NULL,

  -- Required fields
  repository_name VARCHAR(500) NOT NULL,
  repository_kind VARCHAR(100) NOT NULL,
  country VARCHAR(2) NOT NULL,

  -- Optional fields
  alternative_names TEXT[],
  description TEXT,
  scope_note TEXT,
  marc_org_code VARCHAR(20),
  isil VARCHAR(50),
  ror VARCHAR(255),
  city VARCHAR(255),
  region VARCHAR(255),
  postal_code VARCHAR(20),
  website VARCHAR(255),
  email VARCHAR(254),
  phone VARCHAR(50),
  social_media TEXT[],
  holdings_count INTEGER,
  established VARCHAR(10),
  status VARCHAR(50) NOT NULL DEFAULT 'active',

  -- Timestamps
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_repo_id ON repositories_repository(repo_id);
CREATE INDEX idx_repo_name ON repositories_repository(repository_name);
CREATE INDEX idx_repo_kind ON repositories_repository(repository_kind);
CREATE INDEX idx_repo_country ON repositories_repository(country);
CREATE INDEX idx_repo_status ON repositories_repository(status);

No foreign keys — repositories are independent entities.

Vocabulary Table Schema

All vocabularies share the same structure (from BaseVocabulary):

CREATE TABLE catalog_subject (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(100) UNIQUE NOT NULL,
  label VARCHAR(255) NOT NULL,
  definition TEXT,
  uri VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_subject_code ON catalog_subject(code);
CREATE INDEX idx_subject_label ON catalog_subject(label);

Same structure for:

catalog_genre
agents_agentkind
agents_agentrole
repositories_repositorykind
holdings_accessstatus
vocabularies_rightsstatus
vocabularies_format

Vocabulary fields:

code — Unique identifier (e.g., feminism)
label — Display name (e.g., Feminism)
definition — Term definition
uri — Full URI (e.g., https://zinecore.org/v2/subjects#feminism)

Geographic Tables

geography_geoplace

GeoNames-based places (cities, regions, countries).

CREATE TABLE geography_geoplace (
  id BIGSERIAL PRIMARY KEY,
  geonames_id INTEGER UNIQUE,
  name VARCHAR(200) NOT NULL,
  ascii_name VARCHAR(200),
  alternate_names TEXT,
  latitude DECIMAL(10, 7),
  longitude DECIMAL(10, 7),
  feature_class VARCHAR(1),
  feature_code VARCHAR(10),
  country_code VARCHAR(2),
  admin1_code VARCHAR(20),
  population BIGINT,
  elevation INTEGER,
  timezone VARCHAR(40),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_geoplace_geonames ON geography_geoplace(geonames_id);
CREATE INDEX idx_geoplace_name ON geography_geoplace(name);
CREATE INDEX idx_geoplace_country ON geography_geoplace(country_code);
CREATE INDEX idx_geoplace_coords ON geography_geoplace(latitude, longitude);

geography_country

ISO 3166 country codes.

CREATE TABLE geography_country (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(2) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  official_name VARCHAR(255),
  alpha3_code VARCHAR(3),
  numeric_code VARCHAR(3),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_country_code ON geography_country(code);
CREATE INDEX idx_country_name ON geography_country(name);

geography_language

ISO 639 language codes.

CREATE TABLE geography_language (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(10) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  native_name VARCHAR(255),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_language_code ON geography_language(code);
CREATE INDEX idx_language_name ON geography_language(name);

Relationships Diagram

┌─────────────────────────────────────────────────────────────┐
│                     repositories_repository                 │
│  • id (PK)                                                  │
│  • repo_id (unique)                                         │
│  • repository_name                                          │
└───────────────────────────┬─────────────────────────────────┘
                            │
                            │ FK: repository_id
                            │
┌───────────────────────────▼─────────────────────────────────┐
│                      holdings_holding                       │
│  • id (PK)                                                  │
│  • holding_id (unique)                                      │
│  • zine_id (FK → catalog_zine.id)                          │
│  • repository_id (FK → repositories_repository.id)         │
└───────────────────────────┬─────────────────────────────────┘
                            │
                            │ FK: zine_id
                            │
┌───────────────────────────▼─────────────────────────────────┐
│                       catalog_zine                          │
│  • id (PK)                                                  │
│  • zine_id (unique)                                         │
│  • creator[] (agent IDs)                                    │
│  • contributor[] (agent IDs)                                │
│  • publisher[] (agent IDs)                                  │
│  • subject[] (subject codes)                                │
│  • genre[] (genre codes)                                    │
└─────────────────────────────────────────────────────────────┘
                            │
                            │ References (via creator[])
                            │
┌───────────────────────────▼─────────────────────────────────┐
│                       agents_agent                          │
│  • id (PK)                                                  │
│  • agent_id (unique)                                        │
│  • display_name                                             │
└─────────────────────────────────────────────────────────────┘

Key relationships:

Holding → Zine (many-to-one via zine_id)
Holding → Repository (many-to-one via repository_id)
Zine → Agent (many-to-many via creator[] array)

No join tables — ArrayField stores agent IDs directly in zine table.

Indexes

Primary Key Indexes (Automatic)

All id columns have automatic indexes:

CREATE UNIQUE INDEX catalog_zine_pkey ON catalog_zine(id);
CREATE UNIQUE INDEX agents_agent_pkey ON agents_agent(id);
CREATE UNIQUE INDEX holdings_holding_pkey ON holdings_holding(id);
CREATE UNIQUE INDEX repositories_repository_pkey ON repositories_repository(id);

External ID Indexes (Explicit)

All external IDs are indexed:

CREATE UNIQUE INDEX idx_zine_id ON catalog_zine(zine_id);
CREATE UNIQUE INDEX idx_agent_id ON agents_agent(agent_id);
CREATE UNIQUE INDEX idx_holding_id ON holdings_holding(holding_id);
CREATE UNIQUE INDEX idx_repo_id ON repositories_repository(repo_id);

Purpose: Fast lookups for API requests using external IDs.

Foreign Key Indexes (Explicit)

All foreign keys are indexed:

CREATE INDEX idx_holding_zine ON holdings_holding(zine_id);
CREATE INDEX idx_holding_repository ON holdings_holding(repository_id);
CREATE INDEX idx_holding_zine_repo ON holdings_holding(zine_id, repository_id);

Purpose: Fast joins and cascading deletes.

Array Indexes (GIN)

ArrayField columns use GIN indexes for efficient array searches:

CREATE INDEX idx_zine_creator ON catalog_zine USING GIN(creator);
CREATE INDEX idx_zine_subject ON catalog_zine USING GIN(subject);
CREATE INDEX idx_zine_genre ON catalog_zine USING GIN(genre);

Purpose: Fast queries like "all zines with subject code 'feminism'".

Example query:

SELECT * FROM catalog_zine
WHERE 'feminism' = ANY(subject);

GIN index makes this fast.

Timestamp Indexes

Created timestamps are indexed for sorting:

CREATE INDEX idx_zine_created_at ON catalog_zine(created_at DESC);
CREATE INDEX idx_agent_created_at ON agents_agent(created_at DESC);
CREATE INDEX idx_holding_created_at ON holdings_holding(created_at DESC);
CREATE INDEX idx_repo_created_at ON repositories_repository(created_at DESC);

Purpose: Fast "recent records" queries.

Constraints

Unique Constraints

All external IDs are unique:

ALTER TABLE catalog_zine ADD CONSTRAINT uq_zine_id UNIQUE (zine_id);
ALTER TABLE agents_agent ADD CONSTRAINT uq_agent_id UNIQUE (agent_id);
ALTER TABLE holdings_holding ADD CONSTRAINT uq_holding_id UNIQUE (holding_id);
ALTER TABLE repositories_repository ADD CONSTRAINT uq_repo_id UNIQUE (repo_id);

Vocabulary codes are unique:

ALTER TABLE catalog_subject ADD CONSTRAINT uq_subject_code UNIQUE (code);
ALTER TABLE catalog_genre ADD CONSTRAINT uq_genre_code UNIQUE (code);
-- ... same for all vocabularies

NOT NULL Constraints

Required fields have NOT NULL constraints:

ALTER TABLE catalog_zine ALTER COLUMN title SET NOT NULL;
ALTER TABLE catalog_zine ALTER COLUMN creator SET NOT NULL;
ALTER TABLE catalog_zine ALTER COLUMN subject SET NOT NULL;
-- ... all required fields

Foreign Key Constraints

Holdings must reference valid zine and repository:

ALTER TABLE holdings_holding
  ADD CONSTRAINT fk_holding_zine
  FOREIGN KEY (zine_id) REFERENCES catalog_zine(id) ON DELETE CASCADE;

ALTER TABLE holdings_holding
  ADD CONSTRAINT fk_holding_repository
  FOREIGN KEY (repository_id) REFERENCES repositories_repository(id) ON DELETE CASCADE;

Cascade behavior:

Deleting a zine deletes all its holdings
Deleting a repository deletes all its holdings

Query Performance

Example 1: Get Zine by External ID

SELECT * FROM catalog_zine
WHERE zine_id = 'zine_mutate_3_1st';

Uses: idx_zine_id (unique index) Performance: O(log n) — very fast

Example 2: Get All Zines by Subject

SELECT * FROM catalog_zine
WHERE 'feminism' = ANY(subject);

Uses: idx_zine_subject (GIN index) Performance: O(log n) — fast

Example 3: Get Holdings for Zine

SELECT h.*, r.repository_name
FROM holdings_holding h
JOIN repositories_repository r ON h.repository_id = r.id
WHERE h.zine_id = 1842;

Uses:

idx_holding_zine for WHERE clause
Primary key index for join

Performance: O(log n + m) where m = number of holdings

Example 4: Get All Holdings for Repository

SELECT h.*, z.title
FROM holdings_holding h
JOIN catalog_zine z ON h.zine_id = z.id
WHERE h.repository_id = 42;

Uses:

idx_holding_repository for WHERE clause
Primary key index for join

Performance: O(log n + m)

Example 5: Recent Zines

SELECT * FROM catalog_zine
ORDER BY created_at DESC
LIMIT 25;

Uses: idx_zine_created_at (descending index) Performance: O(1) — index scan + limit

Storage Estimates

Sample Database (100,000 records)

Assumptions:

100,000 zines
50,000 agents
500 repositories
150,000 holdings

Table sizes:

Table	Rows	Avg Row Size	Total Size
catalog_zine	100,000	~2 KB	200 MB
agents_agent	50,000	~1 KB	50 MB
holdings_holding	150,000	~500 bytes	75 MB
repositories_repository	500	~1 KB	0.5 MB
Vocabularies	~1,000	~200 bytes	0.2 MB
Geographic	~100,000	~500 bytes	50 MB

Total: ~375 MB

With indexes: ~750 MB (indexes typically 2x table size)

Large deployment (1M zines): ~7.5 GB

Migrations

Django migrations create all tables:

# Generate migrations
python manage.py makemigrations

# Apply migrations
python manage.py migrate

Migration order:

core (base models)
geography (no dependencies)
vocabularies (no dependencies)
agents (no dependencies)
repositories (no dependencies)
catalog (references agents via ArrayField)
holdings (references catalog and repositories)

Backup and Restore

Backup

# Full database dump
pg_dump -U zinecore2 -d zinecore2 > backup.sql

# Schema only
pg_dump -U zinecore2 -d zinecore2 --schema-only > schema.sql

# Data only
pg_dump -U zinecore2 -d zinecore2 --data-only > data.sql

Restore

# Restore full dump
psql -U zinecore2 -d zinecore2 < backup.sql

# Restore schema then data
psql -U zinecore2 -d zinecore2 < schema.sql
psql -U zinecore2 -d zinecore2 < data.sql

Database Maintenance

Analyze Tables (Update Statistics)

ANALYZE catalog_zine;
ANALYZE agents_agent;
ANALYZE holdings_holding;
ANALYZE repositories_repository;

Run after bulk imports.

Reindex

REINDEX TABLE catalog_zine;
REINDEX TABLE agents_agent;

Run if index performance degrades.

Vacuum

VACUUM ANALYZE catalog_zine;
VACUUM ANALYZE agents_agent;

Reclaim space after bulk deletes.

Next Steps

Models — Django model definitions
Serializers — How read/write serializers work
API Reference — Complete endpoint documentation

Understanding the database schema? Continue to API Reference to explore all API endpoints.

Edit this pageorReport an issue

External Identifiers

Why ZineCore2 uses separate internal and external identifiers

API Reference

Complete reference for all ZineCore2 API endpoints