Appendix: Database Tables/Columns

adoption

The adoption table holds a row for each movement attached to an animal or person.

ID

INTEGER A unique, incrementing number that identifies this record

AdoptionNumber

VARCHAR A unique string identifying this movement (defaults to adoption.ID)

AnimalID

INTEGER Link to the animal table

OwnerID

INTEGER Link to the owner table

RetailerID

INTEGER Link to the owner table for a retailer (0 for no retailer)

OriginalRetailerMovementID

INTEGER Link to another movement in this table for an original movement to retailer that started this adoption

EventID

INTEGER Link to the event table for the adoption event this movement came from

MovementDate

TIMESTAMP The date the animal moved

MovementType

INTEGER Link to the lksmovementtype table for the type of animal movement (none = reservation, adoption, foster, transfer, escaped, stolen, reclaimed, etc)

InsuranceNumber

VARCHAR If your shelter does short term insurance when adopting, the policy number

ReturnDate

TIMESTAMP The date the animal came back to the shelter from this movement (or null for not returned)

ReturnedReasonID

INTEGER Link to the entryreason table for reason for return

ReturnedByOwnerID

INTEGER Link to the owner table for the person who returned the animal

ReasonForReturn

VARCHAR Free text, the reason the animal was returned

ReservationDate

TIMESTAMP For reservation movements, the reservation date

Donation

FLOAT A total of all donations attached to this movement

ReservationCancelledDate

TIMESTAMP If this is a reservation and it has been cancelled, the date it was cancelled

IsPermanentFoster

INTEGER 1 if this foster movement is permanent

IsTrial

INTEGER 1 if this is a trial adoption movement

TrialEndDate

TIMESTAMP The date this trial ends if it is a trial adoption movement

Comments

VARCHAR Movement comments

RecordVersion

INTEGER Optimistic lock flag

CreatedBy

VARCHAR User who created this record

CreatedDate

TIMESTAMP Date this record was created

LastChangedBy

VARCHAR User who last changed this record

LastChangedDate

TIMESTAMP Date this record was last changed

animal

The animal table holds a row for each animal on the system. Various denormalised fields at the end are used to track the current movement, whether the animal is on shelter, etc. These are useful for writing reports.

ID

INTEGER A unique, incrementing number that identifies this record

AnimalTypeID

INTEGER Link to the animaltype table

AnimalName

VARCHAR The animal’s name

NonShelterAnimal

INTEGER 1 if the animal is a non-shelter animal (kept out of figures)

CrueltyCase

INTEGER 1 if the animal is a cruelty case (all 0/1 fields link to lksyesno table)

BondedAnimalID

INTEGER Animal ID of bonded animal 1 (or 0)

BondedAnimal2ID

INTEGER Animal ID of bonded animal 2 (or 0)

BaseColourID

INTEGER Link to the basecolour table

SpeciesID

INTEGER Link to the species table

BreedID

INTEGER Primary breed (link to the breed table)

Breed2ID

INTEGER Secondary breed (same as primary if not crossbreed)

BreedName

VARCHAR Name of breed in form breed1 / breed2 if crossbreed

CrossBreed

INTEGER 1 if animal is a crossbreed

CoatType

INTEGER Link to lkcoattype table

Markings

VARCHAR Distinguishing features field

ShelterCode

VARCHAR The animal’s shelter code

ShortCode

VARCHAR The short version of the shelter code

UniqueCodeID

INTEGER The next UUU code can be generated by doing MAX(UniqueCodeID)

YearCodeID

INTEGER The next NNN code is MAX(YearCodeID) for matching brought in year and type

AcceptanceNumber

VARCHAR The acceptance number/Litter ID

DateOfBirth

TIMESTAMP The animal’s date of birth

AgeGroup

VARCHAR The animal’s age group based on date of birth at the most recent time it entered the shelter

AgeGroupActiveMovement

VARCHAR The animal’s age group based on date of birth at the last time it left the shelter

DeceasedDate

TIMESTAMP Date the animal died, if null the animal is still alive

Sex

INTEGER Link to the lksex table

Identichipped

INTEGER 1 if the animal is microchipped

IdentichipNumber

VARCHAR The animal’s microchip number

