Public Sector & Nonprofit · data model template

Citizen Services 311 & Permits data model

Third-normal-form mirror of a 311 / permitting source system. Each operational table becomes its own entity with a warehouse surrogate key plus the genuine business key (Open311 service_request_id, permit_application_id, inspection_id, violation_id, feedback_id). Citizen PII (name, email, address, phone) is masked under FOIA / GDPR records-retention rules; lat/long is preserved at request grain for spatial reporting on caseload and SLA performance.

Normalized replica of a municipal citizen-services platform: Open311 service requests (type, location, description, status lifecycle), permit applications and inspections, code-enforcement violations, and post-resolution citizen satisfaction feedback. Reference data (agency, service catalog, channel, status, priority, inspection type, permit type) is held in slow-changing lookup tables and joined to the transactional intake, permits/inspections, compliance and feedback subject areas. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.

3NFPublic Sector & NonprofitFOIAGDPR

14
tables: 116
columns: 18
relationships: 11
PII columns

Use this template About database schema design →

Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.

Subject areas

master_dataintakepermits_inspectionscompliancefeedback

Design decisions

Open311 service-request lifecycle modeled with status-update history
Open311 represents a service request as a single record whose status evolves; splitting service_request_update into its own entity preserves the full lifecycle (open → in-progress → closed/cancelled/rejected) so SLA breach and reopen-rate reporting can replay every transition.
Permit → inspection lifecycle kept as parent/child entities
A permit application typically requires one or more inspections of different disciplines (building, electrical, plumbing, ...); modeling permit_inspection as a child of permit_application with an inspection_type lookup mirrors how permitting systems store the schedule of visits and their pass/fail outcomes.
Citizen as the single party across SR, permits and feedback
The same citizen can file a service request, apply for a permit and submit satisfaction feedback; keeping a single citizen entity referenced by all three subject areas avoids duplicating PII and lets analytics stitch one resident's journey across the portfolio.
Spatial reporting captured at request grain
Latitude and longitude are stored on service_request itself (not promoted to a separate location dimension) so heat-map and district rollups can group requests geographically without an extra join — matching Open311's location_lat / location_long fields.
FOIA / records-retention framing for PII
Citizen name, address, phone, email and free-text feedback are subject to FOIA disclosure rules and GDPR records-retention; classifying these as PII with partial masking ensures responsive records can be produced without leaking unrelated PII in extracts.

Tables in the Citizen Services 311 & Permits model

Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.

Tables (7)

citizen

Grain: one row per registered or anonymous citizen

Citizen party — single record across SR, permit and feedback subject areas.

Column	Type	Keys
citizen_key	bigint	PK
citizen_id	varchar	BK
first_name	varchar	PII
last_name	varchar	PII
email	varchar	PII
phone	varchar	PII
mailing_address	varchar	PII
preferred_language	varchar
is_anonymous	boolean
registration_date	date

service_request

Grain: one row per Open311 service request (service_request_id)

Open311 service request — the canonical citizen-reported case.

Column	Type	Keys
service_request_key	bigint	PK
citizen_key	bigint
agency_key	bigint
service_type_key	bigint
channel_key	bigint
status_key	bigint
priority_key	bigint
service_request_id	varchar	BK
reported_at	timestamp
location_latitude	decimal
location_longitude	decimal
incident_address	varchar	PII
district_council	varchar
description	varchar
parent_service_request_key	bigint
expected_resolution_date	date
closed_at	timestamp
resolution_minutes	integer

service_request_update

Grain: one row per status update on a service request

Status-update history — replays the lifecycle of a service request.

Column	Type	Keys
service_request_update_key	bigint	PK
service_request_key	bigint
status_key	bigint
update_id	varchar	BK
updated_at	timestamp
updated_by	varchar	PII
update_note	varchar
channel_used	varchar

permit_application

Grain: one row per permit application

Permit application — the lifecycle record from submission through issuance.

Column	Type	Keys
permit_application_key	bigint	PK
citizen_key	bigint
agency_key	bigint
permit_type_key	bigint
permit_application_id	varchar	BK
applied_at	timestamp
expected_issue_date	date
issued_at	timestamp
expired_at	timestamp
total_fee	decimal
paid_amount	decimal
currency	varchar
application_status	varchar
project_address	varchar	PII

permit_inspection

