Skip to content

Database

Software

For most users, please interact with the database using Azure Data Studio, which can be installed through the Company Portal app.

For programmatic database access while using Python, HOURCAR primarily uses pyODBC and sqlanarchy. Access using R is primarily handled using odbc.

Connecting

The HOURCAR database is hosted on Azure SQL, and can be accessed at: hourcar-mn.database.windows.net

Access to the database for individuals is handled through Entra ID (your HOURCAR Microsoft 365 account). Programmatic access (e.g. scripts and automations) should be handled through a service account. Please contact IT to request access to the system.

The database is protected by firewall. Only allowed IP addresses can connect. For most users connecting remotely, please request access to the HOURCAR VPN so that your requests are originating from the office.

How it works

The database ingests new information through a series of scripts, hosted in the api-bridge GitHub repository. These run on a regular cron schedule on self-hosted GitHub Runners, through the GitHub Actions workflows set up in that repository. More documentation specifically about the scripts are held in that repository, but generally speaking, they work by contacting an API service (Vulog, Lytx, etc.), extracting data from the API, doing some transformations, and adding it to the database. Magic! 🪄

Naming Conventions

All column names should be set up using PascalCase.

Extra Tables

There are some extra tables in the database, beyond what exists in the documentation below. Some of these are archived tables that are no longer being used (i.e. reservations, stations, scheduled maintenance, etc). In addition, some tables have their own staging tables (i.e. trips, users, credits) to make the insertion and update process more efficient.

Schema

graph TD
    CREDITS[Credits] --- USERS[Users]
    CREDITS --- TRIPS[Trips]
    CREDITS --- ZONES[Zones]
    CREDITS --- INVOICES[Invoices]

    INVOICES --- USERS
    INVOICES --- TRIPS
    INVOICES --- VEHICLES[Vehicles]

    LYTXEVENTS[Lytx Events] --- USERS
    LYTXEVENTS --- VEHICLES
    LYTXEVENTS --- TRIPS

    MOBILITYPLANLOGSEXPANDED[Mobility Plan Logs Expanded] --- USERS

    POINTSOFINTEREST[POI] --- ZONES

    RADARREQUESTS[Radar Requests] --- USERS
    RADARREQUESTS --- ZONES

    TICKETS[Tickets] --- USERS
    TICKETS --- VEHICLES
    TICKETS --- TRIPS

    TRIPS --- USERS
    TRIPS --- VEHICLES
    TRIPS --- ZONES

    USERS --- VEHICLES
    USERS --- TRIPS

    VEHICLEEVENTS[Vehicle Events] --- VEHICLES
    VEHICLELOGS[Vehicle Logs] --- VEHICLES

    VEHICLES --- ZONES
    VEHICLES --- USERS

    ZONES --- VEHICLES
    ZONES --- TRIPS

Credits

Column Name Type Notes
CreditID uniqueidentifier
UserID uniqueidentifier
InitialAmount float The initial dollar value of the credit.
ValidityStartDateUTC datetime
ValidityEndDateUTC datetime
Notes nvarchar(max)
DiscountCategory nvarchar(50)
OneTimeUsage bit
AvailableAmount float The dollar value of the credit that is currently available (InitialAmount - UsedAmount = AvailableAmount).
UsedAmount float The dollar value of the credit that has already been used (InitialAmount - AvailableAmount = UsedAmount).
OriginID nvarchar(50)
EntityID uniqueidentifier
CreditAlreadyUsed bit
UpdateDateUTC datetime
Type nvarchar(50)
Usage nvarchar(50)
ZoneID nvarchar(50)
TripID nvarchar(50)
PromoCodeReference nvarchar(255) The promocode used to redeem the credit.

Invoices

Column Name Type Notes
InvoiceID varchar(50)
UserID varchar(50)
PricingID varchar(50)
TripID varchar(50)
InvoiceDateUTC datetime2
UpdateDateUTC datetime2
InvoiceYear int
InvoiceStatus varchar(40)
Amount float This is the final amount charged to the member, not including tax or anything paid with system credits.
ProductID varchar(50)
IsProductTaxIncluded bit
TaxName varchar(50)
TaxRate decimal(5,2)
AmountPayWithSystemCredit decimal(10,2)
Attempts int
PSPName nvarchar(90)
PSPReference varchar(50)
IsRefunded bit
PaymentInProgress bit
PaymentDateUTC datetime
ExternalPaymentRequesterID varchar(50)
ExternalPaymentNotes varchar(250)
IsPaidExternally bit
RefundInProgress bit
Component varchar(50)
OriginID varchar(50)
PSPIdempotency varchar(100)
PSPReferencePreAuth varchar(50)
AmountPreAuth decimal(10,2)
PreAuthEnabled bit
MobilityPlan nvarchar(255)
TaxAmount float
ProductType varchar(50)
ProductName nvarchar(255)
TaxAmountPayWithSystemCredit float

