Overview The Inmobiliaria Web application uses PostgreSQL as its primary database. The schema is designed to support a comprehensive real estate management system with authentication, property listings, and user management.
The complete schema is available in inmobiliaria_complete_schema.sql in the project root.
Authentication Tables The authentication system uses Better Auth compatible tables.
users Core user account information.
Column Type Constraints Description idSERIAL PRIMARY KEY Unique user identifier emailVARCHAR(255) NOT NULL, UNIQUE User email address nameVARCHAR(255) User’s full name imageTEXT Profile image URL email_verifiedTIMESTAMP Email verification timestamp roleVARCHAR(50) DEFAULT ‘user’ User role: ‘user’ or ‘admin’ created_atTIMESTAMP DEFAULT NOW() Account creation timestamp updated_atTIMESTAMP DEFAULT NOW() Last update timestamp
accounts OAuth provider accounts linked to users.
Column Type Constraints Description idSERIAL PRIMARY KEY Account identifier user_idINTEGER NOT NULL, FK → users(id) Associated user typeVARCHAR(50) NOT NULL ’oauth’ or ‘email’ providerVARCHAR(50) NOT NULL ’google’, ‘email’, etc. provider_account_idVARCHAR(255) NOT NULL Provider’s user ID refresh_tokenTEXT OAuth refresh token access_tokenTEXT OAuth access token expires_atINTEGER Token expiration timestamp token_typeVARCHAR(50) Token type (e.g., ‘Bearer’) scopeTEXT OAuth scopes id_tokenTEXT OpenID Connect ID token session_stateVARCHAR(255) Session state created_atTIMESTAMP DEFAULT NOW() Creation timestamp
The (provider, provider_account_id) combination is unique.
sessions Active user sessions.
Column Type Constraints Description idSERIAL PRIMARY KEY Session identifier session_tokenVARCHAR(255) NOT NULL, UNIQUE Session token user_idINTEGER NOT NULL, FK → users(id) Associated user expiresTIMESTAMP NOT NULL Session expiration created_atTIMESTAMP DEFAULT NOW() Creation timestamp
verification_tokens Email verification and password reset tokens.
Column Type Constraints Description identifierVARCHAR(255) NOT NULL, PK User identifier (email) tokenVARCHAR(255) NOT NULL, PK Verification token expiresTIMESTAMP NOT NULL Token expiration
user_passwords Password hashes for email/password authentication.
Column Type Constraints Description idSERIAL PRIMARY KEY Record identifier user_idINTEGER NOT NULL, FK → users(id) Associated user password_hashVARCHAR(255) NOT NULL Hashed password created_atTIMESTAMP DEFAULT NOW() Creation timestamp updated_atTIMESTAMP DEFAULT NOW() Last update timestamp
Property Management Tables property_types Types of properties (Casa, Departamento, etc.).
Column Type Constraints Description idSERIAL PRIMARY KEY Type identifier nameVARCHAR(100) NOT NULL, UNIQUE Display name slugVARCHAR(100) NOT NULL, UNIQUE URL-friendly slug
Pre-populated data (14 types):
Casa, Departamento, PH, Terreno, Local comercial, Galpón, Oficina comercial, Campo, Depósito, Bodega, Consultorio, Fondo de comercio, Hotel, Cochera
property_subtypes Subtypes for more specific property classification.
Column Type Constraints Description idSERIAL PRIMARY KEY Subtype identifier type_idINTEGER FK → property_types(id) Parent property type nameVARCHAR(100) NOT NULL Display name slugVARCHAR(100) NOT NULL URL-friendly slug
Examples:
Casa: “Casa en barrio cerrado”, “Casa en country”
Departamento: “Monoambiente”, “Departamento tipo dúplex”, “Departamento en torre”
PH: “PH con jardín”, “PH al fondo”
operation_types Types of property operations.
Column Type Constraints Description idSERIAL PRIMARY KEY Operation identifier nameVARCHAR(50) NOT NULL, UNIQUE Display name slugVARCHAR(50) NOT NULL, UNIQUE URL-friendly slug
Pre-populated data:
Venta, Alquiler, Alquiler temporal, Permuta
property_conditions Property condition/state.
Column Type Constraints Description idSERIAL PRIMARY KEY Condition identifier nameVARCHAR(50) NOT NULL, UNIQUE Condition name
Pre-populated data:
A estrenar, Bueno, Muy bueno, Excelente, Reciclado, Para refaccionar
currencies Supported currencies for pricing.
Column Type Constraints Description idSERIAL PRIMARY KEY Currency identifier codeVARCHAR(3) NOT NULL, UNIQUE ISO currency code symbolVARCHAR(10) NOT NULL Currency symbol nameVARCHAR(50) NOT NULL Currency name
Pre-populated data:
ARS (Peso Argentino), USD (Dólar Estadounidense), EUR (Euro)
features Available property features/amenities.
Column Type Constraints Description idSERIAL PRIMARY KEY Feature identifier nameVARCHAR(100) NOT NULL, UNIQUE Feature name
Pre-populated data:
Parrilla, Pileta, Aire acondicionado, SUM, Gimnasio, Seguridad 24hs, Ascensor, Balcón, Terraza, Jardín, Cochera, Lavadero, Internet, Electricidad
Property tags for highlighting special characteristics.
Column Type Constraints Description idSERIAL PRIMARY KEY Tag identifier nameVARCHAR(100) NOT NULL, UNIQUE Tag name
Pre-populated data:
Oportunidad, Crédito hipotecario, Apto profesional, Acepta permuta, Apto crédito, Amoblado, Acepta mascotas, Dueño directo
property_statuses Property publication status.
Column Type Constraints Description idSERIAL PRIMARY KEY Status identifier nameVARCHAR(50) NOT NULL, UNIQUE Status display name slugVARCHAR(50) NOT NULL, UNIQUE URL-friendly slug
Pre-populated data:
Activo, Pendiente, Vendido, Alquilado, Pausado
properties Main property listings table.
View complete properties table schema
Column Type Constraints Description idSERIAL PRIMARY KEY Property identifier titleVARCHAR(255) NOT NULL Property title descriptionTEXT Detailed description property_type_idINTEGER FK → property_types(id) Property type property_subtype_idINTEGER FK → property_subtypes(id) Property subtype operation_type_idINTEGER FK → operation_types(id) Operation type status_idINTEGER FK → property_statuses(id) Publication status priceNUMERIC(12,2) NOT NULL Property price original_priceNUMERIC(12,2) Original price (for discounts) currency_idINTEGER FK → currencies(id) Price currency expensesNUMERIC(12,2) Monthly expenses surface_total_m2NUMERIC(8,2) Total surface area surface_covered_m2NUMERIC(8,2) Covered surface area lot_size_m2NUMERIC(10,2) Lot/land size roomsINTEGER Number of rooms bedroomsINTEGER Number of bedrooms bathroomsINTEGER Number of bathrooms year_builtINTEGER Construction year condition_idINTEGER FK → property_conditions(id) Property condition floorINTEGER Floor number has_balconyBOOLEAN DEFAULT FALSE Has balcony has_terraceBOOLEAN DEFAULT FALSE Has terrace has_garageBOOLEAN DEFAULT FALSE Has garage has_laundryBOOLEAN DEFAULT FALSE Has laundry has_gardenBOOLEAN DEFAULT FALSE Has garden pets_allowedBOOLEAN DEFAULT FALSE Pets allowed contact_phoneVARCHAR(50) Contact phone contact_emailVARCHAR(255) Contact email user_idINTEGER FK → users(id) Property owner/agent views_countINTEGER DEFAULT 0 View counter created_atTIMESTAMP DEFAULT NOW() Creation timestamp updated_atTIMESTAMP DEFAULT NOW() Last update timestamp published_atTIMESTAMP Publication timestamp url_slugVARCHAR(255) UNIQUE URL-friendly slug search_vectorTSVECTOR Full-text search vector
property_locations Geographic location data for properties.
Column Type Constraints Description idSERIAL PRIMARY KEY Location identifier property_idINTEGER FK → properties(id), UNIQUE Associated property streetVARCHAR(255) Street name street_numberVARCHAR(20) Street number floor_apartmentVARCHAR(50) Floor/apartment neighborhoodVARCHAR(100) Neighborhood cityVARCHAR(100) NOT NULL City provinceVARCHAR(100) NOT NULL Province/state postal_codeVARCHAR(20) Postal code countryVARCHAR(100) DEFAULT ‘Argentina’ Country latitudeDECIMAL(10,7) GPS latitude longitudeDECIMAL(10,7) GPS longitude show_exact_addressBOOLEAN DEFAULT FALSE Display full address
property_images Property photos and images.
Column Type Constraints Description idSERIAL PRIMARY KEY Image identifier property_idINTEGER FK → properties(id) Associated property image_urlTEXT NOT NULL Image URL order_indexINTEGER DEFAULT 0 Display order is_mainBOOLEAN DEFAULT FALSE Main/featured image uploaded_atTIMESTAMP DEFAULT NOW() Upload timestamp
property_features Many-to-many relationship between properties and features.
Column Type Constraints Description property_idINTEGER FK → properties(id), PK Property reference feature_idINTEGER FK → features(id), PK Feature reference
Many-to-many relationship between properties and tags.
Column Type Constraints Description property_idINTEGER FK → properties(id), PK Property reference tag_idINTEGER FK → tags(id), PK Tag reference
User Interaction Tables user_favorites User’s favorite/saved properties.
Column Type Constraints Description idSERIAL PRIMARY KEY Favorite identifier user_idINTEGER FK → users(id) User reference property_idINTEGER FK → properties(id) Property reference created_atTIMESTAMP DEFAULT NOW() Creation timestamp
The (user_id, property_id) combination is unique.
Indexes Performance indexes for common queries:
Authentication Indexes CREATE INDEX idx_sessions_token ON sessions (session_token); CREATE INDEX idx_sessions_user_id ON sessions (user_id); CREATE INDEX idx_accounts_user_id ON accounts(user_id);
Property Indexes CREATE INDEX idx_properties_type ON properties(property_type_id); CREATE INDEX idx_properties_operation ON properties(operation_type_id); CREATE INDEX idx_properties_status ON properties(status_id);
Location Indexes CREATE INDEX idx_locations_city ON property_locations(city); CREATE INDEX idx_locations_province ON property_locations(province);
Full-Text Search Index CREATE INDEX idx_properties_search ON properties USING GIN(search_vector);
The GIN index enables fast full-text search on property titles and descriptions in Spanish.
Triggers Automatic database triggers for data maintenance.
Update Timestamps Automatically updates updated_at columns:
CREATE OR REPLACE FUNCTION update_updated_at_column () RETURNS TRIGGER AS $$ BEGIN NEW . updated_at = NOW (); RETURN NEW; END ; $$ language 'plpgsql' ; CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); CREATE TRIGGER update_properties_updated_at BEFORE UPDATE ON properties FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
Full-Text Search Vector Automatically maintains search vectors for properties:
CREATE OR REPLACE FUNCTION update_property_search_vector () RETURNS TRIGGER AS $$ BEGIN NEW . search_vector : = setweight(to_tsvector( 'spanish' , COALESCE ( NEW . title , '' )), 'A' ) || setweight(to_tsvector( 'spanish' , COALESCE ( NEW . description , '' )), 'B' ); RETURN NEW; END ; $$ language 'plpgsql' ; CREATE TRIGGER update_properties_search_vector BEFORE INSERT OR UPDATE ON properties FOR EACH ROW EXECUTE FUNCTION update_property_search_vector();
Title text is weighted higher (‘A’) than description (‘B’) in search results
Uses Spanish language configuration for proper stemming and stop words
Relationships Diagram
Users & Authentication:
users ← accounts (one-to-many)users ← sessions (one-to-many)users ← user_passwords (one-to-one) Properties & Metadata:
properties → property_types (many-to-one)properties → property_subtypes (many-to-one)properties → operation_types (many-to-one)properties → property_statuses (many-to-one)properties → currencies (many-to-one)properties → property_conditions (many-to-one)properties → users (many-to-one) Property Details:
properties ← property_locations (one-to-one)properties ← property_images (one-to-many)properties ↔ features (many-to-many via property_features)properties ↔ tags (many-to-many via property_tags) User Interactions:
users ↔ properties (many-to-many via user_favorites) Database Setup To set up the complete database schema:
# Create the database creatdb inmobiliaria # Load the schema psql -d inmobiliaria -f inmobiliaria_complete_schema.sql # Verify tables were created psql -d inmobiliaria -c "\dt"
All foreign key relationships use ON DELETE CASCADE, meaning deleting a parent record will automatically delete related child records.
Next Steps