Schema reference
Schema diagram (1.4.1): as a giant .png or dia source (dia homepage).
- agent - Agents are resellers of your service. Agents may be limited to a subset of your full offerings (via their agent type).
- agentnum - primary key
- agent - name of this agent
- typenum - agent type
- prog - (unimplemented)
- freq - (unimplemented)
- disabled - Disabled flag, empty or 'Y'
- username - Username for the Agent interface
- _password - Password for the Agent interface
- agent_type - Agent types define groups of packages that you can then assign to particular agents.
- typenum - primary key
- atype - name of this agent type
- cust_bill - Invoices. Declarations that a customer owes you money. The specific charges are itemized in cust_bill_pkg.
- billpkgnum - primary_key
- invnum - primary key
- custnum - customer
- _date
- charged - amount of this invoice
- printed - how many times this invoice has been printed automatically
- closed - books closed flag, empty or `Y'
- cust_bill_event - Invoice event history
- part_bill_event - Invoice event definitions
- eventpart - primary key
- payby - CARD, DCRD, CHEK, DCHK, LECB, BILL, or COMP
- event - event name
- eventcode - event action
- seconds - how long after the invoice date (cust_bill._date) events of this type are triggered
- weight - ordering for events with identical seconds
- plan - eventcode plan
- plandata - additional plan data
- disabled - Disabled flag, empty or `Y'
- taxclass - Texas tax class flag, empty or "none", "access", or "hosting"
- cust_bill_pkg - Invoice line items
- invnum - (multiple) key
- pkgnum - package or 0 for the special virtual sales tax package
- setup - setup fee
- recur - recurring fee
- sdate - starting date
- edate - ending date
- itemdesc - Line item description (currently used only when pkgnum is 0)
- cust_bill_pkg_detail - Invoice line items detail
- detailnum - primary key
- pkgnum -
- invnum -
- detail - Detail description
- cust_credit - Credits. The equivalent of a negative cust_bill record.
- crednum - primary key
- custnum - customer
- amount - amount credited
- _date
- otaker - order taker
- reason
- closed - books closed flag, empty or `Y'
- cust_credit_bill - Credit invoice application. Links a credit to an invoice.
- creditbillnum - primary key
- crednum - credit being applied
- invnum - invoice to which credit is applied
- amount - amount applied
- _date
- cust_credit_bill - Refund payment application. Links a refund to a payment.
- payrefundnum - primary key
- paynum - payment
- refundnum - refund
- amount - amount applied
- _date
- cust_main - Customers
- custnum - primary key
- agentnum - agent
- refnum - referral
- first - name
- last - name
- ss - social security number
- company
- address1
- address2
- city
- county
- state
- zip
- country
- daytime - phone
- night - phone
- fax - phone
- ship_first
- ship_last
- ship_company
- ship_address1
- ship_address2
- ship_city
- ship_county
- ship_state
- ship_zip
- ship_country
- ship_daytime
- ship_night
- ship_fax
- payby - CARD, DCHK, CHEK, DCHK, LECB, BILL, or COMP
- payinfo - card number, P.O.#, or comp issuer
- paycvv - Card Verification Value, "CVV2" (also known as CVC2 or CID), the 3 or 4 digit number on the back (or front, for American Express) of the credit card
- paydate - expiration date
- payname - billing name (name on card)
- tax - tax exempt, Y or null
- otaker - order taker
- referral_custnum
- comments
- invoice_ship_address - Y or null, does ship address appear on invoice
(columns in italics are optional)
- cust_main_invoice - Invoice destinations for email invoices. Note that a customer can have many email destinations for their invoice (either literal or via svcnum), but only one postal destination.
- destnum - primary key
- custnum - customer
- dest - Invoice destination. Freeside supports three types of invoice delivery: send directly to a service defined in Freeside, send to an arbitrary email address, or print the invoice to a printer and have someone send it out via snail mail. Freeside determines which method to use based on the contents of the dest field. If the contents are numeric, a svcnum pointing to a valid service is expected in the field. If the contents are a string, a literal email address is expected to be in the field. If the special keyword `POST' is present, the snail mail method is used (which is the default if no cust_main_invoice records exist). Snail mail invoices get their address information from cust_main and are printed with the printer defined in the configuration files.
- cust_main_county - Tax rates
- taxnum - primary key
- state
- county
- country
- tax - % rate
- taxclass
- exempt_amount
- taxname - if defined, printed on invoices instead of "Tax"
- setuptax - if 'Y', this tax does not apply to setup fees
- recurtax - if 'Y', this tax does not apply to recurring fees
- cust_tax_exempt - Tax exemption record
- exemptnum - primary key
- taxnum - tax rate
- year
- month
- amount
- cust_pay - Payments. Money being transferred from a customer.
- paynum - primary key
- custnum - customer
- paid - amount
- _date
- payby - CARD, CHEK, LECB, BILL, or COMP
- payinfo - card number, P.O.#, or comp issuer
- paybatch - text field for tracking card processor batches
- closed - books closed flag, empty or `Y'
- cust_pay_void - Voided payments.
- paynum - primary key
- custnum - customer
- paid - amount
- _date
- payby - CARD, CHEK, LECB, BILL, or COMP
- payinfo - card number, P.O.#, or comp issuer
- paybatch - text field for tracking card processor batches
- closed - books closed flag, empty or `Y'
- void_date
- reason
- otaker - order taker
- cust_bill_pay - Applicaton of a payment to a specific invoice.
- pay_batch - Pending batch
- batchnum
- status
- download
- upload
- cust_pay_batch - Pending batch members
- paybatchnum
- batchnum
- payby - CARD, CHEK, LECB, BILL, or COMP
- payinfo - account number
- exp - card expiration
- amount
- invnum - invoice
- custnum - customer
- payname - name on card
- first - name
- last - name
- address1
- address2
- city
- state
- zip
- country
- status
- cust_pkg - Customer billing items
- pkgnum - primary key
- custnum - customer
- pkgpart - Package definition
- setup - date
- bill - next bill date
- last_bill - last bill date
- susp - (past) suspension date
- expire - (future) cancellation date
- cancel - (past) cancellation date
- otaker - order taker
- manual_flag - If this field is set to 1, disables the automatic unsuspensiond of this package when using the unsuspendauto config file.
- cust_refund - Refunds. The transfer of money to a customer; equivalent to a negative cust_pay record.
- refundnum - primary key
- custnum - customer
- refund - amount
- _date
- payby - CARD, CHEK, LECB, BILL or COMP
- payinfo - card number, P.O.#, or comp issuer
- otaker - order taker
- closed - books closed flag, empty or `Y'
- cust_credit_refund - Applicaton of a refund to a specific credit.
- creditrefundnum - primary key
- crednum - credit
- refundnum - refund
- amount
- _date
- cust_svc - Customer services
- nas - Network Access Server (terminal server)
- nasnum - primary key
- nas - NAS name
- nasip - NAS ip address
- nasfqdn - NAS fully-qualified domain name
- last - timestamp indicating the last instant the NAS was in a known state (used by the session monitoring).
- part_pkg - Package definitions
- pkgpart - primary key
- pkg - package name
- comment - non-customer visable package comment
- promo_code - promotional code
- deprecated setup - setup fee expression
- freq - recurring frequency (months)
- deprecated recur - recurring fee expression
- setuptax - Setup fee tax exempt flag, empty or `Y'
- recurtax - Recurring fee tax exempt flag, empty or `Y'
- plan - price plan
- deprecated plandata - additional price plan data
- disabled - Disabled flag, empty or `Y'
- part_pkg_option - Package definition options
- optionnum - primary key
- pkgpart - Package definition
- optionname - option name
- optionvalue - option value
- reg_code - One-time registration codes
- codenum - primary key
- code
- agentnum - Agent
- reg_code_pkg - Registration code link to package definitions
- part_referral - Referral listing
- refnum - primary key
- referral - referral
- part_svc - Service definitions
- svcpart - primary key
- svc - name of this service
- svcdb - table used for this service: svc_acct, svc_forward, svc_domain, svc_charge or svc_wo
- disabled - Disabled flag, empty or `Y'
- part_svc_column
- columnnum - primary key
- svcpart - Service definition
- columnname - column name in part_svc.svcdb table
- columnvalue - default or fixed value for the column
- columnflag - null, D or F
- pkg_svc
- pkgsvcnum - primary key
- pkgpart - Package definition
- svcpart - Service definition
- quantity - quantity of this service that this package includes
- primary_svc - blank or Y: primary service
- export_svc
- part_export - Export to external provisioning
- exportnum - primary key
- machine - Machine name
- exporttype - Export type
- nodomain - blank or Y: usernames are exported to this service with no domain
- part_export_option - provisioning options
- optionnum - primary key
- exportnum - Export
- optionname - option name
- optionvalue - option value
- port - individual port on a nas
- portnum - primary key
- ip - IP address of this port
- nasport - port number on the NAS
- nasnum - NAS
- prepay_credit - prepaid cards
- prepaynum - primary key
- identifier - text or numeric string of prepaid card
- amount - amount of prepayment
- seconds - prepaid time instead of (or in addition to) monetary value
- agentnum - optional agent assignment for prepaid cards
- session
- sessionnum - primary key
- portnum - Port
- svcnum - Account
- login - timestamp indicating the beginning of this user session.
- logout - timestamp indicating the end of this user session. May be null, which indicates a currently open session.
- svc_acct - Accounts
- svcnum - primary key
- username
- _password
- sec_phrase - security phrase
- popnum - Point of Presence
- uid
- gid
- finger - GECOS
- dir
- shell
- quota - (unimplementd)
- slipip - IP address
- seconds
- domsvc
- radius_Radius_Reply_Attribute - Radius-Reply-Attribute
- rc_Radius_Check_Attribute - Radius-Check-Attribute
- svc_acct_pop - Points of Presence
- popnum - primary key
- city
- state
- ac - area code
- exch - exchange
- loc - rest of number
- part_pop_local - Local calling areas
- localnum - primary key
- popnum - primary key
- city
- state
- npa - area code
- nxx - exchange
- svc_domain - Domains
- svc_forward - Mail forwarding aliases
- domain_record - Domain zone detail
- recnum - primary key
- svcnum - Domain (by svcnum)
- reczone - zone for this line
- recaf - address family, usually IN
- rectype - type for this record (A, MX, etc.)
- recdata - data for this record
- svc_www
- type_pkgs
- queue - job queue
- jobnum - primary key
- job
- _date
- status
- statustext
- svcnum
- queue_arg - job arguments
- argnum - primary key
- jobnum - job
- arg - argument
- queue_depend - job dependancies
- dependnum - primary key
- jobnum - source jobnum
- depend_jobnum - dependancy jobnum
- radius_usergroup - Link users to RADIUS groups.
- usergroupnum - primary key
- svcnum - account
- groupname
- rate - Call rate plans
- ratenum - primary key
- ratename
- rate_detail - Call rate detail
- ratedetailnum - primary key
- ratenum - rate plan
- orig_regionnum - call origination region
- dest_regionnum - call destination region
- min_included - included minutes
- min_charge - charge per minute
- sec_granularity - granularity in seconds, i.e. 6 or 60
- rate_region - Call rate region
- regionnum - primary key
- regionname
- rate_prefix - Call rate prefix
- prefixnum - primary key
- regionnum - rate region
- countrycode
- npa
- nxx
- msgcat - i18n message catalog
- msgnum - primary key
- msgcode - message code
- locale - locale
- msg - Message text
- clientapi_session - ClientAPI session store
- sessionnum - primary key
- sessionid - session ID
- namespace - session namespace
- clientapi_session_field - Client API session store data
- fieldnum - primary key
- sessionnum - session
- fieldname
- fieldvalue