IdentichipDate

TIMESTAMP The date the animal was microchipped

Tattoo

INTEGER 1 if the animal has an ear tattoo

TattooNumber

VARCHAR The animal’s tattoo number

TattooDate

TIMESTAMP The date the animal was tattooed

Neutered

INTEGER 1 if the animal has been neutered/spayed

NeuteredDate

TIMESTAMP The date the animal was neutered/spayed

Declawed

INTEGER 1 if the animal has been declawed

HiddenAnimalDetails

VARCHAR Hidden animal comments

AnimalComments

VARCHAR The animal’s comments

OwnersVetID

INTEGER Link to the owner’s vet (owner table, 0 for no value)

CurrentVetID

INTEGER Link to the current vet (owner table, 0 for no value)

OriginalOwnerID

INTEGER Link to the original owner (owner table)

BroughtInByOwnerID

INTEGER Link to the owner who brought the animal in (owner table)

ReasonForEntry

VARCHAR Freeform text, reason animal was brought to shelter

ReasonNO

VARCHAR Freeform text, reason original owner didn’t bring the animal in

DateBroughtIn

TIMESTAMP The date the animal was brought into the shelter

EntryReasonID

INTEGER Reason for entry (link to entryreason table)

HealthProblems

VARCHAR Health problems box on Vet tab

PutToSleep

INTEGER 1 if the animal was euthanised

PTSReason

VARCHAR Freeform text, reason the animal died

PTSReasonID

INTEGER Euthanasia category - link to deathreason table

IsDOA

INTEGER 1 if the animal was dead on arrival to the shelter

IsTransfer

INTEGER 1 if the animal was a transfer from another animal shelter

IsGoodWithCats

INTEGER Link to the lksynun table for yes/no/unknown

IsGoodWithDogs

INTEGER Link to the lksynun table for yes/no/unknown

IsGoodWithChildren

INTEGER Link to the lksynun table for yes/no/unknown

IsHouseTrained

INTEGER Link to the lksynun table for yes/no/unknown

IsNotAvailableForAdoption

INTEGER 1 if the animal should not be included for publishing as adoptable

HasSpecialNeeds

INTEGER 1 if the animal has special needs

ShelterLocation

INTEGER Location on shelter (link to internallocation table)

DiedOffShelter

INTEGER 1 if the animal died off shelter (won’t be included on figures reports)

Size

INTEGER animal’s size (link to lksize table)

RabiesTag

VARCHAR The rabies tag field

Adoptable

INTEGER 1 if the animal is adoptable according to publishing rules

Archived

INTEGER 1 if the animal is off shelter now

ActiveMovementID

INTEGER Link to the adoption table for the latest movement for this animal

ActiveMovementType

INTEGER Link to lksmovementtype for the current movement type

ActiveMovementDate

TIMESTAMP The current movement date

ActiveMovementReturn

TIMESTAMP The current movement’s return date

HasActiveReserve

INTEGER 1 if the animal has an open reservation

HasTrialAdoption

INTEGER 1 if the animal is on trial adoption

DisplayLocation

VARCHAR Shows a readable version of the animal’s location. If on shelter, the internal location. If off shelter, the movementtype, accompanied by the person name.

MostRecentEntryDate

TIMESTAMP The most recent entry date - either DateBroughtIn or ActiveMovementDate

TimeOnShelter

VARCHAR Readable time spent on shelter (eg: 1 year, 3 months)

DaysOnShelter

INTEGER Number of days spent on shelter

DailyBoardingCost

INTEGER The cost of one days board on shelter for this animal

AnimalAge

VARCHAR Readable animal age (eg: 1 year, 3 months)

RecordVersion

INTEGER For optimistic locking, each save increments this lock value

CreatedBy

VARCHAR Name of user that created this record

CreatedDate

TIMESTAMP Date this record was created

LastChangedBy

VARCHAR Name of user that last changed this record

LastChangedDate

TIMESTAMP Date this record was last changed

animalcontrol

The animalcontrol table holds a row for every animal control incident on the system.

ID

INTEGER A unique, incrementing number that identifies this record

IncidentDateTime

TIMESTAMP The date and time of the incident

IncidentTypeID

