Coast Technology
Three dealer data products mapped into one database, each product's data kept separate
Coast Technology ran website visitor tracking, location tracking and customer data as three dealer products with three databases. We studied the data before writing merge code, planned one database that keeps each product's data apart, and built the foundation and first dealer report on it.
Automotive & DealershipsDealer customer data platformModernized a live product
The challenge
Three dealer data products kept overlapping tables with different columns and separate account records, so no one place held a dealer's visitors and customers.
What we did
A discovery phase on the databases themselves, a written merge plan with a platform ID and access rules on every shared table, then the schema, ETL and scheduling foundation.
The outcome
Coast got a merge strategy for its three databases, the ingestion and scheduling foundation, and the first dealer visitor report, with appends and campaigns planned on top.
- Sources
- Three live products, each with its own database
- Delivered
- Merge plan, base schema, ETL and scheduling
- Access
- Platform ID and access rules on every shared table
- Base
- The visitor tracking product's current database
The starting point
Coast Technology sells data and marketing tools to automotive, RV and powersports dealers: website visitor tracking, location tracking and a customer database, alongside an existing dealer portal whose structure the new platform reused.
What had to change
Dealer data sat in three products with overlapping but different tables and separate account records, so there was no single view of a dealer's visitors and customers. The client wanted one platform to report, append data and run campaigns from.
What could not break
- Each product's data stays separate and access is granted per product
- Records keep their relationships when they move into the shared database
- Account records from three products end up as one set of accounts
The calls we made
We treated it as a data problem first and studied the three databases before deciding anything about features.
- 01
Discovery on the data itself
We listed every table, found the ones shared by all databases and by only some, mapped their columns, identified relationships, analyzed the existing data in each table and reviewed the code, then wrote the merge plan the build would follow.
- 02
One base, not a fresh schema
The plan takes the visitor tracking product's current database as the base, migrates tables that exist only in the other products as they are, and adds the missing columns to shared tables.
- 03
Separation inside one database
A platform ID on every shared table, with access control per platform, keeps each product's data apart while one platform reads them all.
- 04
Move one database at a time, remap accounts
Migration scripts are planned to move each database in turn, create related records with it, merge the account tables and remap account IDs in every related table.
How the system fits together
What we built
Merge plan
Table comparison across the databases, column mapping, relationship analysis, analysis of the existing data and a written schema, access and data migration plan.
Base schema and setup
Schema setup and base code for the unified platform, with the source products' projects and databases set up for analysis.
ETL and scheduling foundation
Generalized ETL packages, a connector framework that schedules cron jobs, CSV parsing and FTP reading helpers, and the first visitor tracking jobs.
Visitor report for dealers
A login, layout and navigation built on the existing portal structure, and a visitor report with search, advanced filters, notes, report generation, a map view and customer details, served by a visitor query endpoint with filters.
How it works
Ingest
Dealer files arrive by FTP or CSV and scheduled jobs pick them up.
Transform
Generalized ETL packages parse each feed and load it into the shared database.
Separate
Each record carries its platform ID, so access rules apply per product.
Query
One endpoint returns visitors with filters for the reports built on top.
What changed
Coast got a merge strategy for its three legacy databases, the schema, ingestion and scheduling foundation for the unified platform, and its first dealer report. Data appends, dealer file matching and campaigns were planned on top, all reading one database instead of three.
Under the hood
- Datics role
- Modernized a live product
- Data model
- One PostgreSQL base database with a platform ID and access control on shared tables.
- Ingestion
- FTP and CSV feeds through generalized ETL packages.
- Scheduling
- A connector framework that schedules cron jobs, after a comparison of job queue libraries for Node.js.
- Hosting
- Development environment on AWS.
Data and retrieval
Cloud and infrastructure
Apps and APIs
Also in the stack
How it shipped
Database discovery
We compared tables, mapped columns, identified relationships and analyzed the data in all three databases.
Merge plan
A written plan for schema migration, schema changes for separation, and data migration with account remapping.
Sprint one
Schema setup, base code, job scheduling and ETL research and the first CSV parsing helper.
Sprint two
Generalized ETL packages, the cron connector framework, FTP reading, the first visitor tracking jobs, the visitor query endpoint and the visitor report screens.
Discovery before sprints
A dedicated discovery phase on the client's databases and code came before any build sprint, and its plan set the build.
Foundation first
The first two sprints put the schema, ETL and scheduling foundation and the visitor report in place, delivered for review, before data appends and campaigns.
What this unlocked
With one data foundation, later work such as email, phone and vehicle data appends, dealer system file matching and campaign deployments has one place to build on.
Related work
Have a product with the same kind of constraints?
Tell us what already exists and what you want it to do. We will tell you what it takes.