Canonical definitions for every entity in the Territori database. Source of truth for naming, scope, and relationships. Field names finalized through the schema review session — encoding in Prisma via T-19.
Entities
37
across 5 groups
Status
Draft
DB aligned · v1.1
MVP scope
ONP
Olympic National Park Gateway
Next step
T-19
Prisma schema + PostGIS migration
Group 1 — People & Access
U
Users
A person who has created a Territori account. Role determines what they can see and do. Operators are the primary user type at MVP. Additional roles (investor, broker, architect) are in the backlog and will be scoped in a future story.
Auth · MVP
Key Fields
id
UUID primary key
email
unique, verified
name
display name
role
Operator | Admin | Investor | Advisor
lifecycle
prospect | commissioned | delivered | live_seat | paused | churned — drives transactional email triggers at each stage transition
Roles: Operator is the primary paying user. Admin is Janice — internal, with full system access including user management and data infrastructure. Investor and Advisor cover the secondary paths from US-04: capital allocators and anyone advising or scouting on an operator's behalf (brokers, architects, researchers). No Scout role — that use case falls under Advisor.
Lifecycle: Starts at sign-up — no DB record exists for anonymous visitors. Each stage transition drives a transactional email: prospect → welcome; commissioned → report in progress; delivered → report ready + 14-day check-in (US-56); live_seat → onboarding + weekly digest; paused/churned → retention and win-back flows.
S
Subscriptions
Links a User to a Market with a billing tier and lifecycle state. A Market has many Subscriptions for two reasons: multiple Users may independently subscribe to the same Market, and the same User may hold a report-tier record and later upgrade to a live seat — each is a distinct record.
Billing · MVP
Key Fields
id
UUID primary key
user_id
FK → Users
market_id
FK → Markets
tier
report | live_seat
status
active | paused | cancelled
started_at
timestamp
renews_at
timestamp — null for report tier
Relationships
belongs to one
Users
belongs to one
Markets
has many
Studies — live seat generates periodic snapshots
Group 2 — Geography
A
Anchors GIS
A primary demand generator that a Market orbits. For ONP Gateway, the Anchor is Olympic National Park. A Market may have multiple Anchors. Anchors provide visitor volume, seasonality, and accessibility data used to calculate tourism saturation and contextualize premium pricing potential.
Reference · MVP
Key Fields
id
UUID primary key
name
e.g. "Olympic National Park"
type
national_park | state_park | monument | urban_core | other
state_id
FK → States (optional) — links to the state this anchor resides in, enabling queries like "all national parks in WA." Nullable until States table is seeded.
location
geography(Point, 4326) — anchor centroid, WGS84
annual_visitors
integer — cached annual total, computed from AnchorVisitorStats. Updated whenever new stats are ingested.
seasonality_curve
JSON — monthly index, 12 values summing to 100
drive_score
integer 0–100 — drivability from major population centers
flight_score
integer 0–100 — short-flight accessibility from dense metros
pop_1hr
integer — population within 1-hour drive; aligns with 60-min Isochrone ring
pop_2hr
integer — population within 2-hour drive
pop_3hr
integer — population within 3-hour drive; broadest catchment band
source_url
URL — visitor data source
Relationships
belongs to one
States (optional) — for state-scoped queries, e.g. "all national parks in WA"
belongs to many
Markets — one anchor can seed multiple markets
has many
AnchorVisitorStats — monthly and annual visitor counts; source of truth for annual_visitors
has many
Isochrones — 15/30/60-minute drive-time rings generated from this anchor
has many
GuestProfiles — demand profiles scoped to this anchor for anchor-level DPI
GIS — Isochrones: Mark's drive-time rings (T-15) need a permanent table in the schema. Each ring is stored as a geography(Polygon,4326) on the Isochrones entity below, keyed to this Anchor with a minutes field (15, 30, 60). Used by the MapLibre map (T-25) and future whitespace queries such as "show unbuilt parcels within 30 minutes of the anchor."
Saturation formula:Anchor.annual_visitors ÷ SUM(Property.keys) for all properties in the Market. Computed at query time.
drive_score vs flight_score: Mt. Rainier sits between Portland and Seattle, so it scores higher on drive_score than ONP despite potentially lower raw visitation. A high flight_score means visitors absorb travel friction — a signal that supports premium pricing.
AVS
AnchorVisitorStats
Time-series visitor counts for an Anchor. Stores both monthly records (for trend analysis and seasonality) and annual totals. The annual_visitors field on Anchors is a cached rollup from this table. Supports year-over-year comparisons and monthly trend charts.
Reference · Post-v0.1
Key Fields
id
UUID primary key
anchor_id
FK → Anchors
year
integer — e.g. 2024
month
integer 1–12 — null for annual records
visitor_count
integer
source_url
URL — original data source (NPS stats page, state tourism board, etc.)
notes
text — optional; flag anomalies e.g. pandemic year, trail closure
Relationships
belongs to one
Anchors
Annual vs monthly: A null month indicates an annual record (the official published total). Monthly records are either sourced directly or derived by distributing the annual total across the seasonality_curve on the Anchor. Annual totals can also be computed by summing monthly records for the same year.
Co
Counties
A US county that falls within or adjacent to a Market catchment. The regulatory unit beneath a Market — each county has its own permitting authority, zoning codes, and STR policy. Permitting friction, approval rates, and regulatory sentiment all belong here. County is the right level of granularity for tracking entry barriers; Markets aggregate across their constituent counties.
Reference · MVP
Key Fields
id
UUID primary key
name
string — e.g. "Clallam"
state_id
FK → State — the state this county belongs to
fips_code
string — Federal Information Processing Standards code; unique county identifier used by census and GIS datasets
slug
string — URL-safe identifier, e.g. "wa-clallam"; used to scope zoning HTML docs and API routes
code_source
URL — canonical county code portal (e.g. https://clallam.county.codes)
code_version
string — last known ordinance version, e.g. "Ord. 1026 · Dec 2024"
integer — average days from CUP filing to building permit issuance; derived from CupEvents + PermitEvents over rolling 24 months
permit_approval_rate
decimal 0–1 — percentage of CUPs that result in an approved permit over rolling 24 months
regulatory_sentiment
decimal 0–100 — derived from NLP sentiment analysis of local news articles, planning commission minutes, and STR policy documents. 0 = highly restrictive, 100 = highly permissive.
zoning_complexity
integer 1–5 — manual or AI-assisted rating of how restrictive and complex the county zoning code is for lodging development
source_url
URL — county planning / DCD portal
Relationships
belongs to one
State
belongs to many
Markets — a market spans one or more counties
has many
ZoneDesignations — all zoning districts defined in this county's code
has many
LandUses — all county-defined hospitality land uses
has many
Parcels — county field on Parcels will become a FK here
has many
PermitEvents — county field on PermitEvents will become a FK here
has many
CupEvents — county field on CupEvents will become a FK here
Entry friction inputs:avg_permit_days, permit_approval_rate, regulatory_sentiment, and str_policy are the county-level components that feed Markets.entry_friction_score. A market spanning four counties aggregates across all four — a single permissive county doesn't offset three restrictive ones.
Migration note: Parcels, PermitEvents, and CupEvents currently carry county as a plain string. Formalizing County as an entity converts these to FKs once the County table is seeded — no data loss, just a join replacement.
code_source / code_version: These fields allow Territori to track when county code was last verified. Clallam County is currently Ord. 1026 (Dec 2024). When a new ordinance passes, update code_version and flag any ZoneLandUseMatrix rows that may need re-verification.
GP
GuestProfiles
A demand-side snapshot of visitors to a Market or Anchor — who they are, where they come from, how they book, and how long they stay. Versioned over time so seasonal and year-over-year shifts are trackable. Scoped to a Market and optionally to a specific Anchor (e.g. ONP visitor profile vs. the broader ONP Gateway market profile). Feeds PCI scoring, ADR underwriting, and Named Category feasibility. Post-MVP — data sourced from AirDNA, Airbnb Insights, or equivalent.
Demand · Post-MVP
Key Fields
id
UUID primary key
market_id
FK → Markets
anchor_id
FK → Anchors (optional) — when present, scopes this profile to a specific anchor within the market. Used to calculate anchor-level DPI (e.g. ONP visitor count vs. ONP Gateway market total).
snapshot_at
timestamp — when this profile was generated; versioned over time
origin_zips
JSON — zip-level origin distribution, e.g. [{zip:"98362", count:142}, ...]. Regions are derived from zips at query time — storing at zip granularity preserves all information. Planned DB migration from originRegions.
avg_booking_window_days
integer — average days between booking date and arrival
avg_stay_duration_nights
decimal — average length of stay in nights
avg_group_size
decimal — average party size
price_tier_distribution
JSON — share of bookings by price tier, e.g. {budget:0.10, mid:0.35, premium:0.40, luxury:0.15}
peak_intent_season
spring | summer | fall | winter — season with highest search and booking volume
intent_fragmentation
decimal 0–100 — how dispersed guest intent is across operators. High = no dominant preference; low = guests seek specific properties by name. Pairs with PCI for demand-side differentiation picture.
source
airdna | airbnb_insights | manual
Relationships
belongs to one
Markets
belongs to one
Anchors (optional) — anchor-scoped profiles for anchor-level DPI
informs
Narratives — demand context for Named Category feasibility and PCI scoring
intent_fragmentation: Captures whether guests arrive with a specific property in mind or browse interchangeably. High fragmentation = undifferentiated market; guests don't care which property they book. Low fragmentation = guests seek specific operators by name — a signal of strong category ownership. Pairs with PCI (ConvergenceSnapshot) and IntentProbes to build a complete picture of demand-side differentiation.
Iso
Isochrones GIS
A drive-time ring polygon generated from an Anchor point — the area reachable by car in 15, 30, or 60 minutes. Produced by Mark's T-15 work using OpenRouteService or Mapbox Isochrone API. Used by the MapLibre map to visualize market reach and by whitespace queries to filter parcels by drive-time proximity.
Spatial · MVP
Key Fields
id
UUID primary key
anchor_id
FK → Anchors — the origin point this ring was generated from
minutes
integer — 15 | 30 | 60
geometry
geography(Polygon, 4326) — drive-time ring boundary. GiST index required. All distance display in ft.
source
openrouteservice | mapbox | qgis
generated_at
timestamp
Relationships
belongs to one
Anchors
used by
Parcels — spatial queries filter parcels by isochrone containment
rendered by
CatchmentMap component (T-25)
GiST index: Required on geometry. Containment queries (ST_Within(parcel.geometry, isochrone.geometry)) will run against all parcels in the market — without a GiST index this full-scans the parcel table on every map load. With the index, PostGIS resolves the spatial tree in milliseconds.
LU
LandUses
A hospitality land use as defined verbatim in the county's zoning code. One row per county-defined use (e.g. "Vacation Rental," "Bed and Breakfast Inn," "Outdoor-oriented Recreation Facility"). This is the legal vocabulary — not Territori's application-level build type labels. The mapping between LandUses and BuildTypes is managed separately via BuildTypeLandUseMap.
Proposed · Not in DB
Key Fields
id
UUID primary key
county_id
FK → Counties — the county whose code defines this term
text — verbatim definition from the county code. Source of truth; never paraphrase.
Relationships
belongs to one
Counties
has many
ZoneLandUseMatrix rows — this term's status across all zone designations
has many
BuildTypeLandUseMap rows — which build types this term maps to
Clallam County hospitality terms (Ord. 1026, Dec 2024): Vacation Rental (§119), Bed and Breakfast Inn (§13), Motel/Hotel (§76), Outdoor-oriented Recreation Facilities (§80), Primitive Campground (§89), RV Park (§97), Master Planned Resort (§66). Lodges (§54) = fraternal organizations — not a hospitality term but potentially interesting for membership-based overnight lodging models.
Work Stay — status unknown: The prior session referenced Work Stay at §33.48 (paid accommodation on a working farm). This was not found in §33.03.010 (the definitions chapter). It may exist in §33.48 as a use standard rather than a defined term, or it may be in the draft ordinance only. Needs verification against the enacted code before seeding.
Not in the code — do not seed: "Boutique Hotel" is not a Clallam County term. "Glamping" is not defined — it falls under Outdoor-oriented Recreation Facilities. "Resort" standalone is not defined — the code only recognizes Master Planned Resort.
Verbatim requirement: The definition field must always contain the verbatim code text. It is the audit trail proving that Territori's UI claims are grounded in actual county law. Do not summarize or paraphrase.
M
Markets GIS
A named geographic catchment that Territori analyzes on a continuously updated basis. "Olympic National Park Gateway" is the MVP Market. Everything — operators, parcels, scores, narratives — is scoped to a Market. A Market is the unit of subscription.
Core · MVP
Key Fields
id
UUID primary key
name
e.g. "Olympic National Park Gateway"
slug
URL-safe identifier, e.g. onp-gateway
catchment
geography(Polygon, 4326) — market boundary, stored in WGS84. All area calculations return sq ft at query time.
state
string[] — US state(s) the market falls within, e.g. ["WA"]. Array to handle markets that straddle a state line.
status
active | archived
entry_friction_score
integer 0–100 — composite score; how hard it is for a new operator to enter this market. Rolled up from component fields below. Post-v0.1.
avg_permit_days
integer — average days from permit application to approval, aggregated across constituent Counties. Post-v0.1.
permit_approval_rate
decimal 0–1 — share of lodging permit applications approved in the last 3 years, aggregated across constituent Counties. Post-v0.1.
regulatory_sentiment
integer 0–100 — AI-scored friendliness of county planning language toward new lodging development, aggregated across constituent Counties. Post-v0.1.
Counties — many-to-many via MarketCounties join table; a market spans one or more counties, a county may belong to multiple markets
has many
Anchors — a market may orbit multiple demand generators
has many
Operators
has many
Parcels — many-to-many; see Parcels note
has many
PermitEvents
has many
CupEvents
has many
Narratives — one per generation run, accumulate over time
has many
Subscriptions
has many
Studies
has many
Comps — market-level aggregate ADR
Open — market type taxonomy: Mark flagged adding a type field (e.g. National Park Gateway, Coastal, Mountain Resort, Urban). Agreed in principle — needs a proposed vocabulary before it goes in the schema.
No counties field: The denormalized counties string[] array has been removed. County membership is managed through the MarketCounties join table (same pattern as MarketParcels), keeping the Counties entity as the single source of truth for county data.
GIS:catchment must use geography(Polygon,4326) — not plain geometry — so area and distance calculations return real-world values (converted to sq ft and ft for display). Requires a GiST spatial index. A GiST index is a Generalized Search Tree: a spatial index structure that lets PostGIS quickly find which parcels overlap the catchment without scanning every row. For MVP single-market, a simple polygon is fine; add the MarketParcels join table before adding a second market.
Why many Narratives: A new Narrative is created each generation run, preserving the history of how the category story evolves — essential for live seat subscribers who want to see what moved week over week.
MC
MarketCounties
Junction table that resolves the many-to-many relationship between Markets and Counties. A market spans one or more counties; a county can belong to multiple markets. In the DB as MarketCounty (singular, Prisma convention).
Core · MVP
Key Fields
market_id
FK → Markets — composite PK
county_id
FK → Counties — composite PK
Relationships
belongs to one
Markets
belongs to one
Counties
St
States
A US state. Sits at the top of the regulatory hierarchy: States → Counties → ZoneDesignations → LandUses. Included so that zoning and land use data can be correctly scoped when Territori expands to markets in multiple states — a zone code like "RCC5" may mean different things in Washington vs. Oregon.
Proposed · Not in DB
Key Fields
id
UUID primary key
name
string — e.g. "Washington"
abbr
string — 2-character USPS abbreviation, e.g. "WA"
Relationships
has many
Counties — a state contains many counties
MVP shortcut: At MVP, Counties.state can remain a plain string abbreviation ("WA"). Formalizing the State entity and adding state_id FK to Counties is a near-term cleanup — do it when the first multi-state market is added.
St
Studies
A point-in-time frozen snapshot of a Market, commissioned as a one-time $3k report. Immutable once delivered. A Study captures Market state at a specific date and links to the Narrative generated for that delivery. Distinct from a Market, which is the living subscription product.
Commerce · MVP
Key Fields
id
UUID primary key
market_id
FK → Markets
user_id
FK → Users — commissioner
snapshot_at
timestamp — when data was frozen
delivered_at
timestamp — when PDF was sent
status
commissioned | in_progress | delivered
narrative_id
FK → Narratives
pdf_url
URL — stored deliverable
Relationships
belongs to one
Markets
belongs to one
Users
has one
Narratives
ZD
ZoneDesignations
A single zoning district as defined in a county's zoning code — the canonical form that determines what land uses are permitted on any parcel assigned to it. One row per zoning district per county. Bridges raw GIS export codes (abbreviated and inconsistent across counties) to the official designation names and code sections. A parcel belongs to exactly one ZoneDesignation. The normalization from GIS code to canonical code currently lives in the TypeScript ZONE_CODE_ALIASES map; this entity formalizes it in the DB.
Proposed · Not in DB
Key Fields
id
UUID primary key
county_id
FK → Counties
gis_code
string — the abbreviated code as exported by county GIS (e.g. "R5", "RC", "UL-C2"). May differ from canonical_code.
canonical_code
string — the official code from Title 33 (e.g. "RCC5", "RC"). Used as the stable join key.
full_name
string — human-readable district name (e.g. "Rural Community Commercial, 5-acre min")
decimal? — minimum parcel size for this district; null for urban or non-acreage zones
Relationships
belongs to one
Counties
has many
Parcels — each parcel is assigned exactly one ZoneDesignation
has many
ZoneLandUseMatrix rows — one per land use term for this zone
GIS normalization: County GIS exports often use abbreviated codes ("R5", "UL") that don't match the code text verbatim. The gis_code → canonical_code mapping lives here in the database. At MVP this is handled by the TypeScript ZONE_CODE_ALIASES map; this entity formalizes it in the DB.
All zone codes, not just rural: Clallam County has 33 zone designations including Urban Low, Urban Medium, Urban High, Rural Community Commercial, Industrial, Agricultural Forestry, Critical Areas, Shoreline. The UI only surfaces zones that appear on actual parcels, but the full table is seeded for completeness.
Group 3 — Operators & Supply
BT
BuildTypes
Territori's application-level taxonomy of hospitality build types — the labels that operators actually think in. Distinct from LandUses (which uses county legal vocabulary). BuildTypes has no county FK — these are Territori's own categories, designed to work across counties and states. The mapping to county LandUses is managed via BuildTypeLandUseMap.
string — display name · BnB | Campground | Hotel | Members Club | Resort (MP) | Resort | RV Park | STR | Work-Stay
description
text? — editorial description for the UI
Relationships
has many
BuildTypeLandUseMap — maps to one or more county land use terms
Confirmed build types (9 total, alphabetical by slug): BnB, Campground, Hotel, Members Club (slug: lodge), Resort, Resort (MP), RV Park, STR, Work-Stay. Boutique Hotel folds into Hotel; Glamping folds into Resort. See internal/zoning-wa-clallam.html §03 for the full CCC land use mapping.
No county FK by design: BuildType is an editorial judgment, not a legal definition. "Resort" in Territori maps to Outdoor-oriented Recreation Facility for lighter operations; "Resort (MP)" maps to Master Planned Resort for the full PUD pathway. The mapping lives in TypeScript and is documented in zoning-wa-clallam.html — it does not belong in the DB.
Members Club (slug: lodge): CCC "Lodges" (§33.03.010(54)) refers to fraternal organizations. Membership-based overnight lodging may qualify — legal review required before relying on this pathway. The slug remains "lodge" to match the CCC term in zone-land-use-data.ts.
BM
BuildTypeLandUseMap
Bridge table connecting Territori's application-level BuildTypes to county-legal LandUses. One row for every build type × land use term pairing that is meaningful for display or filtering. The is_primary flag marks the dominant mapping (e.g. Resort → Outdoor-oriented Recreation Facilities is primary; Resort → Master Planned Resort is secondary, for the largest-scale operations).
Proposed · Not in DB
Key Fields
id
UUID primary key
build_type_id
FK → BuildType
land_use_id
FK → LandUses
is_primary
boolean — true if this is the dominant county term for this build type
notes
text? — editorial notes on why this mapping exists or when it applies
Relationships
belongs to one
BuildType
belongs to one
LandUseTerm
Why a bridge table and not a simple FK: Many build types map to multiple county terms. Campground maps to Primitive Campground (primary). Resort maps to Outdoor-oriented Recreation Facility (primary — glamping, eco-camp, treehouse resort, high-end camp requiring only a CUP) and also to Master Planned Resort (secondary — large-scale PUD requiring a CPA). Many county terms can also map to multiple build types. This is a genuine many-to-many relationship. Note: "Resort" is not a Clallam County land use term — the county only defines Outdoor-oriented Recreation Facility and Master Planned Resort.
Static data, not DB: At MVP the mapping is seeded from TypeScript and lives in zone-land-use-data.ts. The DB entity is defined here for the target architecture — the data isn't in the DB yet. The HTML reference docs (internal/zoning-wa-clallam.html) serve as the human-readable audit trail for every mapping decision.
Ca
Categories
A defensible perceptual position held in the mind of the guest — not a zoning class or a physical format. "Forest Cocooning" is a Category. "Glamping" is not — it has become so ubiquitous it functions more like a class. The core unit of Category Intelligence. Scoped to a Market because the same position may be wide-open in one market and saturated in another.
Core IP · MVP
Key Fields
id
UUID primary key
name
e.g. "Forest Cocooning"
definition
prose — what the guest perceives and remembers
market_id
FK → Markets — saturation is market-specific
moat_combination
FK[] → MoatDimensions — dimensions that underpin this category
saturation_level
wide_open | open | contested | saturated
Relationships
scoped to one
Markets
claimed by many
Properties
references many
MoatDimensions
Category vs Land Use: Land use is what the county calls it (hotel, resort, glamping — per Title 33). Category is what the guest calls it (Forest Cocooning, Heritage Anchor). One is regulatory; the other is perceptual. Territori scores Categories, not Land Uses.
Op
Operators GIS
A lodging business operating inside a Market — the business entity, not the physical property. An Operator may run multiple Properties, and each Property may hold a different Category. Lake Crescent Lodge and a family-run glamping site are both Operators. Segmentation lives at the Property level via class and Category — not on the Operator. Most operators will have a website; booking URLs belong to Properties.
Core · MVP
Key Fields
id
UUID primary key
name
operating / display name — required; the name guests and the market know
mgmt_company
string — property management firm, if different from operating name; nullable
parent_company
string — corporate parent or brand group (e.g. Marriott, a PE rollup); nullable. Key for spotting institutional ownership patterns.
market_id
FK → Markets
website
URL — operator-level brand site
location
geography(Point, 4326) — derived from primary Property centroid; distances displayed in ft
source
google_places | airbnb | manual
ingested_at
timestamp
Relationships
belongs to one
Markets
has many
Properties — category, keys, class, moat scores, comps, and booking URLs all live on properties
via Properties
Categories — an operator's categories are the union of its properties' categories
via Properties
Parcels — parcels are linked at the property level
named in
IntentProbes — AEO results reference operators by name
Segmentation lives at the Property level via class (regulatory type) and Category (perceptual position). Moat scores provide the competitive depth. No segment field on Operator — an operator like Marriott spans dozens of segments simultaneously; tying it to one would lose that nuance.
Pa
Parcels GIS
A raw legal land unit from county assessor records. Has an APN (assessor parcel number), acreage, zoning, and owner of record. A Property sits on one or more Parcels. A Parcel with no linked Property is whitespace — a potential development site. The 10–19 acre band is the primary target for Territori's MVP user.
Spatial · MVP
Key Fields
id
UUID primary key
apn
assessor parcel number — county-issued
county
string
geometry
geography(Polygon, 4326) — parcel boundary, WGS84. GiST index required.
acreage
decimal
zoning
string — county zoning code
owner_name
string
site_features
string[] — physical character of the land (coastal, forest, alpine, riverfront, etc.)
has_water_feature
boolean — true if any value in site_features is water-related. Set automatically on ingest.
has_structures
boolean
assessed_land
decimal — county assessor's land-only valuation in USD
assessed_improvements
decimal — county assessor's valuation of all structures and improvements in USD
last_sale_price
decimal — most recent recorded sale price in USD
last_sale_date
date
cost_per_acre
decimal — last_sale_price ÷ acreage; stored for direct trend analysis
assessor_url
URL — direct link to the county assessor's public record
property_id
FK → Properties — null if undeveloped
Relationships
belongs to many
Markets — via MarketParcels join table
optionally links to
Properties — null means undeveloped whitespace
has many
PermitEvents
has many
CupEvents
GIS: The most spatially critical table. geography(Polygon,4326) with a GiST index is required — catchment containment queries run against this table on every dashboard load. County assessor acreage should be validated on ingest against ST_Area(geometry) / 43560. Flag any record where the difference exceeds 5%.
Many-to-many with Markets: For MVP with a single market, a single market_id FK suffices. The join table MarketParcels should be introduced before adding a second market.
site_features is an open-ended string array. has_water_feature is the derived boolean that makes water-parcel map layer toggling fast — set on ingest, indexed, never queried via array scan.
Assessed value split:assessed_land + assessed_improvements replaces a single assessed_value. High land / low improvements = teardown or underdeveloped site.
zoning → ZoneLandUse:Parcel.zoning is a plain string (e.g., R20, RCC5, CF) matching county assessor records. It is the join key into the ZoneLandUse reference table — no FK, joined at query time via WHERE zone_code = parcel.zoning.
Pr
Properties GIS
The physical built asset an Operator runs. An Operator may have multiple Properties, each potentially holding a different Category. Properties are where keys (unit count), class (regulatory type), unit mix, seclusion metrics, moat scores, booking URLs, and ADR comps live. A Property sits on one or more Parcels.
Supply · MVP
Key Fields
id
UUID primary key
operator_id
FK → Operators
category_id
FK → Categories — perceptual position this property holds
name
property name — may differ from operator
land_use
bnb | campground | hotel | lodge | mpr | resort | rv_park | str | work_stay | other — Territori build type (maps to CCC LandUse slug; see BuildTypeLandUseMap)
keys
integer — total number of bookable units
unit_mix
JSON — array of {type, count, pct}
avg_unit_spacing
decimal — average distance between units; proxy for seclusion as a moat signal.
acreage
decimal — total parcel acreage
density
decimal — keys ÷ acreage (units per acre).
location
geography(Point, 4326) — auto-derived via ST_Centroid(parcel.geometry)
location_source
derived | manual
booking_url
URL
year_established
integer
quality_signal
JSON — class-appropriate quality indicator
differentiation_score
decimal 0–100 — Post-MVP
seasonality_volatility_score
decimal 0–100
Relationships
belongs to one
Operators
claims one
Categories
sits on
Parcels — one or more
has many
MoatScores
has many
Comps — property-specific realized ADR
GIS — auto-derived location: On ingest, location is set to ST_Centroid(primary_parcel.geometry). For properties spanning multiple parcels, use ST_Centroid(ST_Union(...)). The centroid mathematically always falls within a convex parcel — for irregular shapes, spot-check in Mapbox satellite view. The QA target is the parcel polygon accuracy, not the centroid itself. Any property needing a manual pin gets location_source = 'manual' so you can track overrides.
unit_mix: Captures both the descriptive and quantitative breakdown in a single queryable JSON field. Can be normalized to a join table post-MVP if query patterns require it.
Density vs. spacing:density (keys ÷ acreage) tells you how intensively land is used. Low density = more open land, which enables amenity moats: hiking trails, pickleball courts, swimming pools. avg_unit_spacing is the guest-experience signal — the average distance between bookable units. High acreage without high spacing is not a seclusion moat.
Keys and saturation:Anchor.annual_visitors ÷ SUM(Property.keys) for all properties in the Market.
ZLS
ZoneLandUseMatrix
The core lookup table. One row for every zone × land use combination in every county. Encodes whether a given land use term is Permitted, Conditional (requires CUP) or Prohibited in a given zoning district. This is what surfaces "Vacation Rental — Permitted outright" or "Master Planned Resort — Requires overlay" in the parcel modal. The entire zoning intelligence feature lives in this table.
Reference · Seed data
Key Fields
id
UUID primary key
zone_designation_id
FK → ZoneDesignation
land_use_id
FK → LandUses
status
PERMITTED | CONDITIONAL | PROHIBITED | CPA_REQUIRED — PERMITTED = allowed outright; CONDITIONAL = CUP required (administrative decision); PROHIBITED = not allowed; CPA_REQUIRED = Comprehensive Plan Amendment required before any permit pathway opens (MPR only)
code_citation
string — specific code section for this zone × use ruling (e.g. "§33.15.010(b)(4)")
[zone_designation_id, land_use_id] — one row per zone designation × land use pair
Relationships
belongs to one
ZoneDesignation
belongs to one
LandUseTerm
resolved via
Parcels → ZoneDesignation — a parcel's zone designation FK chains through to its ZoneLandUseMatrix rows
CPA_REQUIRED: A fourth status beyond the standard three. Used exclusively for Master Planned Resort, which requires a Comprehensive Plan Amendment — a legislative act voted on by the county commission that can take 1–3 years, not a routine CUP (administrative decision). Displaying "CONDITIONAL" for MPR would be actively misleading to operators. Note: Clallam County has no standalone "Resort" land use term in its code; only Master Planned Resort is defined.
FK chain replacing string match: The previous design matched Parcel.zoning (string) directly to zone_code (string). This design uses proper FKs: Parcel → ZoneDesignation → ZoneLandUseMatrix ← LandUseTerm. Cleaner, prevents orphan rows, and supports multi-county expansion without schema changes.
Scope: Only hospitality-relevant land use terms are seeded: Vacation Rental, Bed and Breakfast Inn, Motel/Hotel, Outdoor-oriented Recreation Facilities, Primitive Campground, RV Park, Master Planned Resort. Source: Clallam County Code Title 33, verified against Ord. 1026 (Dec 2024).
Group 4 — Signals
CS
CompSnapshots
Point-in-time rate and occupancy spot checks against a Comp. Where Comps tracks aggregate ADR over a period (sourced from AirDNA or bulk scrapes), CompSnapshots captures what a property is actually charging on a specific date — useful for monitoring pricing behaviour, seasonal rate swings and yield patterns over time.
Pricing · Post-v0.1
Key Fields
id
UUID primary key
comp_id
FK → Comps
checked_at
timestamp — when the check was performed
rate
decimal — listed or realized rate observed at check time
occupancy
decimal 0–1 — nullable if rate-only check
source
airbnb_scrape | manual
notes
text — optional; flag anomalies e.g. holiday weekend, special event
Relationships
belongs to one
Comps
Comps vs CompSnapshots: Comps holds aggregate ADR over a period (the benchmark). CompSnapshots holds individual spot checks (the evidence). Together they give you both the trend line and the raw observations behind it.
Co
Comps
Realized ADR (actual paid rate, not list price) and occupancy tracked as time-series records so pricing trajectories can be charted and used to underwrite Named Categories. Exist at two levels: market-level aggregates (segment benchmarking) and property-level specifics (individual underwriting), distinguished by the level field and which FK is populated.
Pricing · MVP
Key Fields
id
UUID primary key
level
market | property — explicit discriminator
market_id
FK → Markets — always populated
property_id
FK → Properties — populated for property-level; null for market aggregates
status
active | future | seasonal | closed
seasonal
boolean
season_open
integer 1–12 — month the property opens
season_close
integer 1–12 — month the property closes
class_filter
hotel | resort | str | glamping | all
category_filter
FK → Categories — nullable
period_start
date
period_end
date
adr
decimal — realized average daily rate for this period
occupancy
decimal — occupancy rate 0–1 for this period
source
airbnb_scrape | airdna | manual
Relationships
belongs to one
Markets
optionally belongs to
Properties — null = market-level aggregate
optionally filters by
Categories
has many
CompSnapshots — point-in-time rate and occupancy spot checks
informs
Narratives — ADR targets in Named Categories
On the optional FK pattern: Using a nullable property_id alongside an explicit level discriminator is a standard conditional FK approach. It keeps one table instead of two (MarketComps + PropertyComps), avoiding duplication. The tradeoff is slightly more complex queries. If this causes ORM friction in Prisma, splitting into two tables is the clean alternative — flag for backlog.
CE
CupEvents GIS
A Conditional Use Permit application from a county Department of Community Development portal. Signals intent to build 12–24 months before a building permit — the earlier and more strategically valuable signal. A separate feed from PermitEvents, scraped from DCD portals (not building departments).
Signal · MVP
Key Fields
id
UUID primary key
cup_id
county-issued CUP number
county
string
parcel_id
FK → Parcels — nullable if parcel match fails
cup_status
filed | under_review | approved | denied
date_filed
date
applicant
string
description
text
hearing_date
date — nullable
source_url
URL
address_raw
text — street address as filed; may be null for new or unaddressed parcels
Parcels → Markets — market association derived from linked parcel
optionally links to
Parcels — matched by address or APN where possible
feeds
Support — backs up moat scores for mover signals
GIS — location resolution: Same four-step resolution chain as PermitEvents. CUPs carry a 12–24 month lead-time advantage — a CUP with location_status = manual_review should be prioritized for human resolution above any other record type.
Why separate from PermitEvents: CUPs come from DCD portals (not building departments), cover a different regulatory stage, and require different scraping logic. Same competitive signal concept, different source, 12–24 month lead time advantage over building permits.
PE
PermitEvents GIS
A building permit filing or issuance from a county online-permit portal. Signals that someone is about to break ground. Covers all four ONP Gateway counties. Distinct from CupEvents — these come from county building departments and represent a later, more committed stage of construction.
Signal · MVP
Key Fields
id
UUID primary key
permit_id
county-issued permit number
county
string
parcel_id
FK → Parcels — nullable if parcel match fails
permit_status
filed | under_review | issued | expired
date_filed
date
applicant
string
description
text — project description from filing
source_url
URL — original portal record
address_raw
text — street address as filed; may be null for new or unaddressed parcels
legal_description
text — metes and bounds or township/range/section from filing
Parcels → Markets — market association is derived from the linked parcel's market memberships
optionally links to
Parcels — matched by address or APN where possible
optionally links to
Operators — if applicant matches a known operator
feeds
Support — backs up moat scores for mover signals
GIS — location resolution chain: On ingest, the system attempts to resolve location in order: (1) match APN to a parcel → use ST_Centroid(parcel.geometry); (2) if no APN match but an address exists → geocode via Mapbox, store confidence score; (3) if no address but surveyor GPS coordinates are present → store directly; (4) if none → flag location_status = manual_review. Every record remains mappable at minimum to county centroid; resolved records map precisely.
Market scoping: PermitEvents do not carry a direct market_id. Their market relevance is inherited from their linked Parcel's market memberships.
Group 5 — Intelligence
Al
Alerts
A system-generated notification triggered when a data feed goes stale, an anomaly is detected, or a significant signal event occurs — new CUP filing, permit issued for a known competitor. Surfaces to Admins immediately and optionally to subscribed Users.
An AEO (Answer Engine Optimization) query run against Claude, ChatGPT, and Perplexity to see which operators get named. "Best glamping near Olympic National Park." Each probe run reveals which Operators the AI engines are already recommending — a proxy for organic category ownership in the attention economy.
AEO · MVP
Key Fields
id
UUID primary key
market_id
FK → Markets
query_text
the search or prompt string
engine
claude | chatgpt | perplexity | google
probed_at
timestamp
results
JSON — array of {operator_name, rank, excerpt}
operators_named
FK[] → Operators — matched from results
Relationships
belongs to one
Markets
references many
Operators — those named in results
informs
Narratives — Intent Intelligence section
MD
MoatDimensions
The 15 canonical dimensions of competitive defensibility — static reference data that forms the backbone of the Territori taxonomy. Geography, Weather, Soundscape, History, Local Culture, Attractions, Celebrities, Architecture, Scarcity, Scale, Producers, Story, Collections, Interests, Expertise. Scored per Property via MoatScores; referenced by Categories as moat combinations.
IP note: The 15 dimensions and their definitions are Janice's canonical IP. Claude drafts; Janice finalizes. Seeded once and treated as read-only thereafter (T-26).
MS
MoatScores
A score for a specific Property on a specific MoatDimension, within a Market context. Scores live at the Property level because two properties under the same Operator can hold meaningfully different moat positions. Each score has a numeric value, a saturation level, and links to the Support records that justify it.
Core · MVP
Key Fields
id
UUID primary key
property_id
FK → Properties
dimension_id
FK → MoatDimensions
market_id
FK → Markets
score
integer 0–100
saturation_level
wide_open | open | contested | saturated
scored_at
timestamp
scored_by
ai | Admin | hybrid
Relationships
belongs to one
Properties
belongs to one
MoatDimensions
scoped to one
Markets
has many
Support records
Na
Narratives
The generated editorial output for a Market at a point in time. Contains Named Categories (strategic positioning candidates), Dashboard Captions (section-level editorial interpretation), and the Editorial Diagnosis (prose conclusion). Versioned — a new Narrative is created each generation run so the evolution of the story is preserved. The Narrative is the deliverable heart of Territori.
Output · MVP
Key Fields
id
UUID primary key
market_id
FK → Markets
generated_at
timestamp
generated_by
ai | Admin | hybrid
named_categories
JSON — array of {name, adr_target, time_to_launch, moat_combo, feasibility_score}
dashboard_captions
JSON — keyed by section slug; one editorial caption per dashboard section
editorial_diagnosis
text — prose conclusion in Territori voice
pci_score
integer 0–100 — PCI at time of generation; the hero convergence gauge. Snapshot of ConvergenceSnapshots.pci_index frozen into this Narrative.
recommendation
text — Post-MVP
is_current
boolean — true for the active version
Relationships
belongs to one
Markets
used by
Studies — frozen at delivery
draws from
MoatScores, IntentProbes, Comps
Named Categories: The strategic positioning candidates for this Market — Categories an operator could plausibly claim, each with an ADR target, estimated time-to-launch, moat combination, and feasibility score against the operator's parcel and constraints.
Open: Dashboard Captions section keys to be finalized as dashboard routes are built (T-21).
Su
Support
A source record that backs up a MoatScore. A review excerpt, a press mention, an Airbnb listing detail, a permit filing. Many pieces of Support justify one MoatScore. The transparency layer that lets a user ask "why does this property score 82 on Geography?" and get a citable answer.
Transparency · MVP
Key Fields
id
UUID primary key
moat_score_id
FK → MoatScores
type
review | press | listing | permit | web | manual
excerpt
text — the relevant passage or observation
source_url
URL — nullable
source_date
date — nullable
Relationships
belongs to one
MoatScores
Known MVP limitation: One Support record currently belongs to one MoatScore. A press article that backs multiple scores (e.g., both Geography and Story) requires duplicate Support records in this model. The correct solution is a SupportMoatScores many-to-many join table — deferred post-MVP.
Cv
ConvergenceSnapshots
A point-in-time measurement of the Positioning Convergence Index (PCI) and its five sub-axes for a Market. One row per market per computation run (planned weekly cadence). The time-series backing the PCI gauge, trend chart, and week-over-week delta signals on the dashboard.
Intelligence · Analytics
Key Fields
id
UUID primary key
market_id
FK → Markets
pci
decimal 0–100 — PCI composite (raw); arithmetic mean of the five sub-axes
pci_index
integer — pci × 100; the display value, stored for direct query
keyword_overlap
integer 0–100
positioning_frequency
integer 0–100
amenity_similarity
integer 0–100
price_dispersion
integer 0–100
review_axis_overlap
integer 0–100
computed_at
timestamp
Relationships
belongs to one
Markets
Formula:pci = (keyword_overlap + positioning_frequency + amenity_similarity + price_dispersion + review_axis_overlap) ÷ 5. pci_index = pci × 100 for display (mirrors DPI_INDEX pattern). Sub-axes are unweighted at MVP.
Unique constraint:[market_id, computed_at] — prevents duplicate rows for the same computation run.
Cadence: Designed for weekly runs. The schema supports any cadence including daily — just change the run frequency on ingest; no migration needed. See territori-formulas for the full PCI methodology and threshold definitions.
Dp
DemandPressureSnapshots
A period-based measurement of the Demand Pressure Index (DPI) for a Market — visitor nights relative to available key nights. One row per market per period. The time-series backing the DPI bar chart, annual average calculation, and pricing power window identification.
Intelligence · Analytics
Key Fields
id
UUID primary key
market_id
FK → Markets
period_start
date — first day of the measurement period (e.g. 2026-01-01 for January). Used as the unique period key.
period_end
date — last day of the period. Stored for query convenience.
total_keys
integer — snapshot of total key count in the catchment at time of measurement.
visitor_nights
integer — total guest-nights booked across all operators in the catchment during the period
decimal — raw ratio: visitor_nights ÷ available_keys_per_period (e.g. 0.70)
dpi_index
integer — dpi × 100; the display value (e.g. 70). Stored for direct query.
computed_at
timestamp
Relationships
belongs to one
Markets
Formula:dpi = visitor_nights ÷ available_keys_per_period. All three raw inputs are stored so the formula can be audited or recalculated if the definition changes.
Key definition: For hotels, a "key" = one room. For STRs and BnBs, a "key" = one bedroom (not one listing).
Annual average DPI: Derived at query time — arithmetic mean of the 12 monthly dpi values for a calendar year. Not stored as a field.
Unique constraint:[market_id, period_start] — one row per market per period start date.
Cadence: Initially monthly. Daily cadence is fully supported by this schema without any migration. See territori-formulas for threshold definitions.
Junction Tables (DB-resolved M:N relationships)
MA
MarketAnchors
Resolves the many-to-many relationship between Markets and Anchors. A market may orbit multiple anchors (ONP + Lake Crescent); an anchor can seed multiple markets. DB table: MarketAnchor.
Core · MVP
Key Fields
market_id
FK → Markets — composite PK
anchor_id
FK → Anchors — composite PK
CD
CategoryMoatDimensions
Resolves the many-to-many relationship between Categories and MoatDimensions. Each Category is defined by a combination of moat dimensions (its moat_combination). DB table: CategoryMoatDimension.
Core IP · MVP
Key Fields
category_id
FK → Categories — composite PK
dimension_id
FK → MoatDimensions — composite PK
MP
MarketParcels
Resolves the many-to-many relationship between Markets and Parcels. A parcel can be evaluated across multiple markets (e.g. a parcel near a market boundary); a market contains many candidate parcels. DB table: MarketParcel.
Spatial · MVP
Key Fields
market_id
FK → Markets — composite PK
parcel_id
FK → Parcels — composite PK
IO
IntentProbeOperators
Resolves the many-to-many relationship between IntentProbes and Operators. An intent probe surfaces multiple operators in its results; an operator can appear across many probes. DB table: IntentProbeOperator.
AEO · MVP
Key Fields
intent_probe_id
FK → IntentProbes — composite PK
operator_id
FK → Operators — composite PK
AU
AlertUsers
Resolves the many-to-many relationship between Alerts and Users. An alert can be sent to multiple users; a user may receive many alerts. DB table: AlertUser.