LytxEvents

Column Name Type Notes
EventID nvarchar(255)
TriggerName nvarchar(255)
TriggerSubTypeName nvarchar(255)
Behaviors nvarchar(255)
RecordDateUTC datetime
Score int This numeric score indicates the severity of the event.
LytxVehicleID nvarchar(255)
VulogVehicleID nvarchar(255)
LytxVehicleName nvarchar(255)
VulogTripID nvarchar(255)
ForwardMax decimal(10,5)
LateralMax decimal(10,5)
Speed_mph decimal(10,5)
Latitude decimal(10,7)
Longitude decimal(10,7)
Heading decimal(10,2)
VulogUserID nvarchar(255)
UserFirstName nvarchar(255) From the Lytx system
UserLastName nvarchar(255) From the Lytx system

MobilityPlanLogsExpanded

Column Name Type Notes
UserID nvarchar(100)
MobilityPlan nvarchar(100)
YearMonth datetime2
PlanNumber int
Notes nvarchar(50)
AccountStatus nvarchar(50)

PointsOfInterest

Column Name Type Notes
PoiID nvarchar(255)
PoiName nvarchar(100)
Version int
Type nvarchar(50)
VehicleCapacity int The number of parking spaces at the hub
Description nvarchar(max)
Address nvarchar(255)
PostalCode nvarchar(50)
City nvarchar(100)
Longitude float
Latitude float
ZoneID nvarchar(100) This allows for joining to the Zones table.
ZoneName nvarchar(100)
ZoneType nvarchar(50)
VehicleCount int The number of vehicles currently assigned to the hub

RadarRequests

Column Name Type Notes
RadarRequestID uniqueidentifier
Latitude float
Longitude float
Radius int
StartDateUTC datetime2
EndDateUTC datetime2
UserID uniqueidentifier
Models nvarchar(100)
City nvarchar(100)
Neighborhood nvarchar(100)

Tickets

Column Name Type Notes
TicketID nvarchar(255)
UserID nvarchar(100)
TripID nvarchar(100)
VehicleID nvarchar(100)
CreatorID nvarchar(100)
AssignedTo nvarchar(100)
Subject nvarchar(255)
Description nvarchar(max)
Status nvarchar(50)
Priority nvarchar(50)
CreationDateUTC datetime
UpdateDateUTC datetime
WorkedHours float
CustomerFault int
CategoryID nvarchar(50)
CategoryName nvarchar(100)
EventType nvarchar(255)
ModelID nvarchar(50)
GroupID nvarchar(50)
CloseDateUTC datetime
IsManuallyCreated bit
IsManuallyClosed bit
UserIDs nvarchar(max)
VehicleIDs nvarchar(max)
Comments nvarchar(max)
ServiceTripIDs nvarchar(max)
Assignees nvarchar(max)
Watchers nvarchar(max)
Attachments nvarchar(max)

Trips

Column Name Type Notes
TripID nvarchar(255)
UserID nvarchar(100)
ProfileID nvarchar(100)
ProfileType nvarchar(50)
VehicleID nvarchar(100)
TripDistance_mi float
TripDistance_km float
TotalDuration_min float Includes Booking Duration and Trip Duration.
PauseDuration_min float
TripDuration_min float
BookingDuration_min float
DrivingDuration_min float
StartDateUTC datetime2
EndDateUTC datetime2
ServiceType nvarchar(50)
TheorStartDateUTC datetime2
TheorEndDateUTC datetime2
TicketInfoJSON nvarchar(255)
FirstDriveDelayMinutes float
IsAutolock bit
IsCancel bit
IsReleaseOnServer bit
OriginCancelJourney nvarchar(50)
StartLat float
StartLong float
EndLat float
EndLong float
StartChargerZone nvarchar(100)
EndChargerZone nvarchar(100)
StartHubZone nvarchar(150)
EndHubZone nvarchar(150)
StartTaxZone nvarchar(100)
EndTaxZone nvarchar(100)
StartOtherZones nvarchar(255)
EndOtherZones nvarchar(255)
StartNeighborhood nvarchar(100)
EndNeighborhood nvarchar(100)
StartDateLocalTime datetime
EndDateLocalTime datetime
IsOngoing bit
StartAutonomy_mi float
EndAutonomy_mi float

