Schema Reference

Geographic Tables

`brazil_regions`

Brazil’s 5 macro-regions (Norte, Nordeste, Centro-Oeste, Sudeste, Sul).

`brazil_states`

All 27 Brazilian states and the Federal District.

Column	Type	Description
`id`	INTEGER	Primary key
`region_id`	INTEGER	FK to `brazil_regions`
`name`	TEXT	Full state name
`code`	TEXT	2-letter code (e.g., “SP”)
`slug`	TEXT	URL slug (e.g., “sao-paulo”)

`brazil_cities`

All 5,000+ Brazilian municipalities.

Column	Type	Description
`id`	INTEGER	Primary key
`state_id`	INTEGER	FK to `brazil_states`
`name`	TEXT	City name
`slug`	TEXT	URL slug
`ibge_code`	TEXT	IBGE municipality code

Content Tables

`scopes`

The three real estate scopes.

Column	Type	Description
`id`	INTEGER	Primary key
`name`	TEXT	Scope name (casas, jardim, condominios)
`slug`	TEXT	URL slug
`description`	TEXT	SEO description

`topics`

Content topics (energia-solar, iptu, etc.).

`page_types`

Classification of generated pages (scope_index, state_index, city_page, topic_page, etc.).

Page Tables

`pages`

Every generated page has an entry here.

Column	Type	Description
`id`	INTEGER	Primary key
`url_path`	TEXT	Full URL path
`scope_id`	INTEGER	FK to `scopes` (nullable)
`topic_id`	INTEGER	FK to `topics` (nullable)
`state_code`	TEXT	State code (nullable)
`city_id`	INTEGER	FK to `brazil_cities` (nullable)
`page_type_id`	INTEGER	FK to `page_types`
`title`	TEXT	Page title
`meta_description`	TEXT	SEO meta description
`generated_at`	DATETIME	Last generation timestamp

`page_content_cache`

Pre-rendered HTML content for pages.

Column	Type	Description
`page_id`	INTEGER	FK to `pages`
`content`	TEXT	Pre-rendered HTML
`updated_at`	DATETIME	Last update

`page_generation_log`

Audit log of page generation events.

Real Estate Tables

`property_types`

Types of properties (casa, apartamento, terreno, etc.).

`listings`

Property listings created by users.

Column	Type	Description
`id`	INTEGER	Primary key
`user_id`	INTEGER	Owner user ID
`title`	TEXT	Listing title
`description`	TEXT	Full description
`price`	INTEGER	Price in BRL cents
`city_id`	INTEGER	FK to `brazil_cities`
`state_code`	TEXT	State code
`property_type_id`	INTEGER	FK to `property_types`
`status`	TEXT	active, inactive, sold
`images`	TEXT	JSON array of image URLs

`real_estate_agents`

Real estate agent profiles.

`imobiliarias`

Real estate agencies.

Blog Tables

`blog_themes`

Blog categories/themes.

`blog_posts`

Blog articles.

Column	Type	Description
`id`	INTEGER	Primary key
`theme_id`	INTEGER	FK to `blog_themes`
`slug`	TEXT	URL slug
`title`	TEXT	Article title
`content`	TEXT	Article content (HTML or Markdown)
`published_at`	DATETIME	Publication date

Analytics Tables

`notfound_urls`

Tracks 404 errors from production.

Column	Type	Description
`id`	INTEGER	Primary key
`url_path`	TEXT	The 404 URL
`hit_count`	INTEGER	Number of times requested
`resolved`	BOOLEAN	Whether a page was generated

`page_views`

Page view analytics.

`search_queries`

Search terms entered by users.

Scheduler Tables

`daily_rotation_tracker`

Tracks which state was last refreshed by the daily build.

Column	Type	Description
`id`	INTEGER	Primary key
`state_code`	TEXT	Last refreshed state
`refreshed_at`	DATETIME	Timestamp