INTEGER A link to the incidenttype table for the type of incident

CallDateTime

TIMESTAMP The date and time the incident was reported

CallNotes

VARCHAR The incident description

CallTaker

VARCHAR The username of the system user who took the call

CallerID

INTEGER A link to the owner table for the person who called to report the incident

VictimID

INTEGER A link to the owner table for the person who was the victim (if any)

DispatchAddress

VARCHAR The address the incident occurred at

DispatchTown

VARCHAR The incident town/city

DispatchCounty

VARCHAR The incident county/state

DispatchPostcode

VARCHAR The incident postcode/zipcode

DispatchLatLong

VARCHAR A geocode containing the latitude and longitude of the dispatch address

DispatchedACO

VARCHAR The username of the system user representing the ACO

PickupLocationID

INTEGER A link to the pickuplocation table

DispatchDateTime

TIMESTAMP The date and time the ACO was dispatched to the incident

RespondedDateTime

TIMESTAMP The date and time the ACO arrived at the incident

FollowupDateTime / FollowupDateTime2 / FollowupDateTime3

TIMESTAMP The date and time the ACO should return to the incident to follow up

FollowupComplete / FollowupComplete2 / FollowupComplete3

INTEGER 1 if the follow up has been done

CompletedDate

TIMESTAMP The date the incident was closed and completed

IncidentCompletedID

INTEGER A link to the incidentcompleted table

SiteID

INTEGER A link to the site table for multi-site setups

OwnerID / Owner2ID / Owner3ID

INTEGER A link to the owner table for the suspect

AnimalDescription

VARCHAR A description of the animal involved in the incident

SpeciesID

INTEGER A link to the species table for the animal involved in the incident

Sex

INTEGER A link to the lksex table for the animal involved in the incident

AgeGroup

VARCHAR The age group of the animal involved in the incident

dbfs

The dbfs table holds a row for each file stored in ASM’s database. Name and Path can be concatenated to form a full path. Eg: path = /templates name = cage_card.html

ID

INTEGER A unique, incrementing number that identifies this record

Name

VARCHAR The name of the element

Path

VARCHAR The path to the element

URL

VARCHAR Either file:[filename] or s3:[filename] for binary data stored in the filesystem or remote object storage like Amazon S3, BackBlaze B2 or Cloudflare R2 etc. If the data is in the Content field, the URL will be contain the text base64:

Content

CLOB Base64 encoded content or null if the data is in a file storage (see URL)

media

The media table holds a row for each piece media attached to an animal, owner, etc.

ID

INTEGER A unique, incrementing number that identifies this record

MediaType

INTEGER 1 = File, 2 = Link

MediaName

VARCHAR The name of the file within the dbfs table - always [media.ID].extension unless MediaType=2 in which case this will hold the link URL

MediaNotes

VARCHAR The notes accompanying the media file (can optionally be used as description when publishing)

DocPhoto

INTEGER 1 if this is the preferred photo of an animal for use with document templates

WebsitePhoto

INTEGER 1 if this is the preferred photo of an animal for use on the web

WebsiteVideo

INTEGER 1 if this is the preferred video link of the animal

DocPhoto

INTEGER 1 if this is the preferred photo of an animal for use with generated documents

ExcludeFromPublish

INTEGER 1 if this photo should be excluded from being used on websites or sent to third parties

SignatureHash

VARCHAR For media containing documents that have been signed, this field will contain a cryptographic hash of the document file data so that it can be checked for tampering

NewSinceLastPublish (deprecated)

INTEGER 1 if this media record was created after the last time a publish was done

UpdatedSinceLastPublish (deprecated)

INTEGER 1 if this media record was updated after the last time a publish was done

LastPublished (deprecated - see animalpublished table)

TIMESTAMP The date this record was last published to the web

LastPublishedPF (deprecated)

TIMESTAMP Date this record was last published to PetFinder.com

LastPublishedAP (deprecated)

TIMESTAMP Date this record was last published to AdoptAPet.com

LastPublishedP911 (deprecated)

TIMESTAMP Date this record was last published to Pets911.com

LastPublishedRG (deprecated)

TIMESTAMP Date this record was last published to RescueGroups PetAdoptionPortal.org