Users

Column Name Type Notes
UserID nvarchar(255)
UserName nvarchar(100)
LastName nvarchar(255)
FirstName nvarchar(255)
MiddleName nvarchar(255)
PreferredName nvarchar(255) Not used in our current system.
AccountStatus nvarchar(50)
Gender nvarchar(50)
Locale nvarchar(50)
RegistrationDateUTC datetime2
BirthDate datetime2
RegistrationNotes nvarchar(max)
DataPrivacyConsent bit
ProfilingConsent bit
MarketingConsent bit
UpdateDateUTC datetime2
HasBusinessProfile bit
HourcarServiceStatus nvarchar(50)
EvieServiceStatus nvarchar(50)
BusinessHourcarServiceStatus nvarchar(50)
BusinessEvieServiceStatus nvarchar(50)
Address nvarchar(max)
CurrentMobilityPlan nvarchar(50)
StreetName nvarchar(255)
City nvarchar(255)
PostalCode nvarchar(20)
Region nvarchar(255)
Country nvarchar(255)
Email nvarchar(100)
UserNotes nvarchar(max)
BusinessName nvarchar(255)
PhoneNumber nvarchar(50)
RFID nvarchar(50)
AddressLat float
AddressLong float
AddressNotes nvarchar(100)
AddressLabel nvarchar(50)
BillingGroup nvarchar(50)
StudentIDExpiryDate datetime2
ProfilesJSON nvarchar(max)
Neighborhood nvarchar(100)

VehicleEvents

Column Name Type Notes
VehicleID nvarchar(255)
EventOrigin nvarchar(100)
EventType nvarchar(255)
EventDateUTC datetime
Failed bit
OriginID nvarchar(100)
Reason nvarchar(100)
ExtraInfo nvarchar(max)
EventDateLocalTime datetime2
Autonomy_mi float

VehicleLogs

Column Name Type Notes
VehicleID varchar(255)
DateHour datetime
Lat float
Long float
TaxZone varchar(255)
VehicleStatus varchar(255)
Reason varchar(255)
Autonomy_mi float

Vehicles

Column Name Type Notes
VehicleID nvarchar(36)
VehicleType nvarchar(255)
VehicleName nvarchar(255)
VehicleNameClean nvarchar(255)
VehicleModel nvarchar(255)
Plate nvarchar(255)
VIN nvarchar(255)
BoxID nvarchar(255)
VehicleStatus int 0 = AVAILABLE, 1= IN USE, 2 = OUT OF SERVICE, 4 = VEHICLE TO INTEGRATE, 5 = UNSYNC
OtherZones nvarchar(255)
ChargerZone nvarchar(100)
HubZone nvarchar(150)
TaxZone nvarchar(100)
Releasable bit
BoxStatus int
Autonomy_km float
Autonomy_mi float
IsCharging bit
Battery float
Odometer_km float
Speed_km float
Latitude float
Longitude float
GpsDateUTC datetime2
LastMovingDateUTC datetime2
IsDoorClosed bit
IsDoorLocked bit
EngineOn bit
LastOosDateUTC datetime2
LastCleanedDateUTC datetime2
ImmobilizerOn bit
BookingStatus int
LastActiveDateUTC datetime2
LastWakeUpDateUTC datetime2
Disabled bit
OutOfServiceReason nvarchar(255)
CleanlinessStatus bit
NeedsRedistribution bit
BatteryUnderThreshold bit
IsBeingTowed bit
AutomaticallyEnableVehicleAfterRangeRecovery bit
Neighborhood nvarchar(255)

Zones

Column Name Type Notes
ZoneID nvarchar(255)
ZoneName nvarchar(255)
Version int
Type nvarchar(255)
Coordinates nvarchar(max)
FirstLongitude float The first longitude coordinate from the polygon list.
FirstLatitude float The first latitude coordinate from the polygon list.