Skip to main content

🌿 Ulmexa SaaS Entities

This document describes the main entities in the Ulmexa SaaS application, including their fields and relationships.

The architecture is based on a modular multi-tier system with entities for campaigns, clients, users, projects, VPSs, billing, and messaging.

1. Users​

Represents platform users.

  • Fields:

    • id (PK)
    • email
    • first_name
    • last_name
    • password
    • confirm_password
    • user_code
    • last_seen
    • created_date
    • last_modified_date

  • Relationships:

    • One-to-many with projects
    • One-to-many with tickets
    • Many-to-many with teams via team_members

2. Teams​

Represents a group of users collaborating on projects or tickets.

  • Fields:

    • team_id (PK)
    • name
    • description
    • team_code
    • created_by_id
    • is_default
    • created_date
    • last_modified_date

  • Relationships:

    • Many-to-many with users via team_members
    • Many-to-many with projects via team_projects
    • Linked to tickets (assigned team)

3. Projects​

Represents a user or team workspace.

  • Fields:

    • project_id (PK)
    • project_name
    • description
    • user_id (owner)
    • image_url
    • created_date
    • last_modified_date

  • Relationships:

    • One-to-many with clients
    • One-to-many with campaigns
    • One-to-many with VPS
    • Many-to-many with teams via team_projects
    • One-to-many with project_service_entity

4. Clients​

Represents contacts/customers linked to a project.

  • Fields:

    • client_id (PK)
    • first_name, last_name
    • email, address, business_address
    • whatsapp_tel, num_tel
    • gender
    • type_client
    • social_media_availability
    • field_of_activity
    • fiscal_info
    • project_id (FK)
    • created_date, last_modified_date

  • Relationships:

    • Linked to campaign_recipient (emails sent)
    • Belongs to project

5. Campaigns​

Represents email campaigns.

  • Fields:

    • campaign_id (PK)
    • subject, html_content
    • status: draft, scheduled, sent
    • scheduled_at, sent_at
    • success_rate
    • tracking_id
    • project_id (FK)
    • from_mailbox_id (FK)
    • domain_id (FK)
    • created_date, last_modified_date
    • is_sent (boolean)

  • Relationships:

    • One-to-many with campaign_recipient
    • Belongs to a project
    • Sent from a mailbox and domain

6. Campaign Recipient​

Represents a recipient of a campaign.

  • Fields:

    • recipient_id (PK)
    • email
    • opened_at
    • open_count
    • status: delivered, bounced, opened
    • bounce_reason
    • tracking_id
    • campaign_id (FK)
    • client_id (FK)
    • created_date, last_modified_date

  • Relationships:

    • Belongs to campaign
    • Linked optionally to client

7. Mailbox​

Represents an email account on a domain.

  • Fields:

    • mailbox_id (PK)
    • display_name
    • domain_id (FK)
    • quota, tls_enforce_in, tls_enforce_out
    • password_hash
    • is_enabled
    • force_pw_update
    • local_part
    • created_date, last_modified_date

  • Relationships:

    • One-to-many with smtp_credentials
    • Linked to campaigns as sender

8. SMTP Credentials​

Represents credentials for sending emails via a mailbox.

  • Fields:

    • smtp_cred_id (PK)
    • smtp_host, smtp_port, smtp_username, smtp_password
    • smtp_encryption
    • smtp_sender_address
    • mailbox_id (FK)
    • created_date, last_modified_date

  • Relationships:

    • Belongs to mailbox
    • Used by campaign sending system

9. Domain​

Represents registered email domains.

  • Fields:

    • domain_id (PK)
    • domain_name
    • aliases, backupmx, description
    • is_verified
    • mail_provider
    • mailboxes_count, maxquota, quota
    • relay_all_recipients, relay_host
    • restart_sogo, rl_frame, rl_value
    • project_service_service_id (FK)
    • created_date, last_modified_date, active

  • Relationships:

    • Has multiple mailboxes
    • Linked to campaigns for sending

10. Project Service Entity​

Represents services (like OpenStack, Mailcow) attached to a project.

  • Fields:

    • service_id (PK)
    • project_id (FK)
    • activation_date, deactivation_date
    • is_enabled
    • service_type
    • openstack_project_id

  • Relationships:

    • Linked to VPS and domains

11. VPS​

Represents virtual server instances.

  • Fields:

    • vps_id (PK)
    • project_service_service_id (FK)
    • template_id (FK)
    • status, suspended_at
    • floating_ip, internal_ip
    • openstack_instance_id
    • created_at, due_date

  • Relationships:

    • Linked to vps_template
    • Belongs to project_service_entity

12. VPS Template​

Represents VPS OS templates.

  • Fields:

    • id (PK)
    • image_id (FK)
    • flavor_id (FK)
    • active, is_default
    • cloud_init_script, description

  • Relationships:

    • Used to provision VPS instances

13. VPS Flavor​

Represents VPS hardware configuration.

  • Fields:

    • id (PK)
    • name
    • vcpus, ram_mb, disk_gb
    • price
    • created_at, updated_at, active

  • Relationships:

    • Linked to VPS templates and instances

14. Chat​

Represents internal messaging between users.

  • Fields:

    • id (PK)
    • sender_id, receiver_id (FK)
    • project_id (FK)
    • created_date, last_modified_date

  • Relationships:

    • Has multiple messages
    • Linked to users and projects

15. Message​

Represents individual messages in chat or campaign context.

  • Fields:

    • id (PK)
    • content, media_file_path
    • state, type
    • sender_id, receiver_id
    • chat_id (FK)
    • created_date, last_modified_date

  • Relationships:

    • Belongs to chat
    • Optional link to campaign recipients

16. Tickets​

Represents support tickets.

  • Fields:

    • ticket_id (PK)
    • title, description, status, type
    • user_id (FK)
    • created_at, closed_at, last_modified_date

  • Relationships:

    • Assigned to user or team

17. Subscription​

Represents user or organization subscription.

  • Fields:

    • subscription_id (PK)
    • user_id (FK)
    • plan_id (FK)
    • price, start_date, end_date
    • is_active
    • created_date, last_modified_date, next_billing_date

  • Relationships:

    • Linked to invoice and plan

18. Plan​

Defines subscription plan limits.

  • Fields:

    • id (PK)
    • name, description, features
    • active, recommended
    • billing_cycle, price

  • Relationships:

    • Linked to subscriptions and invoices

19. Invoice​

Represents billing invoices.

  • Fields:

    • invoice_id (PK)
    • subscription_id (FK)
    • amount, paid, invoice_number, issue_date
    • subscription_price, vps_price
    • created_date, last_modified_date

  • Relationships:

    • Linked to subscription

20. OS Images​

Represents operating system images for VPS templates.

  • Fields:

    • id (PK)
    • name, os_type, os_version
    • container_format, disk_format
    • size_bytes
    • is_public, created_at, updated_at
    • openstack_image_id

  • Relationships:

    • Used by VPS templates

21. Domain Tags​

Represents custom tags for domains.

  • Fields:

    • domain_domain_id (FK)
    • tags

  • Relationships:

    • Belongs to domain

22. Flyway Schema History​

Tracks database migration history.

  • Fields:
    • installed_rank (PK)
    • version, description, type, script, checksum
    • installed_by, installed_on, execution_time
    • success

ERD Diagram​

Here is the full entity-relationship diagram for Ulmexa SaaS:

Ulmexa ERD