search-aggregation-service — DATA_MODEL
Companion: SERVICE_OVERVIEW · DOMAIN_MODEL · API_CONTRACTS · EVENT_SCHEMAS · SECURITY_MODEL · ../../docs/06-data-models.md · ../../docs/standards/NAMING.md
This document is the canonical Postgres + OpenSearch + cache schema for search-aggregation-service. It defines tables, indexes, the OpenSearch index template, the field-level cross-tenant allow-list, integrity rules, soft-delete posture, and the read models served to bff-consumer-service. When the bundle and the platform 06 Data Models disagree, the platform document is authoritative for cross-cutting decisions; this document is authoritative for the search domain.
1. Storage Strategy
| Surface | Store | Why |
|---|---|---|
| Canonical projection | Cloud SQL Postgres 15 + PostGIS — schema search | ACID, transactional outbox; enables full reindex from canonical |
| Read index (full-text + geo + facets) | OpenSearch 2.x — alias melmastoon-search-current | Sub-100 ms cross-tenant queries with text + geo + facets |
| Hot result cache | Memorystore Redis 7 | Sub-ms cache for repeated queries and detail reads |
| FX snapshot cache | Memorystore Redis 7 — keys srh:fx:<base>:<target> | Per-currency conversion at read time |
| Outbox | Same Postgres schema (outbox table) | Transactional with the projection write |
| Inbox / dedup | Same Postgres schema (inbox table) | Idempotent consumption of upstream events |
| Event archive (replay) | BigQuery events_raw.melmastoon_* (read-only) | Source for full reindex from epoch |
| Vector embeddings (Phase 2+) | pgvector on the same Postgres + OpenSearch k-NN | Semantic re-ranking |
| Photos | not stored — MediaRef only references med_… in file-storage-service | Photos aren't this service's concern |
The schema is named search. All tables live inside it.
Tenancy posture (THIS SERVICE ONLY). This is the single service in the platform that runs cross-tenant reads. Postgres connections set
SET LOCAL app.tenant_id = '__cross_tenant__'. RLS policies onsearch.*tables explicitly allow this sentinel; ordinary tenant-scoped sentinels still fail. Defense moves to the field-level allow-list: tables and OpenSearch documents simply do not have columns/fields for forbidden data.
2. ID Strategy
| Prefix | Entity | Owning aggregate | Notes |
|---|---|---|---|
srh_ | HotelIndexEntry | this service | OpenSearch doc ID is the upstream propertyId (idempotent); srh_ is the Postgres surrogate key |
brt_ | BoostRule | this service | |
spr_ | SponsoredRanking | this service | Phase 3+ |
clk_ | ClickEvent | this service | |
q_ | SearchQuery (logged) | this service | sampled; anonymized at 30 d |
ibd_ | IndexBuild | this service | |
ppt_ | PropertyId (mirrored from property-service) | upstream | Used as the natural key for upserts |
tnt_ | TenantId (mirrored from tenant-service) | upstream | Preserved for cascade purge / region pinning audit |
rate_ | RatePlanId (mirrored from pricing-service) | upstream | Stored as the winning plan reference in RateSnapshot |
ID format: <prefix>_<26-char Crockford ULID>. CHECK constraint per column: id ~ '^<prefix>_[0-9A-HJKMNP-TV-Z]{26}$'.
3. Cross-Tenant Searchable Allow-List
The allow-list is the single source of truth for what may appear in the index. It is enforced at three layers:
- Type level:
HotelIndexEntry's TypeScript type literally has no field for forbidden data. CI runs a unit test that walks the type and rejects any property not in this list. - Projection layer:
ProjectionAllowListPolicyfilters every inbound event payload to the allow-list set before constructing an aggregate. - DB / index level: Postgres
search.hotel_index_entriestable and the OpenSearch index template only declare columns/fields from the allow-list. Adding a forbidden field is a CI failure (schema diff).
| Field path | Source | cross_tenant_searchable | Why |
|---|---|---|---|
propertyId | property-service | true | Identifier — needed for deep-link |
tenantId | tenant-service | true (audit-only; never returned to consumer) | Cascade purge + region pinning |
name (I18nString, names only) | property-service | true | Public listing |
description (I18nString) | property-service | true | Public listing |
city, countryIso2, region | property-service | true | Public listing + filter |
geo (lat/lng), geohash5 | property-service | true | Public map |
starRating | property-service | true | Public filter |
amenities[] (canonical codes only) | property-service | true | Public filter |
languagesSpoken[] | property-service | true | Public filter |
heroPhoto.{mediaId, signedUrlCacheKey, alt} | property-service | true | Public listing |
ratingAvg, ratingCount | reviews-service (future) | true | Public listing |
priceFrom* (derived from RateSnapshot) | pricing-service | true | Public listing |
roomsAvailable, roomsTotal (per date) | inventory-service | true | Per-night hint |
freeCancellation, payAtProperty | pricing-service | true | Public filter |
popularityScore7d, popularityScore28d | this service | true (internal use only — never returned) | Ranking |
boostMultiplier | this service | true (internal use only — never returned) | Ranking |
| Anything else | upstream | false (forbidden) | PII, payment, lock, financial, unpublished — never indexed |
Forbidden examples (explicitly NOT in the schema): ownerEmail, ownerPhone, bankAccount, taxIdentifier, lockDeviceSerial, internalNotes, unpublishedDescriptions, any guest* fields.
4. Schema (DDL)
4.1 Extensions + sentinel role
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS vector; -- pgvector, Phase 2+
CREATE SCHEMA IF NOT EXISTS search;
-- Sentinel for cross-tenant reads. The application sets this in every connection.
-- Granted to the search-aggregation-service runtime SA only via Cloud SQL IAM auth.
DO $$ BEGIN
PERFORM set_config('app.tenant_id', '__cross_tenant__', false);
EXCEPTION WHEN OTHERS THEN NULL;
END $$;
4.2 hotel_index_entries
CREATE TABLE search.hotel_index_entries (
id text PRIMARY KEY CHECK (id ~ '^srh_[0-9A-HJKMNP-TV-Z]{26}$'),
property_id text UNIQUE NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
tenant_id uuid NOT NULL, -- audit / purge only
name_default_locale text NOT NULL, -- e.g. 'ps'
name_i18n jsonb NOT NULL, -- I18nString.values
description_i18n jsonb NOT NULL,
city text NOT NULL, -- canonical city slug
country_iso2 text NOT NULL CHECK (length(country_iso2) = 2),
region text NOT NULL CHECK (region IN ('AF','TJ','IR','EU','US','GLOBAL')),
geo geography(POINT, 4326) NOT NULL,
geohash5 text NOT NULL CHECK (length(geohash5) = 5),
star_rating int CHECK (star_rating BETWEEN 1 AND 5),
amenities text[] NOT NULL DEFAULT ARRAY[]::text[], -- canonical codes
languages_spoken text[] NOT NULL DEFAULT ARRAY[]::text[],
hero_media_id text, -- 'med_…' from file-storage-service
hero_signed_url_key text,
hero_alt_i18n jsonb,
rating_avg numeric(3,2),
rating_count int NOT NULL DEFAULT 0,
popularity_score_7d double precision NOT NULL DEFAULT 0,
popularity_score_28d double precision NOT NULL DEFAULT 0,
freshness_boost double precision NOT NULL DEFAULT 0,
quality_score double precision NOT NULL DEFAULT 0,
boost_multiplier double precision NOT NULL DEFAULT 1.0
CHECK (boost_multiplier BETWEEN 0.1 AND 5.0),
status text NOT NULL CHECK (status IN ('active','suppressed')),
vc_property_service bigint NOT NULL DEFAULT 0,
vc_pricing_service bigint NOT NULL DEFAULT 0,
vc_inventory_service bigint NOT NULL DEFAULT 0,
last_upserted_at timestamptz NOT NULL DEFAULT now(),
schema_version int NOT NULL DEFAULT 1,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_hie_status_region ON search.hotel_index_entries (status, region) WHERE status = 'active';
CREATE INDEX ix_hie_geo_gist ON search.hotel_index_entries USING GIST (geo) WHERE status = 'active';
CREATE INDEX ix_hie_geohash5 ON search.hotel_index_entries (geohash5) WHERE status = 'active';
CREATE INDEX ix_hie_country_city ON search.hotel_index_entries (country_iso2, city) WHERE status = 'active';
CREATE INDEX ix_hie_amenities_gin ON search.hotel_index_entries USING GIN (amenities) WHERE status = 'active';
CREATE INDEX ix_hie_languages_gin ON search.hotel_index_entries USING GIN (languages_spoken) WHERE status = 'active';
CREATE INDEX ix_hie_tenant_purge ON search.hotel_index_entries (tenant_id);
CREATE INDEX ix_hie_last_upserted ON search.hotel_index_entries (last_upserted_at);
-- RLS: cross-tenant by design. The sentinel '__cross_tenant__' grants unconditional access.
ALTER TABLE search.hotel_index_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE search.hotel_index_entries FORCE ROW LEVEL SECURITY;
CREATE POLICY hotel_index_entries_cross_tenant ON search.hotel_index_entries
USING (current_setting('app.tenant_id') = '__cross_tenant__')
WITH CHECK (current_setting('app.tenant_id') = '__cross_tenant__');
4.3 rate_snapshots
CREATE TABLE search.rate_snapshots (
property_id text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
date date NOT NULL,
base_currency text NOT NULL CHECK (length(base_currency) = 3),
cheapest_base_micro bigint NOT NULL CHECK (cheapest_base_micro > 0),
rate_plan_id text NOT NULL CHECK (rate_plan_id ~ '^rate_[0-9A-HJKMNP-TV-Z]{26}$'),
refundable boolean NOT NULL DEFAULT false,
closed_to_arrival boolean NOT NULL DEFAULT false,
vc_pricing_service bigint NOT NULL DEFAULT 0,
occurred_at timestamptz NOT NULL,
PRIMARY KEY (property_id, date)
);
CREATE INDEX ix_rs_date_price ON search.rate_snapshots (date, cheapest_base_micro);
CREATE INDEX ix_rs_property ON search.rate_snapshots (property_id);
ALTER TABLE search.rate_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE search.rate_snapshots FORCE ROW LEVEL SECURITY;
CREATE POLICY rate_snapshots_cross_tenant ON search.rate_snapshots
USING (current_setting('app.tenant_id') = '__cross_tenant__')
WITH CHECK (current_setting('app.tenant_id') = '__cross_tenant__');
4.4 availability_hints
CREATE TABLE search.availability_hints (
property_id text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
date date NOT NULL,
rooms_available int NOT NULL CHECK (rooms_available >= 0),
rooms_total int NOT NULL CHECK (rooms_total >= 0),
vc_inventory_service bigint NOT NULL DEFAULT 0,
occurred_at timestamptz NOT NULL,
CHECK (rooms_available <= rooms_total),
PRIMARY KEY (property_id, date)
);
CREATE INDEX ix_ah_date_avail ON search.availability_hints (date, rooms_available);
ALTER TABLE search.availability_hints ENABLE ROW LEVEL SECURITY;
ALTER TABLE search.availability_hints FORCE ROW LEVEL SECURITY;
CREATE POLICY availability_hints_cross_tenant ON search.availability_hints
USING (current_setting('app.tenant_id') = '__cross_tenant__')
WITH CHECK (current_setting('app.tenant_id') = '__cross_tenant__');
4.5 boost_rules (Phase 3+)
CREATE TABLE search.boost_rules (
id text PRIMARY KEY CHECK (id ~ '^brt_[0-9A-HJKMNP-TV-Z]{26}$'),
tenant_id uuid NOT NULL,
property_id text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
scope jsonb NOT NULL, -- region, amenities, locales, dateRange
multiplier double precision NOT NULL CHECK (multiplier BETWEEN 0.1 AND 5.0),
status text NOT NULL CHECK (status IN ('draft','active','expired','cancelled')),
created_by text NOT NULL,
activated_by text,
cancelled_by text,
created_at timestamptz NOT NULL DEFAULT now(),
activated_at timestamptz,
expires_at timestamptz
);
CREATE INDEX ix_br_property_status ON search.boost_rules (property_id, status);
CREATE INDEX ix_br_tenant ON search.boost_rules (tenant_id);
CREATE INDEX ix_br_expires_at ON search.boost_rules (expires_at) WHERE status = 'active';
boost_rules is the only table here that is conceptually tenant-owned (operators of a tenant manage rules for their own properties). It still uses the cross-tenant sentinel because the search reader must consult any tenant's rules; the application enforces ownership at command time via BoostRuleScopeViolationError.
4.6 click_events
CREATE TABLE search.click_events (
id text PRIMARY KEY CHECK (id ~ '^clk_[0-9A-HJKMNP-TV-Z]{26}$'),
search_query_id text NOT NULL CHECK (search_query_id ~ '^q_[0-9A-HJKMNP-TV-Z]{26}$'),
property_id text NOT NULL CHECK (property_id ~ '^ppt_[0-9A-HJKMNP-TV-Z]{26}$'),
tenant_id uuid NOT NULL,
rank int NOT NULL CHECK (rank >= 1),
user_bucket text NOT NULL,
occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_ce_property_time ON search.click_events (property_id, occurred_at DESC);
CREATE INDEX ix_ce_query_time ON search.click_events (search_query_id, occurred_at DESC);
CREATE INDEX ix_ce_user_bucket ON search.click_events (user_bucket, occurred_at DESC);
4.7 search_queries
CREATE TABLE search.search_queries (
id text PRIMARY KEY CHECK (id ~ '^q_[0-9A-HJKMNP-TV-Z]{26}$'),
canonical_query_hash char(64) NOT NULL,
locale text NOT NULL,
currency text NOT NULL CHECK (length(currency) = 3),
region text NOT NULL,
text text, -- nullified at 30d (anonymizer)
filter jsonb NOT NULL,
sort_key jsonb NOT NULL,
result_count int NOT NULL,
took_ms int NOT NULL,
user_bucket text, -- nullified at 30d
degradation_level text NOT NULL DEFAULT 'none',
occurred_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_sq_hash_time ON search.search_queries (canonical_query_hash, occurred_at DESC);
CREATE INDEX ix_sq_time ON search.search_queries (occurred_at DESC);
4.8 index_builds
CREATE TABLE search.index_builds (
id text PRIMARY KEY CHECK (id ~ '^ibd_[0-9A-HJKMNP-TV-Z]{26}$'),
regions text[] NOT NULL,
template_version text NOT NULL,
since_ts timestamptz NOT NULL,
status text NOT NULL CHECK (status IN ('draft','planning','replaying','catching_up','swapping','completed','failed')),
new_index_names jsonb, -- per-region
previous_index_names jsonb,
docs_indexed bigint,
duration_seconds int,
error_code text,
error_detail text,
created_at timestamptz NOT NULL DEFAULT now(),
swapped_at timestamptz,
failed_at timestamptz,
CONSTRAINT only_one_active_per_region EXCLUDE USING gist (regions WITH &&) WHERE (status IN ('planning','replaying','catching_up','swapping'))
);
The EXCLUDE constraint enforces "one active build per region" at the DB level (uses intarray/gist extension; for Cloud SQL we use a partial unique index pattern in practice — see migration 0009).
4.9 outbox and inbox
CREATE TABLE search.outbox (
id bigserial PRIMARY KEY,
event_id text NOT NULL UNIQUE CHECK (event_id ~ '^evt_[0-9A-HJKMNP-TV-Z]{26}$'),
topic text NOT NULL,
ordering_key text NOT NULL, -- propertyId for projection events
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
published_at timestamptz,
publish_attempts int NOT NULL DEFAULT 0,
last_error text
);
CREATE INDEX ix_outbox_unpublished ON search.outbox (created_at) WHERE published_at IS NULL;
CREATE TABLE search.inbox (
event_id text PRIMARY KEY CHECK (event_id ~ '^evt_[0-9A-HJKMNP-TV-Z]{26}$'),
topic text NOT NULL,
received_at timestamptz NOT NULL DEFAULT now(),
processed_at timestamptz,
result text CHECK (result IN ('applied','dropped_stale','rejected','dlq'))
);
CREATE INDEX ix_inbox_unprocessed ON search.inbox (received_at) WHERE processed_at IS NULL;
4.10 province_centers (seeded)
CREATE TABLE search.province_centers (
city_slug text PRIMARY KEY,
city_name_i18n jsonb NOT NULL,
country_iso2 text NOT NULL,
region text NOT NULL,
geo geography(POINT, 4326) NOT NULL,
timezone text NOT NULL
);
Seeded with all known cities in AF/TJ/IR for Phase 1 (≈ 200 rows); expandable per region launch.
5. OpenSearch Index Template
Path: services/search-aggregation-service/contracts/opensearch/hotel-index.template.json.
{
"index_patterns": ["melmastoon-search-*"],
"template": {
"settings": {
"index.number_of_shards": 3,
"index.number_of_replicas": 1,
"index.refresh_interval": "1s",
"index.lifecycle.name": "melmastoon-search-ilm",
"analysis": {
"analyzer": {
"english_standard": { "type": "standard", "stopwords": "_english_" },
"pashto_standard": { "type": "custom", "tokenizer": "icu_tokenizer", "filter": ["icu_normalizer", "lowercase", "asciifolding"] },
"dari_standard": { "type": "custom", "tokenizer": "icu_tokenizer", "filter": ["icu_normalizer", "lowercase", "arabic_normalization", "persian_normalization"] },
"persian_standard": { "type": "persian" },
"tajik_standard": { "type": "custom", "tokenizer": "icu_tokenizer", "filter": ["icu_normalizer", "lowercase"] },
"arabic_standard": { "type": "arabic" },
"urdu_standard": { "type": "custom", "tokenizer": "icu_tokenizer", "filter": ["icu_normalizer", "lowercase"] },
"russian_standard": { "type": "russian" },
"edge_ngram_suggest": {
"type": "custom",
"tokenizer": "standard",
"filter": ["lowercase", "asciifolding", "edge_ngram_filter"]
}
},
"filter": {
"edge_ngram_filter": { "type": "edge_ngram", "min_gram": 2, "max_gram": 20 }
}
}
},
"mappings": {
"dynamic": "strict",
"_source": { "enabled": true },
"properties": {
"property_id": { "type": "keyword" },
"tenant_id": { "type": "keyword" },
"city": { "type": "keyword" },
"country_iso2": { "type": "keyword" },
"region": { "type": "keyword" },
"geo": { "type": "geo_point" },
"geohash5": { "type": "keyword" },
"star_rating": { "type": "byte" },
"amenities": { "type": "keyword" },
"languages_spoken": { "type": "keyword" },
"hero_media_id": { "type": "keyword", "index": false },
"hero_signed_url_key": { "type": "keyword", "index": false },
"rating_avg": { "type": "half_float" },
"rating_count": { "type": "integer" },
"popularity_score_7d": { "type": "float" },
"popularity_score_28d": { "type": "float" },
"freshness_boost": { "type": "float" },
"quality_score": { "type": "float" },
"boost_multiplier": { "type": "float" },
"free_cancellation": { "type": "boolean" },
"pay_at_property": { "type": "boolean" },
"status": { "type": "keyword" },
"last_upserted_at": { "type": "date" },
"name": {
"properties": {
"default": { "type": "keyword" },
"en": { "type": "text", "analyzer": "english_standard", "fields": { "suggest": { "type": "text", "analyzer": "edge_ngram_suggest", "search_analyzer": "standard" } } },
"ps": { "type": "text", "analyzer": "pashto_standard", "fields": { "suggest": { "type": "text", "analyzer": "edge_ngram_suggest", "search_analyzer": "standard" } } },
"fa": { "type": "text", "analyzer": "persian_standard", "fields": { "suggest": { "type": "text", "analyzer": "edge_ngram_suggest", "search_analyzer": "standard" } } },
"tg": { "type": "text", "analyzer": "tajik_standard" },
"ar": { "type": "text", "analyzer": "arabic_standard" },
"ur": { "type": "text", "analyzer": "urdu_standard" },
"ru": { "type": "text", "analyzer": "russian_standard" }
}
},
"description": {
"properties": {
"default": { "type": "keyword" },
"en": { "type": "text", "analyzer": "english_standard" },
"ps": { "type": "text", "analyzer": "pashto_standard" },
"fa": { "type": "text", "analyzer": "persian_standard" },
"tg": { "type": "text", "analyzer": "tajik_standard" },
"ar": { "type": "text", "analyzer": "arabic_standard" },
"ur": { "type": "text", "analyzer": "urdu_standard" },
"ru": { "type": "text", "analyzer": "russian_standard" }
}
},
"available_dates": {
"type": "nested",
"properties": {
"date": { "type": "date" },
"rooms_available": { "type": "short" },
"cheapest_base_micro": { "type": "long" },
"base_currency": { "type": "keyword" },
"free_cancellation": { "type": "boolean" }
}
},
"embedding": { "type": "knn_vector", "dimension": 768, "method": { "name": "hnsw", "engine": "lucene", "space_type": "cosinesimil" } }
}
},
"aliases": { "melmastoon-search-current": {} }
}
}
dynamic: "strict" is critical: any unknown field rejects the document. This is the third defense layer for the allow-list.
ILM policy (ilm-policy.json): hot phase ≤ 30 d, warm phase ≤ 90 d, delete > 180 d (only relevant for archived index versions).
6. Cache Key Patterns
| Key | TTL | Value | Invalidation |
|---|---|---|---|
srh:q:<sha256(canonical-query)> | 60 s | SearchResultDto (compressed) | TTL only; targeted invalidation on projection.updated.v1 for any property in result set is fire-and-forget (best-effort) |
srh:detail:<propertyId>:<currency>:<dateRangeKey> | 300 s | HotelDetailDto | On projection.updated.v1{propertyId} direct delete |
srh:sug:<sha256(prefix+locale+kind)> | 60 s | SuggestDto | TTL only |
srh:facets:<sha256(canonical-query-no-paging)> | 60 s | FacetsDto | TTL only |
srh:fx:<base>:<target> | 1 h | { rate, snapshotVersion, fetchedAt } | On fx_snapshot.updated.v1 direct write |
srh:popularity:<propertyId> | 15 min | { score7d, score28d } | Replaced on recompute-popularity job tick |
Compression: snappy for values > 4 KB. Eviction policy: allkeys-lru, max-memory 4 GiB per Memorystore node.
7. Audit & PII Posture
- The schema declares no PII columns, by design. Schema diff is a CI gate.
search_queries.textand.user_bucketare nullified at 30 days bynightly-anonymize-search-queries.- All audit-relevant projection events go to the
audit-servicemirror through themelmastoon.search.boost_rule.v1andmelmastoon.search.index.v1topics (retention classaudit). - A nightly
ProjectionExposureAuditorjob:- Walks every column on
hotel_index_entriesand asserts each is in the allow-list. - Walks every OpenSearch field and asserts each is in the index template.
- Counts documents where
status='active'but the upstreamproperty.published.v1is missing in the inbox of the last 30 d (orphan detection). - Counts documents whose
tenantIdcorresponds to a deleted tenant (cascade-purge integrity).
- Walks every column on
- Auditor failures emit
search.index.health_alert.v1and a security on-call page.
8. Storage sizing (initial)
| Surface | Estimate (Phase 1, AF/TJ/IR) |
|---|---|
hotel_index_entries | ≈ 50 000 rows × 3 KB = ~150 MB |
rate_snapshots | ≈ 50 000 properties × 365 days = ~18 M rows × 0.2 KB = ~3.6 GB (partitioned by month) |
availability_hints | similar to rate_snapshots — ~3.6 GB |
click_events | 1 M/day × 30 d × 0.3 KB = ~9 GB (partitioned by day) |
search_queries (sampled) | 100 K/day × 30 d × 1 KB = ~3 GB (partitioned by day) |
| OpenSearch primary store | ≈ 50 000 docs × 8 KB = ~400 MB primary (×2 replicas + overhead ≈ 1.5 GB) |
| Memorystore | 4 GiB tier (cache) |
rate_snapshots, availability_hints, click_events, search_queries are range-partitioned by month/day to keep VACUUM and query plans fast.
9. Migrations
0001_init_schema.sql— schema, extensions, sentinel role.0002_hotel_index_entries.sql— table + indexes + RLS.0003_rate_snapshots.sql0004_availability_hints.sql0005_outbox_inbox.sql0006_search_queries.sql0007_click_events.sql0008_province_centers.sql+ seed0009_index_builds.sql0010_boost_rules.sql(Phase 3+)0011_pgvector_embedding.sql(Phase 2+)0012_partition_rate_snapshots_by_month.sql0013_partition_click_events_by_day.sql0014_partition_search_queries_by_day.sql
Migrations follow the platform expand → backfill → contract discipline; see MIGRATION_PLAN.md.