Grain: one row per inspection scheduled against a permit

Inspection scheduled against a permit application — captures permit→inspection lifecycle.

Column	Type	Keys
permit_inspection_key	bigint	PK
permit_application_key	bigint
inspection_type_key	bigint
inspection_id	varchar	BK
scheduled_date	date
performed_date	date
inspector_name	varchar	PII
inspection_result	varchar
follow_up_required	smallint
notes	varchar
parent_inspection_key	bigint

code_violation

Grain: one row per code-enforcement citation

Code-enforcement citation arising from a service request.

Column	Type	Keys
code_violation_key	bigint	PK
service_request_key	bigint
agency_key	bigint
violation_id	varchar	BK
citation_number	varchar
violation_code	varchar
violation_description	varchar
issued_date	date
fine_amount	decimal
paid_amount	decimal
currency	varchar
violation_status	varchar
cited_party	varchar	PII

citizen_satisfaction

Grain: one row per satisfaction feedback submission

Post-resolution citizen satisfaction feedback tied back to the originating SR.

Column	Type	Keys
citizen_satisfaction_key	bigint	PK
service_request_key	bigint
citizen_key	bigint
channel_key	bigint
feedback_id	varchar	BK
submitted_at	timestamp
satisfaction_score	smallint
would_recommend	smallint
feedback_text	varchar	PII

Lookup tables (7)

agency

Grain: one row per municipal agency / department

Municipal agency responsible for handling service requests and permits.

Column	Type	Keys
agency_key	bigint	PK
agency_id	varchar	BK
agency_name	varchar
department	varchar
jurisdiction	varchar

service_type

Grain: one row per Open311 service type

Open311 service catalog — the set of request types citizens can report.

Column	Type	Keys
service_type_key	bigint	PK
service_code	varchar	BK
service_name	varchar
service_category	varchar
jurisdiction	varchar

channel

Grain: one row per intake channel

Origination channel through which a request, update or feedback was submitted.

Column	Type	Keys
channel_key	bigint	PK
channel_id	varchar	BK
channel_name	varchar
is_digital	boolean

status

Grain: one row per service-request lifecycle status

Open311 service-request lifecycle status values.

Column	Type	Keys
status_key	bigint	PK
status_id	varchar	BK
status_name	varchar
is_terminal	boolean
is_open	boolean

inspection_type

Grain: one row per inspection type

Catalog of inspection disciplines performed against permitted projects.

Column	Type	Keys
inspection_type_key	bigint	PK
inspection_type_id	varchar	BK
description	varchar
requires_followup	boolean

permit_type

Grain: one row per permit type

Permit catalog — the set of permits citizens / businesses can apply for.

Column	Type	Keys
permit_type_key	bigint	PK
permit_type_id	varchar	BK
permit_name	varchar
fee_amount	decimal
requires_inspection	boolean

priority

Grain: one row per service-request priority tier

Priority tier with associated SLA response and resolution targets.

Column	Type	Keys
priority_key	bigint	PK
priority_id	varchar	BK
priority_name	varchar
sla_response_hours	integer
sla_resolution_hours	integer

Relationships

Each relationship is a foreign key on the first table referencing the second.

service_request.citizen_key → citizen (many-to-one)
service_request.agency_key → agency (many-to-one)
service_request.service_type_key → service_type (many-to-one)
service_request.channel_key → channel (many-to-one)
service_request.status_key → status (many-to-one)
service_request.priority_key → priority (many-to-one)
service_request_update.service_request_key → service_request (many-to-one)
service_request_update.status_key → status (many-to-one)
permit_application.citizen_key → citizen (many-to-one)
permit_application.agency_key → agency (many-to-one)
permit_application.permit_type_key → permit_type (many-to-one)
permit_inspection.permit_application_key → permit_application (many-to-one)
permit_inspection.inspection_type_key → inspection_type (many-to-one)
code_violation.service_request_key → service_request (many-to-one)
code_violation.agency_key → agency (many-to-one)
citizen_satisfaction.service_request_key → service_request (many-to-one)
citizen_satisfaction.citizen_key → citizen (many-to-one)
citizen_satisfaction.channel_key → channel (many-to-one)

Related templates

All templates →

Start for $1

From requirements to AI-ready context, in one platform.

Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.

Get started Book a demo

Plans from $1/month · cancel any time