Engineering · PostgreSQL · Flyway · Integrations

Data model & integrations.

How CPLK's data is shaped, how schema evolves, and how the system talks to PayHere, Keycloak, R2, RabbitMQ, Redis and SMTP.

Snapshot

Entities

Migrations

142

Tenant-scoped

~45

extend TenantAwareEntity

External systems

PayHere · Keycloak · R2 · RabbitMQ · SMTP

Core ERD

The headline aggregates and their relationships. Tenant-scoped entities all carry agency_id; soft-deletable entities are shown with a small marker.

erDiagram AGENCY ||--o{ USER : "employs" AGENCY ||--o{ PROPERTY : "owns" AGENCY ||--o{ SUBSCRIPTION : "has" AGENCY ||--o{ INQUIRY : "receives" AGENCY ||--o{ CONTACT : "has" AGENCY ||--o{ BLOG : "publishes" AGENCY ||--o{ ARTICLE : "publishes" AGENCY ||--o{ AUDIT_LOG : "trail" AGENCY ||--o{ FINANCIAL_AUDIT_LOG : "ledger" USER ||--o{ PROPERTY : "agent_id" USER ||--o{ INQUIRY : "assigned_to" USER ||--o{ FAVORITE : "favourites" SUBSCRIPTION }o--|| PACKAGE : "plan" SUBSCRIPTION ||--o{ POINTS_TRANSACTION : "credit ledger" PROPERTY ||--o{ PROPERTY_IMAGE : "media" PROPERTY ||--o{ PROPERTY_CONTACT : "contacts" PROPERTY ||--o{ PROPERTY_HISTORY : "audit" PROPERTY ||--o{ PROPERTY_BOOST : "boosts" PROPERTY ||--o{ INQUIRY : "leads" PROPERTY ||--o{ FAVORITE : "favourited" PROPERTY ||--o| PROPERTY_PAYMENT_SLIP : "slip" PROPERTY ||--o{ PROPERTY_REFUND_REQUEST : "refunds" PAYMENT ||--o| INVOICE : "generates" PAYMENT }o--|| SUBSCRIPTION : "for" INQUIRY ||--o{ INQUIRY_MESSAGE : "thread" AGENCY { uuid id PK string name string slug "unique" string email "encrypted" enum status enum agency_type uuid keycloak_org_id jsonb settings } USER { uuid id PK uuid agency_id FK uuid identity_id "Keycloak · unique" enum role enum status bool verified bool onboarding_completed } PROPERTY { uuid id PK uuid agency_id FK uuid agent_id FK string slug "unique per agency" enum property_type enum transaction_type enum status decimal price uuid city_id FK bool deleted } SUBSCRIPTION { uuid id PK uuid agency_id FK uuid package_id FK uuid user_id FK "for PROPERTY_OWNER" enum status timestamp current_period_end timestamp grace_period_end int remaining_credits int topup_credits bool auto_renew } PAYMENT { uuid id PK uuid agency_id FK uuid subscription_id FK string order_id "unique" enum status enum payment_type decimal amount string currency "LKR" } PROPERTY_BOOST { uuid id PK uuid property_id FK uuid agency_id FK enum boost_type timestamp start_date timestamp end_date enum status } INQUIRY { uuid id PK uuid property_id FK uuid agency_id FK uuid agent_id FK string email string phone "encrypted" enum status enum source } AUDIT_LOG { uuid id PK uuid agency_id FK uuid user_id FK string entity_type uuid entity_id enum action jsonb old_values jsonb new_values } FINANCIAL_AUDIT_LOG { uuid id PK enum event_type uuid agency_id FK uuid entity_id decimal amount int credit_delta }

Simplified ERD. Tenant-scoped entities (Property, Inquiry, Boost, Blog, Article, Contact, etc.) all carry agency_id and a Hibernate filter pins reads to it.

Entity catalogue

Group	Entity	Highlights
Tenant root	`Agency`	UUID PK · unique slug · encrypted PII (email, phone, address) · `keycloak_org_id` · JSONB `settings`. Not itself tenant-aware.
Tenant root	`User`	`identity_id` UNIQUE (Keycloak sub) · 11-value role enum · soft `status` · `onboarding_completed`.
Properties	`Property`	TenantAware · slug unique per agency · soft-delete (`deleted` + partial unique index) · 6-state lifecycle · `@SQLRestriction` filters deleted rows.
	`PropertyImage`	base key + dimensions · sort order · primary flag · variant generation async.
	`PropertyContact`	Channel flags (WhatsApp, Botim, Telegram, WeChat) · multiple per property.
	`PropertyHistory`	Immutable state transition log.
	`PropertyPaymentSlip`	Owner-uploaded payment proof · FINANCIAL_OFFICER verifies.
	`PropertyRefundRequest`	Refund states: SUBMITTED · APPROVED · REJECTED · REFUND_COMPLETED.
Billing	`Package`	Fixed UUIDs for system packages · MONTHLY / YEARLY / PAY_AS_YOU_GO · `target_audience` filter.
	`Subscription`	Agency-scoped (UNIQUE on active per agency) or user-scoped (PROPERTY_OWNER) · grace period · annual commitment · auto-renew · stored customer token.
	`Payment`	`order_id` UNIQUE for idempotency · PayHere payment id · 5-state lifecycle · payment type enum.
	`Invoice`	Generated async after payment success · PDF via PDFBox.
	`BoostPackage`	One-off point bundles seeded in V30 (Starter / Pro / Enterprise).
	`PointsTransaction`	Immutable EARN/SPEND ledger · balance_after on each row.
Boost	`PropertyBoost`	Time-windowed · FEATURED / HIGHLIGHTED · auto-expires.
Boost
Engagement	`Inquiry`	UNIQUE(user_id, property_id) · encrypted phone · IP + UA captured · agent assignment.
	`InquiryMessage`	Threaded conversation · `read_by_submitter` flag.
	`Favorite`	User × Property; no analytics row.
Content	`Blog` · `Article`	TenantAware · JSONB body · slug unique · draft fields for in-place revision of published posts (`hasPendingDraft`).
	`BlogHistory` · `ArticleHistory`	Immutable change log.
	`ArticleCategory`	Reference.
Audit	`AuditLog`	ImmutableEntity · old/new values JSONB · user role & agency denormalised for fast reports.
Audit	`FinancialAuditLog`	Separate ledger for money & credit movements · positive=inflow, negative=outflow.
Notifications	`Notification`	In-app · 10+ types · pushed via SSE.
	`NotificationDeliveryLog`	Email/SMS delivery status.
	`NotificationPreference`	Per-user opt-out per type.
Reference	`City` · `District` · `PopularLocationView`	Read-only views used by lookup endpoints.
System	`SystemConfig`	Key-value · SUPER_ADMIN write · includes `CREDIT_VALUE_LKR`, `MINIMUM_SUBSCRIPTION_CHARGE_PCT`.

Multi-tenancy in the schema

Three layers stop tenant leakage:

Column — every business table has agency_id UUID NOT NULL with an FK to agencies and an index.
Hibernate filter — @FilterDef(name="tenantFilter", parameters=@ParamDef(name="agencyId", type=UUID.class)) on TenantAwareEntity with @Filter(condition="agency_id = :agencyId").
AOP enable — TenantFilterAspect wraps every repository call and enables the filter with the current ThreadLocal tenant.

// TenantAwareEntity.java
@FilterDef(name = "tenantFilter",
           parameters = @ParamDef(name = "agencyId", type = UUID.class))
@Filter(name = "tenantFilter", condition = "agency_id = :agencyId")
@MappedSuperclass
public abstract class TenantAwareEntity extends BaseEntity {
    @Column(name = "agency_id", nullable = false) private UUID agencyId;
}

Indexes & constraints worth knowing

Index / constraint	What it guarantees
`UNIQUE (agency_id, slug) WHERE deleted = false` on `properties`	Slug uniqueness per tenant survives soft-deletes.
`UNIQUE (order_id)` on `payments`	PayHere webhook idempotency.
`UNIQUE (identity_id)` on `users`	One Keycloak user maps to exactly one CPLK user.
`UNIQUE (agency_id) WHERE status='ACTIVE'` on `subscriptions`	At most one active subscription per agency.
`UNIQUE (submitted_by_user_id, property_id)` on `inquiries`	One inquiry per user per property.
Composite (`agency_id`, `status`) on `properties`	Agency dashboard queries.
Composite (`status`, `published_at DESC`) on `properties`	Public listing pagination.
Composite (`entity_type`, `entity_id`) on `financial_audit_logs`	Full trace for any transaction.

Flyway timeline

142 migrations grouped into eras:

timeline title Flyway eras section Foundation (V1–V11) V1–V11 : agencies · users · packages · subscriptions · properties · images · boosts · seed section Auth & onboarding (V12–V23) V12–V23 : onboarding fields · notifications · inquiries · audit_logs · analytics · CPLK system agency section Payments & subscriptions (V24–V52) V24–V52 : PayHere · invoices · property history · districts/cities · soft delete · CPLK individual agency · per_seat section Contacts & user-scoped subs (V53–V65) V53–V65 : contacts · PROPERTY_APPROVER · CUSTOMER_SUPPORT_AGENT · user subs · verification · type renames section Content & blog (V66–V76) V66–V76 : contact_submissions · grace_period_end · customer_token · blogs & draft fields section Refunds & financial (V77–V87) V77–V87 : refund tracking · upgrades · annual commitment · PROPERTY_VIEWER · financial_audit_logs · created_by/last_modified_by section Standardisation (V88–V142) V88–V142 : nullable invoice · PCI-clean payments · city FK · slug-soft-delete index · audit denormalisation · system announcements · support tickets

Migrations are never edited once applied. To fix a checksum mismatch, write a corrective migration — never modify the original.

Integrations

PayHere

Sri Lanka's primary payment gateway. CPLK uses three of its APIs:

API	Used for	Implementation
Hosted checkout	Subscription upgrade, boost purchase, PROPERTY_OWNER per-listing payment	`PaymentService.initiatePreapprovalCheckout` · `PayHereHashGenerator` (MD5 + secret)
Webhook	Async status confirmation	`PaymentController#handleNotification` · idempotent via `order_id` UNIQUE · `findByOrderIdForUpdate` row lock
Charging API	Recurring renewals via stored token	`PayHereRecurringService.chargeViaToken` · OAuth bearer (cached, 401 → refresh once)

Keycloak

Realm cplk · backend client cplk-backend · frontend client cplk-frontend.
App provisions users via Admin REST (KeycloakService.createUser); local User.identity_id holds the Keycloak sub.
Realm roles map 1:1 with CPLK UserRole; mapped to ROLE_* authorities at JWT decode time.
Each agency gets a Keycloak organisation; users are added/removed via OrganizationMembersResource.
Session APIs used for "log out everywhere" on password reset.

Storage

Three implementations behind one interface (StorageService):

R2

Production. Cloudflare R2 (S3-compatible). Presigned PUT URLs for uploads.

S3

Backward-compatibility for the AWS path. Same SDK.

Local

Dev fallback. Stores under ./uploads; served from /api/uploads.

ImageProcessingService generates thumbnail (300×200) and medium (800×600) WebP variants asynchronously after upload and strips EXIF metadata. Eight upload services exist for different domains (property, blog, agency logo, agency cover, user avatar, verification doc, etc.).

Notifications & messaging

Email · SMTP

Thymeleaf templates under src/main/resources/templates/emails/. EmailServiceImpl runs in prod; NoOpEmailService in dev (silent).

Sent @Async; failures logged but never roll back the calling transaction.

In-app · SSE + RabbitMQ

NotificationService.create persists, audits, then pushes over SSE. Single node uses an in-memory broadcaster; multi-node uses RedisSseNotificationBroadcaster for cross-node fan-out.

RabbitMQ carries email delivery messages with a DLQ + alert handler (DlqNotificationHandler).

Caffeine cache

Caffeine is used for read-heavy lookups; writes evict aggressively:

Cache	Used by	Eviction
`properties`, `property-slug`, `property-featured`, `property-recent`, `property-home-featured`	Public listing pages	On any property update/delete
`property-type-counts`, `transaction-type-counts`	Search facets	Periodic + on property publish
`transactionSummary`	Agency dashboard	1 h TTL
`dashboardStats`	Inquiry counts	On new inquiry / status change
Lookups (packages, cities, districts)	Forms, filters	Manual on system config change

Data protection

PII columns (agency & user phone, email, address; inquiry phone; contact phone/email) encrypted at rest via EncryptedStringConverter.
No card data ever stored — PayHere is the system of record for card tokens; CPLK persists only the customer_token reference on the subscription.
HTML user input sanitised via owasp-java-html-sanitizer before persistence (article/blog bodies).
Soft delete preserves audit trail; data is never physically removed in business flows.