Skip to main content

Ticket Field Details & Database Scheme

Released: 4/1/24

Effective: 1/1/23

Updated: 3/28/24

WARNING

Not all fields shown below appear on every format. Please download the text examples at the bottom of this page for each formats details.

Database Scheme

Database Field Name

Display Name

Data Type

ticket

Ticket

varchar(10)

revision

Revision

varchar(3)

completed

Created

varchar(24)

type

Type

varchar(4)

priority

Priority

varchar(4)

category

Category

varchar(4)

lookup

Lookup

varchar(4)

expires

Expires

varchar(24)

account

User

varchar(20)

channel

Channel

varchar(3)

caller_type

Caller Type

varchar(4)

name

Company Name

varchar(40)

address1

Address

varchar(40)

city

City

varchar(30)

cstate

State

varchar(2)

zip

Zip

varchar(9)

phone

Phone

varchar(11)

phone_ext

Ext

varchar(15)

caller

Caller Name

varchar(30)

caller_phone

Caller Phone

varchar(11)

caller_phone_ext

Caller Ext

varchar(15)

caller_language

Caller Language

varchar(15)

contact

Site Contact

varchar(30)

contact_phone

Site Phone

varchar(11)

contact_phone_ext

Site Phone Ext

varchar(15)

contact_email

Site Email

varchar(80)

contact_cell

Site Cell

varchar(11)

cell

Caller Cell

varchar(11)

email

Caller Email

varchar(80)

state

State

varchar(2)

county

County

varchar(40)

place

City/Place

varchar(40)

st_from_address

Address

varchar(11)

st_to_address

n/a

varchar(11)

street

Street

varchar(40)

cross1

Cross Street 1

varchar(40)

cross2

Cross Street 2

varchar(40)

area_in_miles

Area in Sq Miles

decimal(9,6)

area_in_sqft

Area in Sq Feet

int

work_date

Work Date/Time

varchar(24)

response_due

Response Due

varchar(24)

work_type

Type of Work

varchar(80)

done_for

Work Done For

varchar(40)

permit

Permit

varchar(30)

work_order

Work Order

varchar(30)

explosives

Exploisives

varchar(1)

vacuum

Vacuum

varchar(1)

priority_number

Priority

tinyint

minfit_rectangle

Center Generated GPS

varchar(100)

remarks_requested

Re-marks Requested

varchar(1)

remarks_type

Request Re-marks From

varchar(200)

how_delineated

Delineation Method

varchar(60)

project_id

Project ID

varchar(15)

project_name

Project Name

varchar(65)

work_area_zip_codes

Work Area Zip Codes

varchar(30)

boring

Boring

bit

emergency

Emergency

bit

excav_st_walk

Excavation Enters Street/Sidewalk

bit

response_required

Response Required

bit

eml_confirm

Caller Email Confirmation

bit

sms_confirm

Caller SMS Confirmation

bit

contact_eml_confirm

Site Email Confirmation

bit

contact_sms_confirm

Site SMS Confirmation

bit

one_year

One Year

bit

pavement_only

Excavation in Pavement Only

bit

hashtags

Searchable Tags

text

caller_gps

Caller Provided GPS

text

location

Location

text

comments

Comments

text

Schemes by Format Type

JSON (Request Re-marks shown for all possible fields)

NOTE

field, data followed by comment with data type then max length

