Skema Database & ERD

Sistem memakai PostgreSQL dengan ORM Sequelize 6. Model dimuat otomatis dari models/*.js oleh models/index.js, dan asosiasi didaftarkan via fungsi associate() di tiap model.

Konvensi umum

Aspek	Detail
Nama tabel	Tidak ada `tableName` eksplisit; Sequelize memluralkan nama model (mis. `TransEmailLog` → `TransEmailLogs`).
Primary key	`id` — `INTEGER`, auto-increment.
Timestamps	`createdAt`, `updatedAt` (`DATE`) ada di semua tabel (default Sequelize).
UUID	Banyak tabel punya kolom `uuid` dengan default `uuid_generate_v4()` (ekstensi `uuid-ossp`).

Entity Relationship Diagram

Entitas pusat

Entitas	Peran	Koneksi utama
TransCompanies	Tenant/pelanggan (hub)	domain, kontak, relay, subscription, invoice, config, dokumen
Users	Akun pengguna	terhubung ke company via `TransCompanyContacts`; menerima notifikasi
TransCompanyDomains	Domain pengirim	parent dari `TransEmailLogs` & agregat `TViewCompanyMailUsages`
TransCompanySubscriptions	Langganan aktif	terkait package; menggerakkan quota count
TransCompanyInvoices	Faktur (Zoho/Xendit)	punya payment & tax invoice
TransEmailLog	Log per pesan	terkait domain + map host
TransServicePackages	Katalog produk	dilanggan company
TransCompanyAccountRelays	Kredensial relay Zimbra	one-to-one dengan company

Tabel: Auth & Admin

`Users` — `models/users.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`name`	STRING
`email`	STRING
`password`	STRING	di-hash bcrypt saat set
`resetPasswordToken`	TEXT
`verificationToken`	TEXT
`isPrimary`	BOOLEAN
`status`	ENUM	`active`, `inactive`, `trashed`

Relasi: hasOne TransCompanyContacts; hasMany TransUserEmailNotifications, TransUserNotifications.

`Admins` — `models/admins.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	STRING	(migrasi: UUID)
`name`, `email`, `password`	STRING	password di-hash bcrypt
`profilePicture`	TEXT
`roleId`	INTEGER	FK → `AdminRoles`
`resetPasswordToken`, `verificationToken`	TEXT
`status`	ENUM	`active`, `inactive`, `trashed`

Relasi: belongsTo AdminRoles.

`AdminRoles` — `models/adminroles.js`

Kolom	Tipe
`id`	INTEGER PK
`uuid`	UUID
`name`	STRING

Relasi: hasMany Admins.

`AdminLog` — `models/adminlogs.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`adminId`	INTEGER	FK → `Admins`
`activity`	STRING
`logDescription`	STRING

Relasi: belongsTo Admins.

`BlacklistToken` — `models/blacklisttoken.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`token`	TEXT	refresh token yang dicabut
`expiryDate`	DATE

Tabel: Company Core & Contacts

`TransCompanies` — `models/transcompanies.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`idCompanyZoho`, `idContactZoho`, `idContactPersonsAktiva`	STRING	ID referensi Zoho
`name`, `website`, `address`, `phoneNumber`	STRING	profil
`relayAddress`	STRING
`npwp`, `npwpLocation`	STRING/TEXT	data pajak
`profilePicture`	TEXT
`roleUserPrimary`	STRING
`estimateUserCount`, `estimateCountDomain`, `estimateCountEmailSent`	STRING/INTEGER	estimasi onboarding
`maxDomainAllowed`	INTEGER
`allowedMailCountStart`, `allowedMailCountEnd`	DATE
`typeOfEmail`, `previousMailProvider`, `mailRecipientSources`	STRING
`status`	STRING

Relasi: hasMany TransCompanyContacts, TransCompanyDomains, TransCompanyConfigs, TransCompanyDocuments, TransCompanyInvoices, TransCompanyTaxInvoices, TransCompanySubscriptions; hasOne TransCompanyAccountRelays.

`TransCompanyContacts` — `models/transcompanycontacts.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`userId`	INTEGER	FK → `Users`
`name`, `phone`	STRING
`role`	ENUM	`primary`, `billing`, `technical`, `techbill`
`status`	ENUM	`active`, `trashed`
`amcListUid`, `amcSubsUid`	STRING	referensi MailWizz/AMC

Relasi: belongsTo TransCompanies, Users.

`TransCompanyDomains` — `models/transcompanydomains.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`name`	STRING	nama domain
`spfRecord`, `dmarcRecord`, `dkimRecord`, `aRecord`	TEXT	record DNS
`spfStatus`, `dmarcStatus`, `dkimStatus`, `aRecordStatus`	BOOLEAN	status verifikasi
`spfLastCheckDate`, `dmarcLastCheckDate`, `dkimLastCheckDate`	DATE
`notification`	BOOLEAN
`isUsedTrial`	BOOLEAN
`status`	ENUM	`active`, `inactive`, `processing`, `trashed`

Relasi: belongsTo TransCompanies; hasMany TransEmailLog, TViewCompanyMailUsage.

`TransCompanyAccountRelays` — `models/transcompanyaccountrelays.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`zimbraUserId`	STRING	ID akun di Zimbra
`email`, `password`	STRING	kredensial relay
`status`	ENUM	`active`, `processing`, `closed`, `locked`, `suspended`, `trashed`

Relasi: belongsTo TransCompanies.

`TransCompanyConfigs` — `models/transcompanyconfigs.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`configType`	STRING	jenis config (mis. flag trial, state notif, refresh token Zoho)
`params1`, `params2`, `params3`	STRING	nilai konfigurasi

Relasi: belongsTo TransCompanies.

`TransCompanyDocuments` — `models/transcompanydocuments.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`docType`	STRING
`pathSrc`	STRING	path file (S3)
`status`	ENUM	`active`, `trashed`

Relasi: belongsTo TransCompanies.

`TransCompanyTags` — `models/transcompanytags.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`transCompanyContactId`	INTEGER	FK → `TransCompanyContacts` (migrasi saja)
`label`	ENUM	`primary`, `technical`, `billing`, `techbill`, `other`
`status`	ENUM	`active`, `trashed`

Relasi: tidak ada associate() (FK hanya di migrasi).

`UnlockCounter` — `models/unlockCounter.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyId`	INTEGER	FK → `TransCompanies`
`counter`	INTEGER	jumlah percobaan unlock relay
`firstCounterTime`	DATE

Relasi: belongsTo TransCompanies (tanpa reverse hasMany).

Tabel: Billing & Subscription

`TransServicePackages` — `models/transservicepackages.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`name`	STRING
`description`	TEXT
`countTotalUser`	INTEGER
`price`	INTEGER
`maxTotalEmail`, `maxTotalEmailHourly`	INTEGER	kuota email
`maxTotalDomain`	INTEGER
`maxAttachmentSize`	INTEGER
`isHasPhoneSupport`, `isHasDedicatedIp`	BOOLEAN
`itemIdZoho`	STRING
`status`	ENUM	`active`, `inactive`, `trashed`
`statusPublication`	BOOLEAN
`transServiceAddOnPackageId`	INTEGER	FK → add-on

Relasi: hasMany TransCompanySubscriptions; belongsTo TransServiceAddOnPackage.

`TransServiceAddOnPackage` — `models/transserviceaddonpackage.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	STRING
`name`	STRING
`price`	INTEGER
`description`	TEXT
`zohoItemId`	STRING

Relasi: hasMany TransServicePackages.

`TransCompanySubscriptions` — `models/transcompanysubscriptions.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`idRecurringXendit`	STRING	ID recurring payment Xendit
`transCompanyId`	INTEGER	FK → `TransCompanies`
`transServicePackageId`	INTEGER	FK → `TransServicePackages`
`price`	INTEGER
`servicePeriode`	STRING	(di model dideklarasi dua kali; versi STRING menang)
`startPeriode`, `endPeriode`	DATE
`status`	ENUM	`active`, `inactive`, `trashed`, `deleted`
`transCompanyInvoiceId`	INTEGER	referensi invoice (tanpa asosiasi Sequelize)

Relasi: belongsTo TransCompanies, TransServicePackages.

`TransCompanyInvoices` — `models/transcompanyinvoices.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`idEstimateZoho`, `idInvoiceZoho`	STRING	ID dokumen Zoho
`invoiceUrlZoho`	TEXT
`paymentUrlXendit`	TEXT
`serialNumber`	STRING
`transCompanyId`	INTEGER	FK → `TransCompanies`
`paidStatus`	ENUM	`paid`, `unpaid`, `overdue`, `cancelled`
`servicePeriodeStart`, `servicePeriodeEnd`	DATE
`date`, `dueDate`	DATE
`total`, `price`	INTEGER
`paymentMethod`	STRING

Relasi: belongsTo TransCompanies; hasOne TransCompanyTaxInvoices (alias Tax_Invoice), TransCompanyPayments.

`TransCompanyPayments` — `models/transcompanypayments.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyInvoiceId`	INTEGER	FK → `TransCompanyInvoices`
`paymentAmount`	INTEGER
`paymentDate`	DATE
`sourceBank`, `destinationBank`	STRING
`uploadLink`	TEXT	bukti pembayaran
`status`	STRING

Relasi: belongsTo TransCompanyInvoices.

`TransCompanyTaxInvoices` — `models/transcompanytaxinvoices.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyInvoiceId`	INTEGER	FK → `TransCompanyInvoices`
`transCompanyId`	INTEGER	FK → `TransCompanies`
`serialNumber`	STRING
`date`	DATE
`total`	INTEGER
`taxInvoicePath`	STRING
`isSentToClient`	BOOLEAN

Relasi: belongsTo TransCompanyInvoices (alias Tax_Invoice), TransCompanies.

`TransCompanyEmailQuotaCount` — `models/transcompanyemailquotacount.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`transCompanyId`	INTEGER
`transSubsId`	INTEGER	FK → `TransCompanySubscriptions`
`sendingQuota`, `currentSendingCount`	INTEGER	kuota & pemakaian
`eightyPercentAct`, `ninetyPercentAct`, `hundredPercentAct`	BOOLEAN	flag ambang notifikasi
`quotaStartDate`, `quotaEndDate`	DATE
`overQuota`	BOOLEAN
`overQuotaType`	STRING
`allowNextOverQuota`	BOOLEAN	default false

Relasi: belongsTo TransCompanySubscriptions.

Tabel: Email Delivery & Usage

`TransEmailLog` — `models/transemaillog.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`transCompanyDomainId`	INTEGER	FK → `TransCompanyDomains`
`dateWithTimeZone`	DATE
`messageId`	STRING
`subject`	TEXT
`senderAddress`, `recipientAddress`	STRING
`status`	STRING
`mapHostId`, `mapHostSendingId`	INTEGER
`hostResponse`	STRING

Relasi: belongsTo TransCompanyDomains, MapHost.

Tabel legacy

Tidak ada repository/controller yang menulis ke TransEmailLogs di codebase saat ini. Log pengiriman sesungguhnya disimpan di Elasticsearch (dashboard-clean-log), bukan tabel ini. Lihat Logging Pengiriman.

`MapHost` — `models/maphost.js`

Kolom	Tipe
`id`	INTEGER PK
`uuid`	UUID
`hostName`	STRING
`hostIpAddress`	STRING

Relasi: hasMany TransEmailLog.

`TViewCompanyMailUsage` — `models/tviewcompanymailusage.js`

Bukan SQL View

Meskipun prefix TView, ini adalah tabel biasa yang dikelola Sequelize (createTable), bukan SQL VIEW maupun materialized view. Berisi statistik pemakaian email yang sudah di-agregasi oleh kode aplikasi.

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`usageType`	ENUM	`monthly`, `daily`, `weekly`, `yearly`, `total` (runtime memakai nilai pendek seperti `day`)
`transCompanyId`	INTEGER	default 0
`transCompanyDomainId`	INTEGER	default 0
`usageKey`	STRING
`mailSendStatus`	ENUM	`failed`, `delivered`, `allCount`
`isActiveCurrentSubscribe`	BOOLEAN
`countDelivered`, `countFailed`	INTEGER
`currentTotalSubscribe`	INTEGER
`topSenderEmail` / `topSenderEmailValue`	STRING/INTEGER
`topReceiverEmail` / `topReceiverEmailValue`	STRING/INTEGER
`topReceiverDomain` / `topReceiverDomainValue`	STRING/INTEGER
`attachment`	STRING

Relasi: belongsTo TransCompanyDomains.

Tabel: Notifikasi & Konten

`NotificationContent` — `models/notificationcontent.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`date`	DATE
`category`, `title`, `preview`, `content`	STRING
`adminId`	INTEGER	FK → `Admins`
`isDeleted`	BOOLEAN

Relasi: hasMany TransUserNotifications; belongsTo Admins.

`TransUserNotifications` — `models/transusernotifications.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`userId`	INTEGER	FK → `Users`
`contentId`	INTEGER	FK → `NotificationContent`
`isRead`	BOOLEAN
`date`	DATE

Relasi: belongsTo Users, NotificationContent.

`TransUserEmailNotifications` — `models/transuseremailnotifications.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID
`userId`	INTEGER	FK → `Users`
`date`	DATE
`title`	STRING
`context`	TEXT
`isRead`	BOOLEAN

Relasi: belongsTo Users.

Tabel: Jobs & Misc

`Job` — `models/job.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`uuid`	UUID	di-set otomatis di hook
`jobType`	STRING	nama queue / routing worker
`status`	ENUM	`on-queue`, `processing`, `done`, `error`
`params1`	TEXT	payload JSON utama
`params2`	STRING	key sekunder (mis. email relay untuk dedup)
`params3`	STRING	pesan error (JSON) saat gagal

Relasi: hasMany JobDetail.

`JobDetail` — `models/jobdetail.js`

Kolom	Tipe	Keterangan
`id`	INTEGER PK
`transJobId`	INTEGER	FK → `Job`
`payload`	TEXT
`status`	ENUM	`done`, `error`

Relasi: belongsTo Job. Catatan: tabel ini tidak terpakai (tidak ada kode yang membaca/menulisnya).

`TransArticles` — `models/transarticles.js`

Kolom	Tipe
`id`	INTEGER PK
`docType`	STRING
`title`	STRING
`filePath`	STRING

Relasi: tidak ada.

Catatan quirks skema (penting untuk migrasi)

Asosiasi tidak lengkap — mis. TransCompanies tidak punya hasMany UnlockCounter; TransCompanySubscriptions.transCompanyInvoiceId tanpa FK Sequelize.
Drift enum — enum TViewCompanyMailUsages di model berbeda dengan migrasi; runtime memakai nilai pendek (day, dll.).
Kolom duplikat — servicePeriode di TransCompanySubscriptions dideklarasi dua kali.
Mismatch tipe — beberapa uuid STRING di model padahal migrasi UUID.
TransCompanyTags — FK hanya di migrasi, tanpa associate().
Users ↔ TransCompanyContacts — model memakai hasOne padahal data memungkinkan banyak contact per user.
Tabel legacy/tak terpakai — TransEmailLogs, JobDetails.

Detail rekomendasi penanganan ada di Known Issues dan Roadmap Migrasi.

Skema Database & ERD ​

Konvensi umum ​

Entity Relationship Diagram ​

Entitas pusat ​

Tabel: Auth & Admin ​

Users — models/users.js ​

Admins — models/admins.js ​

AdminRoles — models/adminroles.js ​

AdminLog — models/adminlogs.js ​

BlacklistToken — models/blacklisttoken.js ​

Tabel: Company Core & Contacts ​

TransCompanies — models/transcompanies.js ​

TransCompanyContacts — models/transcompanycontacts.js ​

TransCompanyDomains — models/transcompanydomains.js ​

TransCompanyAccountRelays — models/transcompanyaccountrelays.js ​

TransCompanyConfigs — models/transcompanyconfigs.js ​

TransCompanyDocuments — models/transcompanydocuments.js ​

TransCompanyTags — models/transcompanytags.js ​

UnlockCounter — models/unlockCounter.js ​

Tabel: Billing & Subscription ​

TransServicePackages — models/transservicepackages.js ​

TransServiceAddOnPackage — models/transserviceaddonpackage.js ​

TransCompanySubscriptions — models/transcompanysubscriptions.js ​

TransCompanyInvoices — models/transcompanyinvoices.js ​

TransCompanyPayments — models/transcompanypayments.js ​

TransCompanyTaxInvoices — models/transcompanytaxinvoices.js ​

TransCompanyEmailQuotaCount — models/transcompanyemailquotacount.js ​

Tabel: Email Delivery & Usage ​

TransEmailLog — models/transemaillog.js ​

MapHost — models/maphost.js ​

TViewCompanyMailUsage — models/tviewcompanymailusage.js ​

Tabel: Notifikasi & Konten ​

NotificationContent — models/notificationcontent.js ​

TransUserNotifications — models/transusernotifications.js ​

TransUserEmailNotifications — models/transuseremailnotifications.js ​

Tabel: Jobs & Misc ​

Job — models/job.js ​

JobDetail — models/jobdetail.js ​

TransArticles — models/transarticles.js ​

Catatan quirks skema (penting untuk migrasi) ​

Skema Database & ERD

Konvensi umum

Entity Relationship Diagram

Entitas pusat

Tabel: Auth & Admin

`Users` — `models/users.js`

`Admins` — `models/admins.js`

`AdminRoles` — `models/adminroles.js`

`AdminLog` — `models/adminlogs.js`

`BlacklistToken` — `models/blacklisttoken.js`

Tabel: Company Core & Contacts

`TransCompanies` — `models/transcompanies.js`

`TransCompanyContacts` — `models/transcompanycontacts.js`

`TransCompanyDomains` — `models/transcompanydomains.js`

`TransCompanyAccountRelays` — `models/transcompanyaccountrelays.js`

`TransCompanyConfigs` — `models/transcompanyconfigs.js`

`TransCompanyDocuments` — `models/transcompanydocuments.js`

`TransCompanyTags` — `models/transcompanytags.js`

`UnlockCounter` — `models/unlockCounter.js`

Tabel: Billing & Subscription

`TransServicePackages` — `models/transservicepackages.js`

`TransServiceAddOnPackage` — `models/transserviceaddonpackage.js`

`TransCompanySubscriptions` — `models/transcompanysubscriptions.js`

`TransCompanyInvoices` — `models/transcompanyinvoices.js`

`TransCompanyPayments` — `models/transcompanypayments.js`

`TransCompanyTaxInvoices` — `models/transcompanytaxinvoices.js`

`TransCompanyEmailQuotaCount` — `models/transcompanyemailquotacount.js`

Tabel: Email Delivery & Usage

`TransEmailLog` — `models/transemaillog.js`

`MapHost` — `models/maphost.js`

`TViewCompanyMailUsage` — `models/tviewcompanymailusage.js`

Tabel: Notifikasi & Konten

`NotificationContent` — `models/notificationcontent.js`

`TransUserNotifications` — `models/transusernotifications.js`

`TransUserEmailNotifications` — `models/transuseremailnotifications.js`

Tabel: Jobs & Misc

`Job` — `models/job.js`

`JobDetail` — `models/jobdetail.js`

`TransArticles` — `models/transarticles.js`

Catatan quirks skema (penting untuk migrasi)