LinkID

INTEGER The ID of the record in the table this media is linked to

LinkTypeID

INTEGER Link to lksmedialink table to determine linked table

CreatedDate

TIMESTAMP The date this record was created

Date

TIMESTAMP Date this record was last updated

RetainUntil

TIMESTAMP The date to automatically delete this record (or NULL to never delete)

RecordVersion

INTEGER Optimistic lock flag

owner

The owner table holds a row for every person stored in ASM’s database.

ID

INTEGER A unique, incrementing number that identifies this record

OwnerType

INTEGER 1 = Individual, 2 = Organization, 3 = Couple

OwnerCode

VARCHAR A unique code for this record, generated from first two letters of surname and padded ID

OwnerTitle / OwnerTitle2

VARCHAR The person’s title, eg: Mr

OwnerInitials / OwnerInitials2

VARCHAR The person’s initials

OwnerForeNames / OwnerForeNames2

VARCHAR The person’s first name(s)

OwnerSurname / OwnerSurname2

VARCHAR The person’s surname

OwnerName

VARCHAR Title, Forenames and Surname concatenated together for display

OwnerAddress

VARCHAR The address

OwnerTown

VARCHAR The town or city (depending on locale)

OwnerCounty

VARCHAR The county or state (depending on locale)

OwnerPostcode

VARCHAR

HomeTelephone

VARCHAR

WorkTelephone / WorkTelephone2

VARCHAR

MobileTelephone / MobileTelephone2

VARCHAR

EmailAddress / EmailAddress2

VARCHAR

DateOfBirth / DateOfBirth2

TIMESTAMP

IdentificationNumber / IdentificationNumber2

VARCHAR The government issued identification number (passport, driving license, etc)

Comments

VARCHAR

GDPRContactOptIn

VARCHAR didnotask,declined,email,post,sms,phone

ExcludeFromBulkEmail

INTEGER 1 if this person should not receive bulk emails

IDCheck

INTEGER 1 if the person has been homechecked

IsBanned

INTEGER 1 if this person has been banned from adopting animals

IsVolunteer

INTEGER 1 if this person is a shelter volunteer

IsHomeChecker

INTEGER 1 if this person homechecks people

IsMember

INTEGER 1 if this person is a shelter member

MembershipExpiryDate

TIMESTAMP The date this person’s membership expires

MembershipNumber

VARCHAR The membership number

IsDonor

INTEGER 1 if this person is a regular donor

IsDriver

INTEGER 1 if this person is a driver for transport

IsShelter

INTEGER 1 if this person is another animal shelter

IsACO

INTEGER 1 if this person is an animal care officer

IsStaff

INTEGER 1 if this person is shelter staff (permissions exist to prevent users viewing staff records)

IsFosterer

INTEGER 1 if this person fosters animals

IsRetailer

INTEGER 1 if this person is a retailer for animals (eg: Pet shop)

IsVet

INTEGER 1 if this person is a vet

IsGiftAid

INTEGER (UK only) 1 if this person is eligible for gift aid on their donations

AdditionalFlags

VARCHAR Comma separated list of all person flags applying to this owner, includes additional person flags created under lookup data as well as the standard ones above.

HomeCheckAreas

VARCHAR If this person homechecks other people, the areas they are wiling to check

DateLastHomeChecked

TIMESTAMP The date this person was last homechecked

HomeCheckedBy

INTEGER ID of the person record who homechecked this person

MatchAdded

TIMESTAMP Date any looking for match was added to this person

MatchExpires

TIMESTAMP Date this looking for info expires

MatchActive

INTEGER 1 If we should consider this person’s looking for info when building the looking for report

MatchSex

INTEGER

MatchSize

INTEGER

MatchAgeFrom

FLOAT

MatchAgeTo

FLOAT

MatchAnimalType

INTEGER

MatchSpecies

INTEGER

MatchBreed

INTEGER

MatchBreed2

INTEGER

MatchGoodWithCats

INTEGER

MatchGoodWithDogs

INTEGER

MatchGoodWithChildren

INTEGER

MatchHouseTrained

INTEGER

MatchCommentsContain

VARCHAR

MatchFlags

VARCHAR