JSON 
{
  "delivery": {
    "format_version": "1.05.01.8",  //string 20
    "center": "USAS", //string 4
    "sys_id": "A", //string 1
    "member": "MBRCD01", //string 20
    "transmitted": "2022-08-09T10:58:14-0700", //string 24 
    "transmission_type": "TKT", //string 4
    "sequence": 15 //int
  },
  "ticket": {
    "ticket": "A121231234", //string 10
    "revision": "01A", //string 3
    "created": "2022-08-02T10:57:19-0700", //string 24
    "type": "REMK", //string 4
    "priority": "NORM", //string 4
    "category": "LREQ", //string 4
    "lookup": "POLY", //string 4
    "expires": "2022-08-30T23:59:59-0700", //string 24
    "account": "CSR01", //string 20
    "channel": "200", //string 3
    "work_date": "2022-08-04T17:01:00-0700", //string 24
    "response_due": "2022-08-04T17:01:00-0700", //string 24
    "work_type": "REPAIR AND REPLACE SOMETHING", //string 80
    "done_for": "JOES RESTAURANT", //string 40
    "permit": "123456789ABC", //string 30
    "work_order": "AB123456", //string 30
    "project_id": "", //string 15
    "project_name": "", //string 65
    "explosives": true, //boolean
    "vacuum": true, //boolean
    "remarks_type": "ALL", //string 200 (only shown if type = REMK)
    "how_delineated": ["WHITEPAINT"], //array 60
    "boring": false, //boolean
    "excav_st_walk": true, //boolean
    "response_required": true, //boolean
    "one_year": false, //boolean
    "pavement_only": false, //boolean
    "searchable_tags": [
      "#this",
      "#field",
      "#contains",
      "#various",
      "#binary",
      "#data",
      "#with",
      "#unrestricted",
      "#length"
    ], //text unlimted
    "comments": "**REQUEST RE-MARKS** FROM ALL-WORK CONTINUING  PER JAMES WINGATE--[CSR01 08/02/2022 10:57:06 AM]", //text unlimted
    "caller": {
      "name": "JIM SCHWILK", //string 30
      "language": "ENGLISH", //string 15
      "phone": "1231231234", //string 11
      "phone_ext": "12345", //string 15
      "cell": "1231231234", //string 11
      "email": "JIM@DOMAIN.COM" //string 80
    },
    "excavator": {
      "name": "ABC COMPANY", //string 40
      "type": "CONT", //string 4
      "address": "123 MAIN ST", //string 40
      "city": "SOMEWHERE", //string 30
      "state": "NY", //string 2
      "zip": "12345" //string 9
    },
    "site_contact": {
      "name": "JAMES WINGATE", //string 30
      "phone": "1231231234", //string 11
      "email": "JAMES@DOMAIN.COM", //string 80
      "cell": "1231231234" //string 11
    },
    "excavation_area": {
      "state": "CA", //string 2
      "county": "LOS ANGELES", //string 40
      "place": "CARSON", //string 40
      "work_area_zip_codes": "90745", //string 30
      "st_from_address": "12345", //string 11
      "st_to_address": "12345", //string 11
      "street": "MAIN ST", //string 40
      "cross1": "1ST ST", //string 40
      "cross2": "",  //string 40
      "location": "DOG IN YARD", //text unlimted
      "job_size": {
        "feet": 123,
        "miles": 123
      },  //decimal 9,6
      "work_area": {
        "type": "Feature",  //string 
        "properties": null,  //string 
        "geometry": {
          "type": "Polygon", //string 
          "coordinates": [  //array decimal 10,6
            [
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456],
              [-123.123456, 12.123456]
            ]
          ]
        }
      }
    },
    "links": {
      "map": "https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring", //text unlimted 
      "epr": "https://somelink.domain.com/eprlinkwithinformationthatislong" //text unlimited
    },
    "members": [ //each code = string 20 but unlimited codes
      "MBR001",
      "MBR020",
      "MBR039",
      "LONGMBR058",
      "MBR153",
      "MBR172",
      "MBR191",
      "LONGMBR210",
      "MBR324",
      "MBR343",
      "LONGMBR362",
      "MBR077",
      "MBR096",
      "MBR115",
      "LONGMBR134",
      "MBR229",
      "MBR248",
      "MBR267",
      "LONGMBR286"
    ] //text unlimited
  }
}

XML

NOTE

comment with data type then max length, followed by field

XML 
<?xml version="1.0"?>
<NewDataSet>
  <delivery>
    <!-- string 20 -->
    <format_version>1.05.01.8</format_version>
    <!-- string 4 -->
    <center>USASA</center>
    <!-- string 20 -->
    <recipient>MBRCD01</recipient>
    <!-- string 4 -->
    <transmission_type>TKT</transmission_type>
    <!-- string 24 -->
    <transmitted>2024-03-28T22:42:02</transmitted>
    <!-- int -->
    <seq_num>8</seq_num>
    <!-- string 10 -->
    <ticket>A121231234</ticket>
    <!-- string 3 -->
    <revision>01A</revision>
  </delivery>
  <tickets>
    <!-- string 10 -->
    <ticket>A222310033</ticket>
    <!-- string 3 -->
    <revision>01A</revision>
    <!-- string 24 -->
    <created>2024-03-28T09:37:35</created>
    <!-- string 20 -->
    <account>CSR01</account>
    <!-- string 3 -->
    <channel>200</channel>
    <!-- string 24 -->
    <work_date>2024-04-02T17:01:00</work_date>
    <!-- string 1 -->
    <response_required>Y</response_required>
    <!-- string 24 -->
    <response_due>2024-04-02T17:01:00</response_due>
    <!-- string 24 -->
    <replace_by_date>2024-04-25T23:59:59</replace_by_date>
    <!-- string 24 -->
    <expires>2024-04-25T23:59:59</expires>
    <!-- string 1 -->
    <one_year>N</one_year>
    <!-- string 4 -->
    <priority>NORM</priority>
    <!-- string 4 -->
    <type>REMK</type>
    <!-- string 4 -->
    <category>LREQ</category>
    <!-- string 4 -->
    <lookup>POLY</lookup>
    <!-- string 2 -->
    <state>CA</state>
    <!-- string 40 -->
    <county>LOS ANGELES</county>
    <!-- string 40 -->
    <place>CARSON</place>
    <!-- string 30 -->
    <work_area_zip_codes>12345</work_area_zip_codes>
    <!-- string 11 -->
    <st_from_address>12345</st_from_address>
    <!-- string 11 -->
    <st_to_address>12345</st_to_address>
    <!-- string 40 -->
    <street>MAIN ST</street>
    <!-- string 40 -->
    <cross1>1ST ST</cross1>
    <!-- string 40 -->
    <cross2>2ND ST</cross2>
    <polygon>
      <coordinate>
        <!-- decimal 10,6 -->
        <latitude>12.123456</latitude>
        <longitude>-123.123456</longitude>
      </coordinate>
      <coordinate>
        <latitude>12.123456</latitude>
        <longitude>-123.123456</longitude>
      </coordinate>
      <coordinate>
        <latitude>12.123456</latitude>
        <longitude>-123.123456</longitude>
      </coordinate>
      <coordinate>
        <latitude>12.123456</latitude>
        <longitude>-123.123456</longitude>
      </coordinate>
    </polygon>
    <!-- int -->
    <area_in_sqft>123456</area_in_sqft>
    <!-- decimal 9,6 -->
    <area_in_miles>0.132456</area_in_miles>
    <!-- string 60 -->
    <how_delineated>WHITEPAINT</how_delineated>
    <!-- string 1 -->
    <excav_st_walk>Y</excav_st_walk>
    <!-- string 80 -->
    <work_type>REPAIR AND REPLACE SOMETHING</work_type>
    <!-- string 1 -->
    <boring>Y</boring>
    <!-- string 1 -->
    <explosives>N</explosives>
    <!-- string 1 -->
    <vacuum>Y</vacuum>
    <!-- string 1 -->
    <pavement_only>N</pavement_only>
    <!-- string 30 -->
    <permit>123456789ABC</permit>
    <!-- string 30 -->
    <work_order>AB123456</work_order>
    <!-- string 40 -->
    <done_for>JOES RESTAURANT</done_for>
    <!-- string 15 -->
    <project_id>12345</project_id>
    <!-- string 65 -->
    <project_name>JOES PROJECT</project_name>
    <!-- string 200 (only shown if type = REMK) -->
    <remarks_type>ALL</remarks_type>
    <!-- string 40 -->
    <name>ABC COMPANY</name>
    <!-- string 4 -->
    <caller_type>CONT</caller_type>
    <!-- string 40 -->
    <address1>123 MAIN ST</address1>
    <!-- string 30 -->
    <city>SOMEWHERE</city>
    <!-- string 2 -->
    <cstate>NY</cstate>
    <!-- string 9 -->
    <zip>12345</zip>
    <!-- string 11 -->
    <phone>1231231234</phone>
    <!-- string 30 -->
    <caller>JIM SCHWILK</caller>
    <!-- string 15 -->
    <caller_language>ENGLISH</caller_language>
    <!-- string 11 -->
    <cell>1231231234</cell>
    <!-- string 80 -->
    <email>JIM@DOMAIN.COM</email>
    <!-- string 30 -->
    <contact>JAMES WINGATE</contact>
    <!-- string 11 -->
    <contact_phone>1231231234</contact_phone>
    <!-- string 80 -->
    <contact_email>JAMES@DOMAIN.COM</contact_email>
    <!-- text unlimited -->
    <location xml:space="preserve">DOG IN YARD</location>
    <!-- text unlimited -->
    <comments xml:space="preserve">**REQUEST RE-MARKS** FROM ALL - WORK CONTINUING  PER JIM SCHWILK--[CSR01 03/28/2024 09:34 AM]</comments>
    <memberlist>
      <!-- each code = string 20, but unlimited codes -->
      <member>MBR001</member>
      <member>MBR020</member>
      <member>MBR039</member>
      <member>LONGMBR058</member>
      <member>MBR153</member>
      <member>MBR172</member>
      <member>MBR191</member>
      <member>LONGMBR210</member>
      <member>MBR324</member>
      <member>MBR343</member>
      <member>LONGMBR362</member>
      <member>MBR077</member>
      <member>MBR096</member>
      <member>MBR115</member>
      <member>LONGMBR134</member>
      <member>MBR229</member>
      <member>MBR248</member>
      <member>MBR267</member>
      <member>LONGMBR286</member>
      <member>WBMWD</member>
    </memberlist>
    <!-- text unlimited -->
    <map_url>https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring</map_url>
    <!-- text unlimited -->
    <response_url>https://somelink.domain.com/eprlinkwithinformationthatislong</response_url>
    <!-- text unlimited -->
    <printable_text xml:space="preserve">
MBRCD01  00008Y USAS 03/28/24 22:42:02 A222310033-01A REMK NORM POLY LREQ 

Ticket: A222310033 Rev: 01A Created: 03/28/24 09:37 User: CSR01 Chan: 200

Work Start: 04/02/24 17:01 Legal Start: 04/02/24 17:01 Expires: 04/25/24 23:59
Response required: Y Priority: 2
Re-Marks Required: ALL

Excavator Information
Company: ABC COMPANY
Co Addr: 123 MAIN ST
City   : SOMEWHERE                      State: NY Zip: 12345
Created By: JIM SCHWILK                    Language: ENGLISH
Office Phone: 123-123-1234  x12345 SMS/Cell: 123-123-1234 Office Email: JIM@DOMAIN.COM
 
Site Contact: JAMES WINGATE
Site Phone: 123-123-1234          Site SMS/Cell: 123-123-1234
Site Email: JAMES@DOMAIN.COM
 
Excavation Area
State: CA County: LOS ANGELES     Place: CARSON
Zip: 12345
Location: Address/Street: 12345 MAIN ST
        : X/ST1: 1ST ST
        :
        : DOG IN YARD
Searchable Tags:
Job size: sq-feet: 123456     sq-miles: 0.123456
 
Delineated Method: WHITEPAINT
Work Type: REPAIR AND REPLACE SOMETHING
Work For : JOES RESTAURANT
Permit: 123456789ABC                   Job/Work order: AB123456
Project ID:
Project Name:
1 Year: N Boring: N Street/Sidewalk: Y Vacuum: Y Explosives: Y
 
Lat/Long
Center Generated (NAD83): 12.123456/-118.280453 33.844393/-118.279476
                        : 12.123456/-123.123456 12.123456/-123.123456 Excavator Provided:

Map link:
https://somelink.doamin.com/linkinformationhereandthiscouldbealongstring

Comments:

**REQUEST RE-MARKS** FROM ALL - WORK CONTINUING  PER JIM SCHWILK--[CSR01 03/28/2024 09:34 AM]

Members:
MBR001   MBR020     MBR039    LONGMBR058       MBR153  MBR172
MBR191       LONGMBR210      MBR324   MBR343     LONGMBR362     MBR077
MBR096  MBR115    LONGMBR134      MBR229       MBR248   MBR267
LONGMBR286   WBMWD

     (c) Copyright 2022 Underground Service Alert of Southern California.
                             All rights reserved.
    </printable_text>
  </tickets>
</NewDataSet>
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close